Excel - Insert worksheet from another workbook into active workbook Topic is solved

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
jarhead
Posts: 149
Joined: 09 Sep 2020, 12:43

Excel - Insert worksheet from another workbook into active workbook

Post by jarhead » 16 Jan 2022, 18:13

I've searched the forum and reviewed the VBA reference but I cannot get my script to work.

I'm trying to copy a worksheet from an unopened Excel workbook (.xlsx) and insert it as the first worksheet into my active workbook. The unopened workbook contains only one worksheet. The unopened workbook is named "Cover Page.xlsx" and the lone worksheet in the workbook is called Cover Page.

I've tried setting the path to the unopened workbook and tried using Copy unsuccessfully.

Here is my current script. Some of the referenced variables will be placed into the Cover Page sheet after it is inserted.

Code: Select all


#NoEnv
SetWorkingDir %A_ScriptDir%
#SingleInstance, Force

#z::		; win+z for testing purposes
	
FindFirst := "First Name"
FindLast := "Last Name"

SaveDate = %A_MM%-%A_dd%-%A_YYYY%	; date format → 01-16-2022
SavePath = %A_Desktop%\MyFolder

xl := ComObjActive("Excel.Application")		; connect to Excel
xl.Application.DisplayAlerts := False		; avoid alerts

FirstFound := xl.Sheets("Raw Data").Range("A:AZ").Find(FindFirst).Offset(1,0).Value	; First Name Value
LastFound := xl.Sheets("Raw Data").Range("A:AZ").Find(FindLast).Offset(1,0).Value	; Last Name Value

LastFirstUp = %LastFound%, %FirstFound%		; SMITH, JOHN
StringUpper, LastFirst, LastFirstUp, T		; Smith, John

TotalPaid := xl.Sheets("Billed").Range("J1").Value	; grabs value from cell J1
TotalPaid := StrReplace(TotalPaid, "Total Paid:`n", "")		; removes everything but amount

For sheet in xl.Worksheets
	if sheet.Name != "Billed"	; act on all sheets except this one
		sheet.delete					; delete other sheets

xl.Sheets("Billed").Name := "FINAL"	; rename sheet
xl.ActiveWorkbook.SaveAs(SavePath . "\" . LastFirst . " Ledger " . SaveDate,51)	; saves file as "Last Name, First Name Ledger 01-16-2022.xlsx"
WinWait, %LastFirst% Ledger %A_MM%-%A_dd%-%A_YYYY%

; More to come

return

Thanks.

User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: Excel - Insert worksheet from another workbook into active workbook  Topic is solved

Post by flyingDman » 16 Jan 2022, 19:04

To copy an entire worksheet from one workbook to another (the source is not open, the target is open) use this:

Code: Select all

xl := ComObjActive("excel.application")
path := "..........."				; use full path 

wrkbk1 := xl.activeworkbook
wrkbk2 := xl.Workbooks.Open(path,0,0)

wrkbk2.sheets(1).copy(wrkbk1.sheets(1))              ; or use sheets("name")
return
14.3 & 1.3.7

jarhead
Posts: 149
Joined: 09 Sep 2020, 12:43

Re: Excel - Insert worksheet from another workbook into active workbook

Post by jarhead » 16 Jan 2022, 19:41

Thanks flyingDman... I was using path incorrectly. Works great.

Post Reply

Return to “Ask for Help (v1)”