Excel Workbook Copy To Topic is solved
Excel Workbook Copy To
I have a few different CSV files I want to merge into a single Excel Workbook, with the contents of each CSV file on a separate WorkSheet.
Any ideas?
Any ideas?
-
- Posts: 4311
- Joined: 29 Mar 2015, 09:41
- Contact:
Re: Excel Workbook Copy To
Code: Select all
csv1 := "D:\test1.CSV"
csv2 := "D:\test2.CSV"
csv3 := "D:\test3.CSV"
CsvArr := [csv1, csv2, csv3]
outFilePath := A_ScriptDir . "\MyBook.xlsx"
Excel := ComObjCreate("Excel.Application")
XLBook := Excel.Workbooks.Add
for k, csvFile in CsvArr {
FileData := GetCsvFileData(csvFile)
IdentifyCodePage(FileData)
IdentifyDelimiter(FileData)
FileData.XLSht := k = 1 ? XLBook.ActiveSheet : XLBook.Worksheets.Add(, PrevSht)
PrevSht := FileData.XLSht
LoadDataFromCsv(FileData)
}
XLBook.SaveAs(outFilePath, xlWorkbookDefault := 51)
XLBook.Close(true, outFilePath)
DetectHiddenWindows, On
WinClose, % "ahk_id" . Excel.hwnd
Return
GetCsvFileData(csvFilePath) {
Data := {}
File := FileOpen(csvFilePath, "r")
Data.text := File.Read()
Data.codepage := File.Encoding
File.Close()
Data.filePath := csvFilePath
Return Data
}
IdentifyCodePage(Info) {
codePage := Info.codePage
RegExMatch(codePage, "i)^(utf-|cp)(\d+)$", cp)
codePage := ""
if (cp = "")
codePage := 1251
else if (cp1 = "cp")
codePage := cp2
else {
if (cp2 = 8)
codePage := 65001
else if (cp2 = 16)
codePage := 1200
}
(codePage = "" && codePage := 1251)
if ( codePage = 1251 && RegExMatch(Info.text, "Ў|Ђ|ћ|џ") )
codePage := 65001
Info.codePage := codePage
}
IdentifyDelimiter(Info) {
text := RegExReplace(Info.text, """[^""]+""")
StrReplace(text, ",", "", commaCount)
StrReplace(text, ";", "", semicolonCount)
ObjDelete(Info, "text")
Info.delimiter := commaCount > semicolonCount ? "," : ";"
}
LoadDataFromCsv(Info) {
Tbl := Info.XLSht.QueryTables.Add("TEXT;" . Info.filePath, Info.XLSht.Range("A1"))
Tbl.PreserveFormatting := true
Tbl.RefreshOnFileOpen := false
Tbl.RefreshStyle := xlInsertDeleteCells := 1
Tbl.AdjustColumnWidth := true
Tbl.RefreshPeriod := 0
Tbl.TextFilePlatform := Info.codePage
Tbl.TextFileStartRow := 1
Tbl.TextFileParseType := xlDelimited := 1
Tbl.TextFileTextQualifier := xlTextQualifierDoubleQuote := 1
Tbl.TextFileConsecutiveDelimiter := false
Tbl.TextFileTabDelimiter := false
Tbl.TextFileSemicolonDelimiter := Info.delimiter = ";" ? true : false
Tbl.TextFileCommaDelimiter := Info.delimiter = "," ? true : false
Tbl.TextFileSpaceDelimiter := false
Tbl.Refresh(false)
}
- FanaticGuru
- Posts: 1906
- Joined: 30 Sep 2013, 22:25
Re: Excel Workbook Copy To
Post your code.
This should all be very doable. It is possible you are getting multiple copies of the Excel application running and they cannot see each other, especially if you are using an older version of Excel.
FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
Re: Excel Workbook Copy To
I have the following in my script which opens two CSV files in Excel. I'm trying to figure out still how to copy all of the files into one Workbook on separate Worksheet tabs.
Using the Macro editor to see VBA commands to Copy, however by opening both files using AutoHotkey, the "Move or Copy" window only shows the Workbook that I initiate the menu from. It does not see the other Workbook. I'm not sure why.
Code: Select all
#SingleInstance, Force
fPath := "File1.csv"
XLSTT := ComObjCreate("Excel.Application")
XLSTT.Workbooks.Open(fPath, 0, 0)
XLSTT.Visible := True
fPath := "File2.csv"
XLCBD := ComObjCreate("Excel.Application")
XLCBD.Workbooks.Open(fPath, 0, 0)
XLCBD.Visible := True
Re: Excel Workbook Copy To
Your code seems to work with my data too. Thank you! I would have thought there was a simpler way, like copying worksheets from existing Workbooks, etc. If no one comes along with a more simple solution, this will definitely be what I'll use. Appreciate your help!teadrinker wrote: ↑08 Oct 2020, 19:34I'm not sure if this code is comletely correct, but for me it works.
- FanaticGuru
- Posts: 1906
- Joined: 30 Sep 2013, 22:25
Re: Excel Workbook Copy To
This will combine all the sheets from multiple Excel files, regardless of whether they are csv or xlsx. Each workbook can have multiple sheets, they will all be combined into one workbook.
Code: Select all
Files =
(Join`n
C:\Users\FG\Documents\2020-04-02-AccountStatement.csv
C:\Users\FG\Documents\2020-04-06-AccountStatement.csv
)
Xl := ComObjCreate("Excel.Application")
Xl.Visible := true
Xl.Application.ScreenUpdating := false
XlNewWB := Xl.Workbooks.Add
DefaultSheets := {}
for Sheet in XlNewWB.Worksheets
{
DefaultSheets.Push(Sheet)
Sheet.Name := "Something Unique " A_Index
}
Loop, Parse, Files, `n
{
Xl.Workbooks.Open(A_LoopField)
SplitPath, A_LoopField,,,, FileName
for Sheet in Xl.Workbooks(FileName).Worksheets
{
SheetCount ++
Xl.Workbooks(FileName).Worksheets(Sheet.Name).Copy(,XlNewWB.Worksheets(SheetCount))
}
Xl.Workbooks(FileName).Close
}
for key, Sheet in DefaultSheets
Sheet.Delete
Xl.Application.ScreenUpdating := true
The code could be streamlined some if you were sure each workbook would have only one sheet as then no need to loop through sheets.
There is also an oddity that when a new workbook is created, there are default sheets. Those cannot all be deleted to get a truly blank workbook, as a workbook always has to have at least one sheet. It creates a unique name for each of those default sheets, then deletes them at the end when there should be other real sheets by then.
FG
Last edited by FanaticGuru on 08 Oct 2020, 20:03, edited 2 times in total.
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
Re: Excel Workbook Copy To
@FanaticGuru
Thank you! Your code also works. I'll study each example provided. Thank you everyone!!!
Thank you! Your code also works. I'll study each example provided. Thank you everyone!!!
-
- Posts: 4311
- Joined: 29 Mar 2015, 09:41
- Contact:
Re: Excel Workbook Copy To
For me doesn't work.
Error: wrong index.
Error: wrong index.
Re: Excel Workbook Copy To
following lil code will merge all workbooks that are open in single excel application whcih should be active, into one workbook and ask for saveas
while merging it will close every workbook after getting all sheets merged one by one
so all workbook remain as previous you get new workbook
while merging it will close every workbook after getting all sheets merged one by one
so all workbook remain as previous you get new workbook
Code: Select all
xl := ComObjActive("excel.application")
for book in xl.workbooks
{
if (a_index = 1)
nbook := Book
else
{
for sheet in book.sheets
sheet.copy(,nbook.sheets(nbook.sheets.count)) ; expression.Copy (Before, After)
book.close(0)
}
}
xl.Dialogs(xlDialogSaveAs := 5).Show( a_desktop "\")
nbook.close()
xl.quit
return
"When there is no gravity, there is absolute vacuum and light travel with no time" -Game changer theory
Re: Excel Workbook Copy To Topic is solved
I examined everyone's code and found what works best for my purpose. Thank you, everyone! Let me know if you see any mistakes.
Code: Select all
#SingleInstance, Force
File := []
File[1] := A_ScriptDir "\MyFile1.csv"
File[2] := A_ScriptDir "\MyFile2.csv"
File[3] := A_ScriptDir "\MyFile3.csv"
File[4] := A_ScriptDir "\MyFile4.csv"
XL := ComObjCreate("Excel.Application")
XL.Visible := False
WB := XL.Workbooks.Add
SheetCount := 0
For Index, FilePath In File {
XL.Workbooks.Open(FilePath)
SplitPath, FilePath,,,, FileName
For Sheet In XL.Workbooks(FileName).Worksheets {
SheetCount++
XL.Workbooks(FileName).Worksheets(Sheet.Name).Copy(,WB.Worksheets(SheetCount))
}
XL.Workbooks(FileName).Close
}
WB.Sheets("Sheet1").Delete ; Delete default sheet
WB.SaveAs(A_ScriptDir "\Merged.xlsx", 51)
WB.Close(0)
XL.Quit
XL := ""
- FanaticGuru
- Posts: 1906
- Joined: 30 Sep 2013, 22:25
Re: Excel Workbook Copy To
It looks like the error is related to an invalid file name.
Your picture has some non-english letters in it so I am guessing this is something to do with language localization.
Could have something to do with file coding and how you entered the file names at the top of the script.
And of course you have to adjust the file names at the top to match files and paths actually on your computer.
I assume from the knowledge you have shown in other post you have made that you probably know most of this so I can not say exactly what the problem is.
FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks