Interfacing with Excel from Inside Project VBA code

[jsl]

Okay... not sure if this question rightly belongs here or in the Excel
VBA group...

I have some VBA code in Project where I'm writing some data from my
Project file to an Excel spreadsheet.

so I do the following

Set xlApp = CreateObject("Excel.Application")

If xlApp Is Nothing Then
Set xlApp = New Excel.Application
xlApp.Visible = True
Else
xlApp.Workbooks.Open ("C:\test.xls")
xlApp.Visible = True
End If

Set xlSheet = xlApp.Worksheets("Sheet1")

and then via incrementing a couple of counters, say i and j, I cycle
through my Project using "xlsheet.cells(i, j).Value =" to write the
desired data to my spreadsheet (in this case test.xls).

I then successfully save and close the spreadsheet, e.g...

xlApp.ActiveWorkbook.Save
xlApp.Quit

My primary problem is that the code snippet expects the file "test.xls"
to already exist... but I don't want that.

Initially it will be fine to keep the hard-coded pathname and if it
doesn't exist, create it. Long-term, I'll modify this to have a
user-form to query for a file-name to use for the Excel spreadsheet.

I haven't been able to figure out from any examples, either via links
from here or MSDN or the SDK how to get the file created if it doesn't
already exist. Once I can figure this out, it's easy to set a flag so
that the code will correctly do .Save vs. .SaveAs

Any suggestions?

--jsl

 

[Ed Morrison]

You can check if the file exists.

Set fs = CreateObject("Scripting.FileSystemObject")
If fs.fileexists("c:\test.xls") Then
xlApp.Workbooks.Open ("C:\test.xls")
End If

Use the SaveAs function instead of the Save.

 

[jsl]

Thx much. Works like a champ.

--jsl