Saving a workbook from Access VBA

[Tim Johnson]

Hi there,

I am pushing several Excel files out from an Access database and trying to
save them each in a folder. The following code is contained within a loop:

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)

<Code filling in data to xlSheet>


xlBook.SaveAs FileName:=Environ("userprofile") & "\DAD_TEMP\DAI_" & Dist
& ".xls", FileFormat:=56
xlApp.Quit

Everything seems to be working fine, except that I am being prompted with
the "Do you want to save the changes you made to book..." I have verified
that the DAD_TEMP folder exists, and it is empty, so there shouldn't be any
conflicts. I am using Office 2003, so FileFormat:=56 should equate to
xlExcel8 (97-2003 format)...I'm lost.

What am I doing wrong?

Thanks in advance,
Tim

 

[Ken Snell [MVP]]

Use EXCEL's DisplayAlerts property to suppress the dialog box:

xlApp.DisplayAlerts = False
xlBook.SaveAs FileName:=Environ("userprofile") & "\DAD_TEMP\DAI_" & Dist &
".xls", FileFormat:=56
xlApp.DisplayAlerts = True

 

[Tim Johnson]

Thanks for your response, unfortunately, the alerts aren't the actual issue
here.

The workbook is not saving. I disable DisplayAlerts as you suggested, but
ended up with no files. I took it one step further and added the code after
the SaveAs command:

If Not xlBook.Saved Then
MsgBox "Not Saved"
Exit Sub
End If

The message box DID appear; which leaves me confused about how to proceed.

Any help is greatly appreciated.

 

[Tim Johnson]

I think I've solved the issue.

The application that this code is in is being run by both Access 2003 and
2007. It appears that xlExcel8 is not a valid constant in Access/Excel 2003
(although I think it should be). When I changed my FileFormat parameter to
39, all worked as it should have.

Hopefully, this will be able to help someone else with this issue in the
future.

Thanks for your efforts, Ken, it is greatly appreciated.

 

[cisse]

dou you from greece;;;;;;