Access VBA - Opening and Saving Excel

[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?

[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?

[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.

[ChrisGreaves]

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

[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"

[Abraxus]

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

[ChrisGreaves]

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
Cheers, Chris

[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.

[ChrisGreaves]

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
[later]But NOT including since DOS-days the TIME command which produces NO formatting prompt!

timeprompt.jpg

C.