using macro to open an excel spreadsheet

[qaxooti]

I am using a simulation software to open up results in an excel spreadsheet. What I basically want to do is to open up an excel spreadsheet, and then have it follow a macro to put the results on the sheet. I have already the macro data, the only problem I am having is that I can get the spreadsheet to open and format the first time, but then when I run the simulation again, only a blank sheet opens up and the macro is not run....I am very confused....I do'nt know if it some odd quirk of excel or something....also, how can you use VBA to open up an external file? The way I have currently done it is that I will open an new excel application and then use a macro the changes directories and opens up the correct file. I can't see to open up the file directly without doing that. Is there some type of command that would help me with that? Here is my code for opening the excel spreadsheet:

' =========================================================================
' Start Excel (or use existing program instance if Excel already is loaded)
' =========================================================================
Dim XL As Excel.Application, XLBook As Excel.Workbook, XLSheet As Excel.Worksheet
On Error Resume Next
Set XL = CreateObject("Excel.Application")


' Set XL = GetObject(, "Excel.Application")
' If Err.Number <> 0 Then ' Excel wasn't loaded; start it
' Err.Clear
' If Err.Number <> 0 Then
' MsgBox "Unable to load Microsoft Excel."
' Exit Sub
' End If
' End If
' Err.Clear

XL.Visible = True
' Add a new workbook with one worksheet for the chart
XL.SheetsInNewWorkbook = 1
Set XLBook = XL.Workbooks.Add
' Row = 0
' Set XLSheet = XLBook.Worksheets(1)
' XLSheet.Name = "Results"



With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Lockheed Project\F22 Sim.out", Destination:=Range("A1"))
.Name = "F22 Sim"



after this, I have more commands that will do more stuff to the spreadsheet

 

[BrianB]

In the absence of another reply (using LL97 that doesnot have TEXT; option) :-

Perhaps you have to use
.Refresh
on the query table.