10-16-2019 01:57 AM
Hello,
I wanted to copy an Excel sheet from an Excel file and then save it as a separate file in a folder that I could define.
I write down the following Code:
Option Explicit
Dim myExcel, objWorkbook
Set myExcel = CreateObject("Excel.Application")
myExcel.Visible = false
Set objWorkbook = myExcel.Workbooks.Add
Call myExcel.Workbooks.Open("C:\Users\Root\Document\Data.xlsx")
myExcel.Sheets("Data_Sheet_2").Select
myExcel.Sheets("Data_Sheet_2").Copy
objWorkbook.SaveAs "C:\Users\Root\Download\test.xlsx"
The problem with this code is that the newly saved file opens two Excel files. One has the name "test.xlsx" (and is empty) and the other is called "Excel" and this file is the copied sheet from the main Excel file ("Data.xlsx").
Does anyone know why the saved file opens two Excel files and how I can fix this?
10-16-2019 12:25 PM - edited 10-16-2019 12:54 PM
Option Explicit 'Forces the explicit declaration of all the variables in a script. Dim myExcel, objWorkbook, objWorkSheet, objSheet Set myExcel = CreateObject("Excel.Application") myExcel.Visible = false Set objWorkbook = myExcel.Workbooks Set objWorkSheet = objWorkBook.Open("C:\Users\111660\Desktop\Data.xlsx") Call objWorkSheet.SaveAs("C:\Users\111660\Desktop\test.xlsx") Set objWorkSheet = objWorkbook.Open("C:\Users\111660\Desktop\test.xlsx") Call objWorkSheet.sheets("Sheet1").Delete() Call objWorkSheet.sheets("Sheet3").Delete() Call objWorkbook.Close()