Perl Copy data from csv to excel workbook
I am trying to open a csv file and copy the data from within it to paste it into a tab in an already constructed excel (2016) workbook.
my problem appears to be accessing the csv file properly.
Here is my code
use utf8;
use Cwd;
use warnings;
use strict;
use Win32::OLE;
use Win32::OLE qw(in with);
use Win32::OLE::Const "Microsoft Office .* Object Library";
my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit');
$Excel->{Visible} = 0;
$Excel->{DisplayAlerts}=0;
my $XLSX_LOG_IN = "book1.xlsx";
my $Book_In = $Excel->Workbooks->Open("$XLSX_LOG_IN") or die "Excel Logfile Workbook not opened - Ensure file 'book1.xlsx' is in the working directoryn";
my $csv_in = $Excel->Workbooks->Open("$csv") or die "Excel Logfile Workbook not opened - Ensure file "$csv" is in the working directoryn";
my @s_ins = in $csv_in->worksheets;
my $s_in = $s_ins[0];
my $name = $csv_in->Worksheets($s_in)->{Name};
print "$s_in - sheet I'm trying to openn";
print "$name - sheet I'm trying to openn";
my $res_sheet = $csv_in -> Worksheets("$name");
my $last_row = $res_sheet -> UsedRange -> Find({What => "*", SearchDirection => 2, SearchOrder => 1}) -> {Row};
my $last_col = $res_sheet -> UsedRange -> Find({What => "*", SearchDirection => 2, SearchOrder => 2}) -> {Column};
my $range = "A1:".$last_col.$last_row;
$res_sheet->range($range)->copy();
my $ResReport = $Book_In -> Worksheets("ModelReserveReport");
$ResReport->range('A1')->Select();
$ResReport->paste();
$csv_in->Close();
I keep getting the following error
Win32::OLE(0.1712) error 0x8002000b: "Invalid index"
I think I am either not pointing excel to the right name of the worksheet or I have the method for finding the last row and column wrong but I'm struggling to work out how to fix either.
Can anyone spot my mistake?
excel perl csv win32ole
asked Nov 26 '18 at 1:14
MicrobicTigerMicrobicTiger
95113
add a comment |
I am trying to open a csv file and copy the data from within it to paste it into a tab in an already constructed excel (2016) workbook.
my problem appears to be accessing the csv file properly.
Here is my code
use utf8;
use Cwd;
use warnings;
use strict;
use Win32::OLE;
use Win32::OLE qw(in with);
use Win32::OLE::Const "Microsoft Office .* Object Library";
my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit');
$Excel->{Visible} = 0;
$Excel->{DisplayAlerts}=0;
my $XLSX_LOG_IN = "book1.xlsx";
my $Book_In = $Excel->Workbooks->Open("$XLSX_LOG_IN") or die "Excel Logfile Workbook not opened - Ensure file 'book1.xlsx' is in the working directoryn";
my $csv_in = $Excel->Workbooks->Open("$csv") or die "Excel Logfile Workbook not opened - Ensure file "$csv" is in the working directoryn";
my @s_ins = in $csv_in->worksheets;
my $s_in = $s_ins[0];
my $name = $csv_in->Worksheets($s_in)->{Name};
print "$s_in - sheet I'm trying to openn";
print "$name - sheet I'm trying to openn";
my $res_sheet = $csv_in -> Worksheets("$name");
my $last_row = $res_sheet -> UsedRange -> Find({What => "*", SearchDirection => 2, SearchOrder => 1}) -> {Row};
my $last_col = $res_sheet -> UsedRange -> Find({What => "*", SearchDirection => 2, SearchOrder => 2}) -> {Column};
my $range = "A1:".$last_col.$last_row;
$res_sheet->range($range)->copy();
my $ResReport = $Book_In -> Worksheets("ModelReserveReport");
$ResReport->range('A1')->Select();
$ResReport->paste();
$csv_in->Close();
I keep getting the following error
Win32::OLE(0.1712) error 0x8002000b: "Invalid index"
I think I am either not pointing excel to the right name of the worksheet or I have the method for finding the last row and column wrong but I'm struggling to work out how to fix either.
Can anyone spot my mistake?
excel perl csv win32ole
asked Nov 26 '18 at 1:14
MicrobicTigerMicrobicTiger
95113
add a comment |
I am trying to open a csv file and copy the data from within it to paste it into a tab in an already constructed excel (2016) workbook.
my problem appears to be accessing the csv file properly.
Here is my code
use utf8;
use Cwd;
use warnings;
use strict;
use Win32::OLE;
use Win32::OLE qw(in with);
use Win32::OLE::Const "Microsoft Office .* Object Library";
my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit');
$Excel->{Visible} = 0;
$Excel->{DisplayAlerts}=0;
my $XLSX_LOG_IN = "book1.xlsx";
my $Book_In = $Excel->Workbooks->Open("$XLSX_LOG_IN") or die "Excel Logfile Workbook not opened - Ensure file 'book1.xlsx' is in the working directoryn";
my $csv_in = $Excel->Workbooks->Open("$csv") or die "Excel Logfile Workbook not opened - Ensure file "$csv" is in the working directoryn";
my @s_ins = in $csv_in->worksheets;
my $s_in = $s_ins[0];
my $name = $csv_in->Worksheets($s_in)->{Name};
print "$s_in - sheet I'm trying to openn";
print "$name - sheet I'm trying to openn";
my $res_sheet = $csv_in -> Worksheets("$name");
my $last_row = $res_sheet -> UsedRange -> Find({What => "*", SearchDirection => 2, SearchOrder => 1}) -> {Row};
my $last_col = $res_sheet -> UsedRange -> Find({What => "*", SearchDirection => 2, SearchOrder => 2}) -> {Column};
my $range = "A1:".$last_col.$last_row;
$res_sheet->range($range)->copy();
my $ResReport = $Book_In -> Worksheets("ModelReserveReport");
$ResReport->range('A1')->Select();
$ResReport->paste();
$csv_in->Close();
I keep getting the following error
Win32::OLE(0.1712) error 0x8002000b: "Invalid index"
I think I am either not pointing excel to the right name of the worksheet or I have the method for finding the last row and column wrong but I'm struggling to work out how to fix either.
Can anyone spot my mistake?
excel perl csv win32ole
asked Nov 26 '18 at 1:14
MicrobicTigerMicrobicTiger
95113
I am trying to open a csv file and copy the data from within it to paste it into a tab in an already constructed excel (2016) workbook.
my problem appears to be accessing the csv file properly.
Here is my code
use utf8;
use Cwd;
use warnings;
use strict;
use Win32::OLE;
use Win32::OLE qw(in with);
use Win32::OLE::Const "Microsoft Office .* Object Library";
my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit');
$Excel->{Visible} = 0;
$Excel->{DisplayAlerts}=0;
my $XLSX_LOG_IN = "book1.xlsx";
my $Book_In = $Excel->Workbooks->Open("$XLSX_LOG_IN") or die "Excel Logfile Workbook not opened - Ensure file 'book1.xlsx' is in the working directoryn";
my $csv_in = $Excel->Workbooks->Open("$csv") or die "Excel Logfile Workbook not opened - Ensure file "$csv" is in the working directoryn";
my @s_ins = in $csv_in->worksheets;
my $s_in = $s_ins[0];
my $name = $csv_in->Worksheets($s_in)->{Name};
print "$s_in - sheet I'm trying to openn";
print "$name - sheet I'm trying to openn";
my $res_sheet = $csv_in -> Worksheets("$name");
my $last_row = $res_sheet -> UsedRange -> Find({What => "*", SearchDirection => 2, SearchOrder => 1}) -> {Row};
my $last_col = $res_sheet -> UsedRange -> Find({What => "*", SearchDirection => 2, SearchOrder => 2}) -> {Column};
my $range = "A1:".$last_col.$last_row;
$res_sheet->range($range)->copy();
my $ResReport = $Book_In -> Worksheets("ModelReserveReport");
$ResReport->range('A1')->Select();
$ResReport->paste();
$csv_in->Close();
I keep getting the following error
Win32::OLE(0.1712) error 0x8002000b: "Invalid index"
I think I am either not pointing excel to the right name of the worksheet or I have the method for finding the last row and column wrong but I'm struggling to work out how to fix either.
Can anyone spot my mistake?
excel perl csv win32ole
excel perl csv win32ole
asked Nov 26 '18 at 1:14
MicrobicTigerMicrobicTiger
95113
asked Nov 26 '18 at 1:14
MicrobicTigerMicrobicTiger
95113
edited Nov 26 '18 at 1:28
MicrobicTiger
asked Nov 26 '18 at 1:14
MicrobicTigerMicrobicTiger
95113
asked Nov 26 '18 at 1:14
MicrobicTigerMicrobicTiger
95113
asked Nov 26 '18 at 1:14
MicrobicTigerMicrobicTiger
95113
95113
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
I fixed this by editiing my last 6 lines of code
I made the selection on the source sheet
my $range = "A1:CZ".$last_row;
$res_sheet->range($range)->copy();
and removed the selection before pasting my data
my $ResReport = $Book_In -> Worksheets("ModelReserveReport");
$ResReport->paste();
$csv_in->Close();
The copy/paste now works.
answered Dec 10 '18 at 3:19
MicrobicTigerMicrobicTiger
95113
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53473627%2fperl-copy-data-from-csv-to-excel-workbook%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
I fixed this by editiing my last 6 lines of code
I made the selection on the source sheet
my $range = "A1:CZ".$last_row;
$res_sheet->range($range)->copy();
and removed the selection before pasting my data
my $ResReport = $Book_In -> Worksheets("ModelReserveReport");
$ResReport->paste();
$csv_in->Close();
The copy/paste now works.
answered Dec 10 '18 at 3:19
MicrobicTigerMicrobicTiger
95113
add a comment |
I fixed this by editiing my last 6 lines of code
I made the selection on the source sheet
my $range = "A1:CZ".$last_row;
$res_sheet->range($range)->copy();
and removed the selection before pasting my data
my $ResReport = $Book_In -> Worksheets("ModelReserveReport");
$ResReport->paste();
$csv_in->Close();
The copy/paste now works.
answered Dec 10 '18 at 3:19
MicrobicTigerMicrobicTiger
95113
add a comment |
I fixed this by editiing my last 6 lines of code
I made the selection on the source sheet
my $range = "A1:CZ".$last_row;
$res_sheet->range($range)->copy();
and removed the selection before pasting my data
my $ResReport = $Book_In -> Worksheets("ModelReserveReport");
$ResReport->paste();
$csv_in->Close();
The copy/paste now works.
answered Dec 10 '18 at 3:19
MicrobicTigerMicrobicTiger
95113
I fixed this by editiing my last 6 lines of code
I made the selection on the source sheet
my $range = "A1:CZ".$last_row;
$res_sheet->range($range)->copy();
and removed the selection before pasting my data
my $ResReport = $Book_In -> Worksheets("ModelReserveReport");
$ResReport->paste();
$csv_in->Close();
The copy/paste now works.
answered Dec 10 '18 at 3:19
MicrobicTigerMicrobicTiger
95113
answered Dec 10 '18 at 3:19
MicrobicTigerMicrobicTiger
95113
answered Dec 10 '18 at 3:19
MicrobicTigerMicrobicTiger
95113
answered Dec 10 '18 at 3:19
MicrobicTigerMicrobicTiger
95113
95113
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53473627%2fperl-copy-data-from-csv-to-excel-workbook%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
