? about File SaveAs procedure in Addin macro

[Barney Bird]

I searched this newsgroup's archive on Google but could not find this
question answered.

I have a macro-driven weekly caseworker inventory report I run in Excel.
The inventory report is set up as a template I'll call WklyInvRpt.xls. I
save each week's report under a file name like WklyInvRpt-03222004.xls. In
order to avoid saving the macro with each week's report, I have the VBA code
loaded into an addin macro which is saved as WklyInvRpt.xla. The addin
macro runs the report. So far so good.

I'm having a problem with the procedure that automatically saves the
completed report. The procedure saves the addin file (WklyInvRpt.xla) as
WklyInvRpt-03222004.xls instead of the completed inventory report. How do I
get the SaveAs procedure in the addin macro to save the completed report
instead of the addin file?


Barney Byrd

 

[Steve Garman]

Difficult to know your exact problem without seeing your code.

However, you may be using ThisWorkbook.SaveAs

If so, try replacng it with ActiveWorkbook.SaveAs
or an explicit reference to your workbok

 

[Barney Bird]

Sub SaveCompletedReport()

Dim FileDate As Date, FileNameDate As String, _
FileSaveName As String

FileDate = #3/22/2004#
FileNameDate = Format$(FileDate, "mmddyyyy")
FileSaveName = CurDir & "\WklyInvRpt-" & FileNameDate & ".xls"

Application.DisplayAlerts = False

ThisWorkbook.SaveAs FileSaveName, _
FileFormat:=xlWorkbookNormal

Application.DisplayAlerts = True

End Sub

 

[Steve Garman]

That seems to confirm my guess.

If the workbook your saving is active then my original suggestion to
replace "ThisWorkbook.SaveAs" with "ActiveWorkbook.SaveAs" should work.
Have you tried it?

If it may not be the active workbook then you'll need to create a
reference to the workbook you want to save.

 

[Barney Bird]

That fixed my problem. Thanks.


Barney Byrd

That seems to confirm my guess.

If the workbook your saving is active then my original suggestion to
replace "ThisWorkbook.SaveAs" with "ActiveWorkbook.SaveAs" should work.
Have you tried it?

If it may not be the active workbook then you'll need to create a
reference to the workbook you want to save.