-
Oct 16th, 2017, 01:58 AM
#1
Thread Starter
New Member
Copying a very wide CSV file to Excel fails with error message
Hello,
A complete Newbie to VB Scripting. I have a table with over 260 columns which I am writing to a CSV file from SSIS. Due to excel limitations, I cannot write directly to a pre-formatted excel template with a header.
So I am looking at using a script task in SSIS which will copy the CSV without the header to the Excel file starting at a specific row (A4).
I am trying the below first from the command line, but it fails with an error message:
"The information cannot be posted because the copy area and paste area are not the same size. "
However, the CSV and the Excel template are identical.
What am I doing wrong here?
is there a better way to do write this script so that it can be invoked from SSIS?
Thanks in advance,
KB
Dim xlApp
Dim xlBook
Dim CSVBook
'Opens the Excel file in Excel
Set xlApp = CreateObject("Excel.Application")
xlApp.visible = true
Set xlBook = xlApp.Workbooks.open("C:\TEMPLATE.xls")
Set CSVBook = xlApp.Workbooks.open("C:\active.csv")
Dim xlToRight
Dim xlDown
xlToRight=-4161
xlDown=-4121
CSVBook.ActiveSheet.Range("A1").Select
CSVBook.Activesheet.Range(xlApp.Selection, xlApp.Selection.End(xlToRight)).Select
CSVBook.Activesheet.Range(xlApp.Selection, xlApp.Selection.End(xlDown)).Select
xlApp.Selection.Copy
xlBook.Activate
xlBook.Sheets("MTD").Select
xlBook.sheets("MTD").Range("A4").Select
xlBook.sheets("MTD").Paste
xlApp.quit
Set xlApp = Nothing
CSVBook.close
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|