'ActiveWorkbook.SaveAs' for Excel from Access Module

[eoan]

{1st post}

Many thanks to all of you who have helped me in the past, however no amount of searching has given me a solution to this one, so I'm posting in the hope that someone can point out where I'm going wrong...

I need a module in Access which:

1- runs a few queries, placing the outputs into an excel file,
2- renames the worksheets
3- saves document as a new file name (incorporating today's date)

so far i can achieve one and two, but I've had no luck with 3...

the relevant bit of code is:

Dim objXLApp As Object
Dim objXLBook As Object
Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("C:\M08_09\Reporting\LIVEDOC - Open.xls")
objXLApp.Application.Visible = True

objXLBook.Sheets("Sub_Q_IMPART_OpenCases_Alloc_BD").Name = "BD-Allocated"

{... cut more of the same sheet-renaming code ...}

objXLBook.ActiveWorkbook.SaveAs Filename:= _
"C:\M08_09\Reporting\Open Cases - " & Format(Date, "yyyy.mm.dd") & ".xls", FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

the ActiveWorkbook.SaveAs bit works fine if I run the code as a macro within excel, however when i put it into the Access module I get the following error:

"Object doesn't support this property or method"

I've tried all sorts of odds and ends I've dug up from the internet without success - I'm new into IT and have never used VBA before, so I'm winging it somewhat...

Any ideas or assistance would be gratefully received!

 

[macca the hacke]

When you are outputting the data into excel, why don't you name it what you want it to be first of all - and then open excel and rename sheets, etc?

 

[eoan]

Hi Macca - the document I paste into already has the required formatting and summaries; the report is generated on a weekly basis and gets the date included in the filename.

I'm not quite sure I understand the second part of your reply - I think you're suggesting running the module in Access, then running the macro in Excel, in which case yes, I can use that for the time being, however I'd like to have the whole thing done in one go. I don't need the new date-stamped document to include the renaming macro either, so it seems to make sense to do it all through Access.

Thanks for such a speedy reply, though - and do let me know if you have any other thoughts/ suggestions!

 

[DCrake]

Simple Software Solutions

What Macca is trying to get over to is that when you are declaring your objects,

Set objXLBook = objXLApp.Workbooks.Open("C:\M08_09\Reporting\LIVED OC - Open.xls")

Prior to this why don't you simply copy the aforenamed spreadsheet to a new spreadsheet with the date and time stamp.

C:\MH08_09\Reporting\Open Cases - " & Format(Date, "yyyy.mm.dd") & ".xls"

FileSystem.FileCopy("C:\M08_09\Reporting\LIVED OC - Open.xls","C:\MH08_09\Reporting\Open Cases - " & Format(Date, "yyyy.mm.dd") & ".xls")

Then instead of opening the original spreadsheet you open the newly created spreadsheet.

When you have infinshed you population of the data then simply perform a simple

objXLBook.ActiveWorkbook.Save

Don't forget to set the object to nothing to destroy the instances are release the caching.


CodeMaster:

 

[ecawilkinson]

instead of:

objXLBook.ActiveWorkbook.SaveAs Filename:= _

use:

objXLApp.ActiveWorkbook.SaveAs Filename:= _

A Excel workbook does not have an Activeworkbook property. Also, unless you have added a reference to Excel, then Access will not understand xlNormal as it is part of the Excel object model. (its value is -4143).

HTH,
Chris

 

[eoan]

Thanks DCrake & Chris - I understand what you and macca are saying about copying first, thanks! The value for xlNormal was also very helpful (- read: made it work ).

I also found a couple of other bits and pieces I was able to copy and paste and I now have a script which extracts the relevant info, creates a password-protected file with the correct name and formatting, and then emails the doc to the relevant people complete with message and sign off:

Function Query_Emailer()
On Error GoTo Query_Emailer_Err
' Run Queries, export to pre-formatted excel file.
    DoCmd.TransferSpreadsheet acExport, 8, "Sub-Q-IMPART-OpenCases-Alloc-BD", "C:\M08_09\Reporting\LIVEDOC - Open.xls", False, ""
' ...Several more like these.

    Dim objXLApp As Object
    Dim objXLBook As Object
    Set objXLApp = CreateObject("Excel.Application")
    Set objXLBook = objXLApp.Workbooks.Open("C:\M08_09\Reporting\LIVEDOC - Open.xls")
    objXLApp.Application.Visible = False

' Rename the worksheets.
    objXLBook.Sheets("Sub_Q_IMPART_OpenCases_Alloc_BD").Name = "BD-Allocated"
' ...Several more like these.

' Save as file with today's date.
    objXLBook.SaveAs Filename:= _
    "C:\M08_09\Reporting\Open Cases - " & Format(Date, "yyyy.mm.dd") & ".xls", FileFormat:=-4143 _
        , Password:="Gorgonzola", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
    objXLBook.Close

Dim EmailApp, NameSpace, EmailSend As Object

Set EmailApp = CreateObject("Outlook.Application")
Set NameSpace = EmailApp.GetNamespace("MAPI")
Set EmailSend = EmailApp.CreateItem(0)

EmailSend.To = "name@address.com"
EmailSend.Cc = "cc-name@address.com"
EmailSend.Subject = "Subject"
EmailSend.Body = "Message Body" & vbCrLf & vbCrLf & "Kind regards," & vbCrLf & " " & vbCrLf & " " & vbCrLf & "MyName" & vbCrLf & vbCrLf & "MyFullName" & vbCrLf & "MyJobTitle"
EmailSend.Attachments.Add "C:\M08_09\Reporting\Open Cases - " & Format(Date, "yyyy.mm.dd") & ".xls"
EmailSend.Display
EmailSend.Send

Set EmailApp = Nothing
Set NameSpace = Nothing
Set EmailSend = Nothing
    
Query_Emailer_Exit:
    Exit Function

Query_Emailer_Err:
    MsgBox Error$
    Resume Query_Emailer_Exit

End Function

... all at the click of a button. One thing I found odd - on the sign-off, I wanted to leave two lines between 'Regards' and my name, but using

"Kind regards," & vbCrLf  & vbCrLf & vbCrLf & "MyName"

seemed to leave my with far too much space. The addition of the spaces:

"Kind regards," & vbCrLf & " " & vbCrLf & " " & vbCrLf & "MyName"

got it to look right...

I'm sure that and other elements could do with tidying up a bit (any corrections more than welcome, btw), but I hope not too bad for my first use of vb.

Thanks again for both replies and archive info - I think this site might become my homepage soon..!

 

[DCrake]

Simple Software Solutions

Couple of tips:

When you are renaming your spreadsheet using today's date it may be useful to check to see if the file already exists and ask the user if they want to overwrite it first.

Also your naming convention needs a little modification I would suggest you leave out the .'s (dot's ) between your date mask as it may confuse the issue.

David