Page 1 of 1
Excel Workbook Copy To
Posted: 08 Oct 2020, 18:39
by TheDewd
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?
Re: Excel Workbook Copy To
Posted: 08 Oct 2020, 19:34
by teadrinker
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)
}
I'm not sure if this code is comletely correct, but for me it works.
Re: Excel Workbook Copy To
Posted: 08 Oct 2020, 19:34
by FanaticGuru
TheDewd wrote: ↑08 Oct 2020, 18:39
I'm using AutoHotkey to create an Excel Workbook.
If I manually open an existing Workbook, and try to copy a Worksheet to the Workbook created with AutoHotkey, I can't.
The AHK Excel Workbook doesn't show as an available option to 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
Re: Excel Workbook Copy To
Posted: 08 Oct 2020, 19:39
by TheDewd
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
Posted: 08 Oct 2020, 19:45
by TheDewd
teadrinker wrote: ↑08 Oct 2020, 19:34
I'm not sure if this code is comletely correct, but for me it works.
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!
Re: Excel Workbook Copy To
Posted: 08 Oct 2020, 19:53
by FanaticGuru
TheDewd wrote: ↑08 Oct 2020, 18:39
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.
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
Re: Excel Workbook Copy To
Posted: 08 Oct 2020, 19:58
by TheDewd
@FanaticGuru
Thank you! Your code also works. I'll study each example provided. Thank you everyone!!!
Re: Excel Workbook Copy To
Posted: 08 Oct 2020, 20:08
by teadrinker
For me doesn't work.
Error: wrong index.
Re: Excel Workbook Copy To
Posted: 09 Oct 2020, 04:19
by Xeo786
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
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
Re: Excel Workbook Copy To Topic is solved
Posted: 09 Oct 2020, 08:17
by TheDewd
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 := ""
Re: Excel Workbook Copy To
Posted: 09 Oct 2020, 11:24
by FanaticGuru
teadrinker wrote: ↑08 Oct 2020, 20:08
For me doesn't work.
Error: wrong index.
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