Copy Excel Columns to New Workbook

[Nu2Java]

Hi All,

I am trying to copy 3 columns from an existing workbook and paste those columns to a NEW workbook, but I am getting errors that the method of PasteSpecial failed.

Here is what I have right now, which does seem to create the new workbook and paste the values in, but then fails to save and close. This also only works if I use 'On Error Resume Next' (Workbook 'A' is existing).

Set objExcel = CreateObject("Excel.Application")
strFileName = "C:\Scripts\B.xlsx"
objExcel.Visible = True

Set objNewFile = objExcel.Workbooks.Add()
objNewFile.SaveAs(strFileName)


Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\A.xlsx")
'Set objNewFile = objExcel.Workbooks.Open("C:\Scripts\B.xlsx")

Set objWorksheet = objWorkbook.Worksheets(1)

Set objRange = objWorksheet.Range("A:C").EntireColumn
objRange.Copy

Set objWorksheet2 = objNewFile.Worksheets(1)

objWorksheet.Range("A:C").Copy objWorksheet2.Range("A:C")
objWorksheet2.Range("A:C").PasteSpecial xlValues

objWorkBook.Close
objNewFile.Save
objNewFile.Close

 

[SkipVought]

Hi,

xlValues is an Excel Application constant, that you don't have library access to in VBScript.

https://docs.microsoft.com/en-us/office/vba/api/excel.xlpastetype

xlValues is -4163



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!