Excel Workbook Copy To Topic is solved

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
User avatar
TheDewd
Posts: 1508
Joined: 19 Dec 2013, 11:16
Location: USA

Excel Workbook Copy To

Post by TheDewd » 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.

Any ideas?

teadrinker
Posts: 4311
Joined: 29 Mar 2015, 09:41
Contact:

Re: Excel Workbook Copy To

Post by teadrinker » 08 Oct 2020, 19:34

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.

User avatar
FanaticGuru
Posts: 1906
Joined: 30 Sep 2013, 22:25

Re: Excel Workbook Copy To

Post by FanaticGuru » 08 Oct 2020, 19:34

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

User avatar
TheDewd
Posts: 1508
Joined: 19 Dec 2013, 11:16
Location: USA

Re: Excel Workbook Copy To

Post by TheDewd » 08 Oct 2020, 19:39

FanaticGuru wrote:
08 Oct 2020, 19:34
Post your code.
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

User avatar
TheDewd
Posts: 1508
Joined: 19 Dec 2013, 11:16
Location: USA

Re: Excel Workbook Copy To

Post by TheDewd » 08 Oct 2020, 19:45

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! :D

User avatar
FanaticGuru
Posts: 1906
Joined: 30 Sep 2013, 22:25

Re: Excel Workbook Copy To

Post by FanaticGuru » 08 Oct 2020, 19:53

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

User avatar
TheDewd
Posts: 1508
Joined: 19 Dec 2013, 11:16
Location: USA

Re: Excel Workbook Copy To

Post by TheDewd » 08 Oct 2020, 19:58

@FanaticGuru
Thank you! Your code also works. I'll study each example provided. Thank you everyone!!! :wave:

teadrinker
Posts: 4311
Joined: 29 Mar 2015, 09:41
Contact:

Re: Excel Workbook Copy To

Post by teadrinker » 08 Oct 2020, 20:08

For me doesn't work.
 
 Image
 
Error: wrong index.

User avatar
Xeo786
Posts: 759
Joined: 09 Nov 2015, 02:43
Location: Karachi, Pakistan

Re: Excel Workbook Copy To

Post by Xeo786 » 09 Oct 2020, 04:19

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
"When there is no gravity, there is absolute vacuum and light travel with no time" -Game changer theory

User avatar
TheDewd
Posts: 1508
Joined: 19 Dec 2013, 11:16
Location: USA

Re: Excel Workbook Copy To  Topic is solved

Post by TheDewd » 09 Oct 2020, 08:17

I examined everyone's code and found what works best for my purpose. Thank you, everyone! Let me know if you see any mistakes. :thumbup:

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 := ""

User avatar
FanaticGuru
Posts: 1906
Joined: 30 Sep 2013, 22:25

Re: Excel Workbook Copy To

Post by FanaticGuru » 09 Oct 2020, 11:24

teadrinker wrote:
08 Oct 2020, 20:08
For me doesn't work.
 
 Image
 
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
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

Post Reply

Return to “Ask for Help (v1)”