Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

SaveAs problem

Forumthread: SaveAs problem

SaveAs problem
10.10.2016 09:29:02
Steven
Hi, everything here works except for the SAVECOPYAS function, it save a file called "FALSE" _
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

Anzeige

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: SaveAs problem
10.10.2016 10:21:00
Herbert
Hi Steven,
try this one:
TargetWorkbook.SaveCopyAs Filename = "Q:\SAP\PL_Analysis_" & Format(Date, "yyyy_mm_dd") & ".xlsx """
Do not use the "_" underline in this Code-Line.
Servus
AW: SaveAs problem
10.10.2016 10:50:54
Steven
Hi Herbert, no, it doesn't work either, same issue, Filename is saved as "FALSE" in a different directory. I have tried just saving the name without format, same result. The extra "_" underline was not in my program, this editor added it during the text wraparound.
Vielen Dank für die Unterstützung.
Anzeige
AW: SaveAs problem
10.10.2016 11:11:09
Herbert
Sorry Steven, im blind! ;o)=)
please use
ActiveWorkbook.SaveCopyAs
not
TargetWorkbook.SaveCopyAs
Try it again!
Servus
AW: SaveAs problem
10.10.2016 11:45:16
Steven
The ActiveWorkbook did not work, got a compilation error.
AW: SaveAs problem
10.10.2016 11:36:34
EtoPHG
Hi Steven,
Can you tell me the reason, for processing the PL_Analysis-File in a separate Instance of Excel?
IMHO you can't saveascopy from one instance to workbook opened in another Instance!
Greetz Hansueli
Anzeige
AW: SaveAs problem
10.10.2016 11:46:53
Steven
Hi Hansulie, don't really understand your question, I have an active workbook (targetworkbook) and want to copy this to a new name with today's date.
AW: SaveAs problem
10.10.2016 12:08:50
Steven
I think I know what you mean, so I deleted the SaveCopyas, closed the application and did a Filecopy command. This worked perfectly.
;

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige