SaveAs problem
10.10.2016 09:29:02
Steven
under "MyDocuments" instead of the name and path given. Any ideas?
Function PL_Overview()
Dim dbs As Database
Set dbs = CurrentDb
' Opening first Query in Access, it runs in the background
Set rsQuery = dbs.OpenRecordset("PL Auswertung, Lagerbestandswert, Pass 06 TotalSum")
' Opening Excel File PL_Analysis, Worksheet Overview and Deleting Rows, Reloading Rows from _
Access
Set excelApp = CreateObject("Excel.application", "")
excelApp.Visible = True
Set TargetWorkbook = excelApp.Workbooks.Open("Q:\SAP\PL_Analysis_20161005 - Kopie.xlsx")
TargetWorkbook.Sheets("Overview").Rows(4).Delete
TargetWorkbook.Worksheets("Overview").Range("A4").CopyFromRecordset rsQuery
' Running the Acces Query to delete and reload the Worksheet Detail from Overview
Set rsQuery = dbs.OpenRecordset("PL Auswertung, Lagerbestandswert, Pass 05a - Detail Output")
TargetWorkbook.Sheets("Detail from Overview").Rows(4).Delete
TargetWorkbook.Worksheets("Detail from Overview").Range("A4").CopyFromRecordset rsQuery
' Running the Acces Query to delete and reload the Worksheet Cust Consignment
Set rsQuery = dbs.OpenRecordset("PL Auswertung, Lagerbestandswert, Pass 02c, Konsi Report PL")
TargetWorkbook.Sheets("Cust Consignment").Rows(4).Delete
TargetWorkbook.Worksheets("Cust Consignment").Range("A4").CopyFromRecordset rsQuery
' Running the Acces Query to delete and reload the Worksheet Inventory per Purchaser
Set rsQuery = dbs.OpenRecordset("PL Auswertung, Lagerbestandswert, Pass 07 - Bestand nach EKG")
TargetWorkbook.Sheets("Inventory per Purchaser").Rows(10).Delete
TargetWorkbook.Worksheets("Inventory per Purchaser").Range("A10").CopyFromRecordset rsQuery
' Refreshing Pivot Table in Worksheet Inventory per Purchaser
TargetWorkbook.Worksheets("Inventory per Purchaser").PivotTables("PivotTable4").RefreshTable
TargetWorkbook.Worksheets("Inventory per Purchaser").PivotTables("PivotTable1").RefreshTable
' Activating Worksheet Overview
TargetWorkbook.Sheets("Overview").Activate
' Closing Excel File and Saves Changes
TargetWorkbook.SaveCopyAs FileName = "Q:\SAP\PL_Analysis_" & Format(Date, "yyyy_mm_dd") & ". _
xlsx"
TargetWorkbook.Close SaveChanges:=False
' Quits the Excel Application
'excelApp.Quit
' Set excelApp = Nothing
MsgBox "Import is finished, file has been saved."
End Function