Access VBA - Opening and Saving Excel

User avatar
Abraxus
3StarLounger
Posts: 263
Joined: 01 Mar 2010, 17:34
Location: Blue Springs, MO

Access VBA - Opening and Saving Excel

Post by Abraxus »

I have an Access db where I open an Excel workbook, make some changes, and want to save it. For some reason the save isn't working and I can't figure out why.

Code: Select all

Dim rs As DAO.Recordset
    Dim ApXL As Object
    Dim xlWBk As Object
    Dim xlWSh As Object
    Set ApXL = CreateObject("Excel.Application")
    Set xlWBk = ApXL.Workbooks.Open(CurrentDBDir & "OutputTemplate.xlsx")
 
    ApXL.Visible = True
    ApXL.ScreenUpdating = True
 
    'Top
    Set rs = CurrentDb.OpenRecordset("qrySlide3TopChart")
     
    Set xlWSh = xlWBk.Worksheets("Data")
 
    xlWSh.Activate
    xlWSh.Range("A3").Select
    xlWSh.Range("A3").CopyFromRecordset rs
    rs.Close
   
    'Bottom
    Set rs = CurrentDb.OpenRecordset("qrySlide3BottomChart")
      
    xlWSh.Activate
    xlWSh.Range("A17").Select
    xlWSh.Range("A17").CopyFromRecordset rs
    rs.Close
   
    'Full Details
    Set rs = CurrentDb.OpenRecordset("qryFinalDetails")
     
    'Set xlWSh = xlWBk.Worksheets("Data")
 
    xlWSh.Activate
    xlWSh.Range("P3").Select
    xlWSh.Range("P3").CopyFromRecordset rs
    rs.Close
   
    xlWBk.Worksheets("FinalDetails").Activate
   
    xlWBk.Worksheets("FinalDetails").Columns("E:E").Select
    xlWBk.Worksheets("FinalDetails").Columns("E:E").WrapText = True
    xlWBk.Worksheets("FinalDetails").Range("A1").Select
   
    xlWBk.Worksheets("Slide3Top").Activate
   
    'Save it
    ApXL.activeworkbook.SavecopyAs FileName:=CurrentDBDir & "Output\Legal Audit_" & Format(Now, "yyyymmdd_hhnnss") & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

I just get Run-time error 1004 Application defined or object-defined error.

Pointers?
Morgan

User avatar
StuartR
Administrator
Posts: 12857
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Access VBA - Opening and Saving Excel

Post by StuartR »

Did you really mean hhnnss or did you intend to use hhmmss?

If you set a variable equal to
CurrentDBDir & "Output\Legal Audit_" & Format(Now, "yyyymmdd_hhnnss")
Does it look like a valid filename?
StuartR


User avatar
Abraxus
3StarLounger
Posts: 263
Joined: 01 Mar 2010, 17:34
Location: Blue Springs, MO

Re: Access VBA - Opening and Saving Excel

Post by Abraxus »

It is. That gives me a timestamp with hours/minutes/seconds.

Even if I remove the entire date part and hard code a file name, it gives the error.
Morgan

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 16438
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Access VBA - Opening and Saving Excel

Post by ChrisGreaves »

StuartR wrote:
26 Aug 2024, 17:17
Does it look like a valid filename?
eraseme.jpg
It does in Excel2003 VBA - which surprises me. Now I want to know what the nn represents; I mean WHY "nn"?
Cheers, Chris
You do not have the required permissions to view the files attached to this post.
Most of my hair had already fallen out by the time I learned that mousse is spelled with two esses

User avatar
HansV
Administrator
Posts: 79697
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Access VBA - Opening and Saving Excel

Post by HansV »

In format strings in VBA, nn can be used for minutes to distinguish them from months.'

SaveCopyAs does not let you specify the file format. Change

ApXL.ActiveWorkbook.SaveCopyAs ...

to

xlWBk.SaveAs ...

Alternatively, remove the extra arguments from SaveCopyAs:

xlWbk.SaveCopyAs Filename:=CurrentDBDir & "Output\Legal Audit_" & Format(Now, "yyyymmdd_hhnnss") & ".xlsx"
Best wishes,
Hans

User avatar
Abraxus
3StarLounger
Posts: 263
Joined: 01 Mar 2010, 17:34
Location: Blue Springs, MO

Re: Access VBA - Opening and Saving Excel

Post by Abraxus »

Hans, that worked perfectly! Thanks for the correction to my code.
Morgan

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 16438
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Access VBA - Opening and Saving Excel

Post by ChrisGreaves »

HansV wrote:
26 Aug 2024, 18:30
In format strings in VBA, nn can be used for minutes to distinguish them from months.'
Well thanks Hans. I never knew that! A few more years and I'll have a pretty good grip on Office 2003 :grin:
Cheers, Chris
Most of my hair had already fallen out by the time I learned that mousse is spelled with two esses

User avatar
HansV
Administrator
Posts: 79697
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Access VBA - Opening and Saving Excel

Post by HansV »

See Format function, section Time Symbols.

n or nn for minutes is obligatory if you do not use h or hh in the format. If you do use one of those, you can use m or mm.
Example: it is 21:57 local time while I write this.
Format(Now, "mm") returns 08 (since it is August)
Format(Now, "nn") returns 57.
Format(Now, "hh:mm") and Format(Now, "hh:nn") both return 21:57.
Best wishes,
Hans

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 16438
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Access VBA - Opening and Saving Excel

Post by ChrisGreaves »

HansV wrote:
26 Aug 2024, 19:57
n or nn for minutes is obligatory if you do not use h or hh in the format. If you do use one of those, you can use m or mm.
Thanks. I am now officially boggled.
All my formatting life I have used "mm" for minutes.

Until 30 nimutes ago I would have bet on "mm" being the default and anything else (e.g. "nn") being an optional extra.
Now I read it as "nn" being mandatory UNLESS there is an "hh" trigger.

Something to think about as I drift off to sleep tonight ...
Cheers, and thanks again
Chris :scratch:
[later]But NOT including since DOS-days the TIME command which produces NO formatting prompt!
timeprompt.jpg
C.
You do not have the required permissions to view the files attached to this post.
Most of my hair had already fallen out by the time I learned that mousse is spelled with two esses