Run Excel Macro from VBS

[kriemer]

I am trying to find a macro to open Excel and run a macro. I have tried the following script and while Excel does open and the macro starts it does not complete. Of course the macro has been tested and runs when executed "normally".

{code]
Set xlObj = CreateObject("Excel.application")
xlobj.Visible = True ' Remove comment mark " ' " to see spreadsheets open
xlObj.Workbooks.Open "Z:\My Virtual Machines data\office\current EOD data\QP EOD data\daily stock price -0bar.xls"

xlObj.Run "macro1"

xlObj.ActiveWorkbook.Saved = False xlObj.ActiveWindow.Close
xlObj.Quit
[end code]

I will be happy to provide any additional info if required.

 

[tsuji]

The run would run the macro synchroneously. What then is there in the "macro"?

Alse is there such thing as xlObj.ActiveWindow???

 

[kriemer]

Macro code as follows (line that crashed macro is noted):

Sub macro1()
' Ticker Import Function
Range("A2").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;Z:\My Virtual Machines data\VM find stock signals\office\EOD data\complete list current fundamental.txt", Destination:= _
Range("A2"))
.Name = "ticker"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

' QP Data Copy
Range("B2", Range("B2").End(xlToRight)).Copy
Selection.End(xlDown).Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("A1").Select

' Remove_Errors

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

' COLUMN C
Range("A1:H65536").Sort Key1:=Range("C1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Range("C1").Select

Cells.Find(What:="N/A").Activate '<<<< THIS IS THE STATEMENT THAT FAILS

Selection.End(xlToLeft).Select

Range(Selection, Selection.End(xlToRight)).Select

Range(Selection, Selection.End(xlDown)).Select

Selection.EntireRow.Delete

' New Worksheet Name, save as: daily stock price 1bar.csv
Application.DisplayAlerts = False
Dim W1 As Workbook
Set W1 = Workbooks.Add(xlWBATWorksheet)

Range("A1").Select
W1.Sheets("Sheet1").Range("A1:G10000") = _
ThisWorkbook.Sheets("Sheet1").Range("A1:G10000").Value
ActiveWorkbook.SaveAs Filename:="c:\EOD data\daily 0bar.csv", FileFormat:=xlCSV

' Save and close open Workbook(1)
Dim Wb1 As Workbook
For Each Wb1 In Workbooks
If Wb1.Name <> ThisWorkbook.Name Then
Wb1.Close savechanges:=True
End If
Next Wb1

' Close open Workbook(Master)
Application.Quit

End Sub

Thanks for any thoughts you might have