DIAdem

cancel
Showing results for 
Search instead for 
Did you mean: 

Copy and save an Excel sheet with Diadem from an existing Excel file

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?

0 Kudos
Message 1 of 2
(1,846 Views)
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()
Message 2 of 2
(1,789 Views)