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