Save exported spreadsheet in latest format (VBA)

[Ian]

I have been working on some code to create, then modify an Excel spreadsheet
from Access using VBA.

Evrything is working as I need it except for when it come to save the sheet.
If I stop the code and save the file manually, I get a prompt informing me
that the file is in Excel 5.0/95 format and do I want to save it in the
latest format. If I say yes to this, everything is OK.

If I let the code run on the following code executes:
objExcel.ActiveWorkbook.Save
objExcel.Application.Quit

Whe I reopen the file I find that some of the changes have not been saved
(conditional formatting).

Is there some way to force Access to either a)create an Excel spreadsheet
using DoCmd.OutputTo or b) after reopening the created file to add formuale
and formatting, save the file in the latest format?

 

[Tony Toews [MVP]]

If I let the code run on the following code executes:
objExcel.ActiveWorkbook.Save

Try objExcel.ActiveWorkbook.SaveAs <fileName>,<XLfileFormat>, ...
There are many options such as xlWorkbookNormal (or xlExcel12 if you
are running A2007.)

Ah, Excel 2007 help states "The file format to use when you save the
file. For a list of valid choices, see the FileFormat property. For an
existing file, the default format is the last file format specified;
for a new file, the default is the format of the version of Excel
being used." So the SaveAs may do want you want it to do.

You probably won't need, or won't be able, to set the worksheet name
at the beginning of your code

Now if you have multiple versions of Office or Excel installed, or
even no Excel installed, you should consider using Late Binding once
your app is debugged.

Late binding means you can safely remove the reference and only have
an error when the app executes lines of code in question. Rather than
erroring out while starting up the app and not allowing the users in
the app at all. Or when hitting a mid, left or trim function call.

You'll want to install the reference if you are programming or
debugging and want to use the object intellisense while in the VBA
editor. Then,. once your app is running smoothly, remove the
reference and setup the late binding statements.

Sample code:
' Declare an object variable to hold the object
' reference. Dim as Object causes late binding.
Dim objWordDoc As Object
Set objWordDoc = CreateObject(" Word.Document")

For more information including additional text and some detailed links
see the "Late Binding in Microsoft Access" page at
http://www.granite.ab.ca/access/latebinding.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Ian]

There are many options such as xlWorkbookNormal

Thanks Tony. That sorted it, once I realised that the reason I've been
having problems with this line is that I'd specified the filename, not
path/filename. I've just found all sorts of previous files in My Documents!!

I don't suppose there's any way to force Access to create the file as XL200
in the first place, is there? I'm using DoCmd.OutputTo acOutputForm, "Time
sheet for submission", acFormatXLS, strFullPath, 0.

That way I could just use Save instead of SaveAs and not have to create a
second file.

 

[Tony Toews [MVP]]

Thanks Tony. That sorted it, once I realised that the reason I've been
having problems with this line is that I'd specified the filename, not
path/filename. I've just found all sorts of previous files in My Documents!!

I don't suppose there's any way to force Access to create the file as XL200
in the first place, is there? I'm using DoCmd.OutputTo acOutputForm, "Time
sheet for submission", acFormatXLS, strFullPath, 0.

Hmm, Docmd.OutputTo is somewhat incompatible with Automation. Ahh,
I suspect the above line of code is your first line of code and then
you work with the spreadsheet file.

Depending on how you code things you can work with the spreadsheet in
Excel without specifying a name. And only at the end do you use the
objExcel.ActiveWorkbook.SaveAs <fileName>,<XLfileFormat> to give it a
path and file name.

If you want post the first few lines of your code and we'll get you
going in that fashion.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Ian]

Hmm, Docmd.OutputTo is somewhat incompatible with Automation. Ahh,

I suspect the above line of code is your first line of code and then
you work with the spreadsheet file.

Correct. The file is created, then I open and modify it.

Depending on how you code things you can work with the spreadsheet in
Excel without specifying a name. And only at the end do you use the
objExcel.ActiveWorkbook.SaveAs <fileName>,<XLfileFormat> to give it a
path and file name.

It works well enough with the method I have now.

If you want post the first few lines of your code and we'll get you
going in that fashion.

Thanks for the offer, but I'll stick with what I have now.

 

[Tony Toews [MVP]]

Correct. The file is created, then I open and modify it.

If you work with the Excel file in memory and then do the SaveAs you
can specify the file format. Which is your problem.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm