Save Excel Sheet as, and then break links... How?

[BadgerLikeSpeed]

Hello again...
More questions! I have a relatively complicated excel workbook where the information is pulled from data from an excel book created from multiple queries. That sounds a lot more complicated than it is, but basically to get the data into the required format my final book links to various cells in the book created by access.
I've written the code to export everything and open the final sheet, which updates my data ok. What I need help with is then saving the final book with a new file name (preferably the date of the report, which is in a text box in a form), close the master copy of my book, and then break the links in the filled version of the book.
Basically:
1) Export data to Temp.xlsx <-- Already achieved
2) Open Master.xlsx <-- Already achieved
3) Save a copy of Master.xlsx as [ReportDate]Report.xlsx
4) Break links in [ReportDate]Report.xlsx
5) Delete Temp.xlsx (Kill Temp.xlsx?)
5) Close Excel

    Dim xlApp As Object
 
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    xlApp.Workbooks.Open LPath & "GasReportBlank.xlsx"
 
    'Save as...
 
    xlApp.SaveAs LPath & "GasReport.xlsx"
 
    xlApp.Quit
 
    Set xlApp = Nothing

I get a run time error 438 at the Save as command, Object does not support this property or method.

I've found the ActiveWorkbook.BreakLink command, but I'm not sure how to use it, and also I need to be certain that it's not the master sheet where the links are broken.

Just to clarify, I'm running all of this out of my access database. I have a command button that takes a date from a form, runs a set of queries based on that date, and then exports it.

Any help would be massively appreciated!

 

[pwbrown]

This thread might be some use to you regarding this: http://www.access-programmers.co.uk/forums/showthread.php?t=148802

 

[BadgerLikeSpeed]

    Dim xlApp As Object
    
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    xlApp.Workbooks.Open LPath & "GasReportBlank.xlsx", UpdateLinks:=xlUpdateLinksAlways
    
    'Save as...
    
    xlApp.ActiveWorkbook.SaveAs LPath & "GasReport.xlsx"
    
    xlApp.Quit
    
    Set xlApp = Nothing

That solved my save as error, but why are my links not updating automatically? When I open up the new file the data is still as per the previous report...

 

[BadgerLikeSpeed]

Further progress...

    Dim ReportDate As String
    ReportDate = Forms![Start]![StartDate]
    xlApp.ActiveWorkbook.SaveAs LPath & ReportDate & "GasReport.xlsx"

Gets me the start date - but in the format dd/mm/yy... How do I cut out the "/", and preferably change the file name to yymmdd to keep it organised. I need to get the date from the box as the report date might be different from the date of creation.
Very mucha work in progress!!

 

[BadgerLikeSpeed]

Further progress...

    Dim ReportDate As String
    Dim ReportDate1 As String
    Dim DDay As String
    Dim DMonth As String
    Dim DYear As String
    
    ReportDate1 = Forms![Start]![StartDate]
    DDay = Left(ReportDate1, 2)
    DMonth = Left(Right(ReportDate1, 5), 2)
    DYear = Right(ReportDate1, 2)
    ReportDate = DYear & "-" & DMonth & "-" & DDay

Gets me the date from the text box, and rearranges it - is there a neater way of doing it?

 

[BadgerLikeSpeed]

The saga continues...
My logic went something like this: I know how to break links from inside excel, so I recorded a macro to do this, then edited it to get the appropriate command, copied it accross to my code in access. The result was this:

xlApp.ActiveWorkbook.BreakLink Name:="FILENAME", Type:=xlExcelLinks

The Excel macro started at ActiveWorkbook, so when I moved it accross I added the xlApp to the beginning, and now when I run my code I get 'Run Time Error 5 Invalis Procedure Call or Argument' from this statement.