VBA Excel File SaveAs

  • Thread starter Thread starter Pendragon
  • Start date Start date
P

Pendragon

Access 03/WinXP

I am exporting a series of data into Excel and the workbook is a template
file. After the data is exported, I want the Excel file saved to a
programmed file name. I am getting "Error 438 - Object doesn't support this
property of method." This seems fairly simple, especially after reviewing so
many posts that include some variation of this function. The solution is
probably simple and I've just been staring at this too long. Another set of
eyes is appreciated.

If fIsAppRunning("Excel") = -1 Then
Set objExcel = GetObject(, "Excel.Application")
objExcel.Visible = True
Else
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
End If

With objExcel
.workbooks.Open (stDocName)
End With

.....code....

objExcel.workbooks.SaveAs stDocName

rs.Close
CoDB.Close
objExcel.Close
set rs = nothing
set CoDB = nothing
set objExcel = nothing
 
Hi,

I have not tested this to establish if it produces the error you are getting
but I think it might be the problem.

In the VBA Editor, select Tools->References and then scroll down untill you
find
Microsoft Excel nn.0 Object Library and check the box and then OK. (Ensure
you check the box not just select the line before you click O.K..)

nn is 12 for Excel 2007, 11 for Excel 2003, 10 for Excel 2002 etc.
 
The references are good as there are some 3-4 pages of code exporting data to
Excel and manipulating the worksheets prior to the Save As command. The
export, manipulation and formatting all work as desired. The Save As command
fails.
 
SaveAs works with either a Chart, Worksheet or Workbook object. You're
trying to use it with the Workbooks collection.

Try:

objExcel.workbooks(stDocName).SaveAs stDocName

(although I don't understand why you're using the SaveAs method and not just
the Save method)
 
I thought since I was writing to an Excel template (.xlt) I had to use a
SaveAs, since that is what would happen if you manually did a File-Save in
Excel - it would pop-up as SaveAs.

I tried both Save and SaveAs and am now getting an Error 9 Subscript out of
range. I suspect that Workbooks(stDocName) is the culprit - since the
original Excel file is a template, e.g., MembershipData.xlt, the VBA
code-opened Excel file is actually named MembershipData1 and not what is
programmatically set in stDocName.

Is there a way to determine what the numeral is after the file name, e.g.,
MembershipData & ___, from Access VBA? If I could get that information then
that could be placed in Workbooks(_____).

Thanks.
 
You could declare a variable to hold the workbook:

Dim objWorkbook As Object


You'd change

With objExcel
.workbooks.Open (stDocName)
End With

to

Set objWorkbook = objExcel.Workbooks.Open (stDocName)

and then

objExcel.workbooks.SaveAs stDocName

to

objWorkbook.SaveAs stDocName


If stDocName is a template, are you changing the name before you save it?
 
The Excel file is not being saved as a template so there isn't a danger of
saving over it; by default, opening a template and doing a save as requires a
name change. But yes, I am changing the name of the Excel file before
saving. Thanks for the note, though!

The change is code worked perfectly.
 
Back
Top