Need assistance in SaveAs for Excel File

[batteam]

I have the following code that creates an Excel file, puts data into the cells but now I need to save the file. I cannot get the syntax correct for the .......SaveAs FileName:= "xxxxxxx"

Set obj = CreateObject("Excel.Application")
Set oApp = obj.Application
Set oWb = oApp.Workbooks.Add
Set oWb = oApp.Workbooks(1)
Set oWs1 = oWb.Worksheets("sheet1")


oWs1.Cells(1, 1).Value = "column1"
oWs1.Cells(1, 2).Value = "column2"
oWs1.Cells(1, 3).Value = "column3"
...
...

Then:
f1 = "mydata1"
f2 = "mydata2"
f3 = "mydata3"
...
...
This all works OK, but now I need to save the newly created file, so:

obj.ActiveWorkbook.SaveAs FileName:="C:\MyPath_filename.xlsx", FileFormat:=xlsx

But I keep getting the "Object doesn't support this property or method" Run-time error at the 'SaveAs' code line.
Any assistance in getting the correct Dim, set and SaveAs syntax would be appreciated.

 

[Andrzejek]

[tt][red]
oApp.[/red]ActiveWorkbook.SaveAs FileName:="C:\MyPath_filename.xlsx"[/tt]

Have fun.

---- Andy

 

[SkipVought]

hi,

obj.ActiveWorkbook.SaveAs FileName:="C:\MyPath_filename.xlsx", FileFormat:= 51

http://www.rondebruin.nl/win/s5/win001.htm

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

Forgot about: "correct Dim"

[blue]
Dim obj As Object
Dim oApp As Object
Dim oWb As Object
Dim oWs1 As Object
[/blue]
Set obj = CreateObject("Excel.Application")
Set oApp = obj.Application
Set oWb = oApp.Workbooks.Add
Set oWb = oApp.Workbooks(1)
Set oWs1 = oWb.Worksheets("sheet1")

obj.Visible = True

oWs1.Cells(1, 1).Value = "column1"
oWs1.Cells(1, 2).Value = "column2"
oWs1.Cells(1, 3).Value = "column3"

oApp.ActiveWorkbook.SaveAs FileName:="C:\MyPath_filename.xlsx" [green]', FileFormat:=xlsx[/green]

Have fun.

---- Andy

 

[PHV]

Anyway, I'd use oWb.SaveAs

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886