Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1516to1520
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

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

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige