Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Copy Excel Columns to New Workbook

Status
Not open for further replies.

Nu2Java

Technical User
Jun 5, 2012
166
0
0
US
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).

Code:
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
 
Hi,

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


xlValues is -4163



Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/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!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top