CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Beginner:
VBA to Perl Conversion - Understanding QueryTables and such

 



bsully1215
New User

Oct 10, 2011, 12:07 PM

Post #1 of 1 (701 views)
VBA to Perl Conversion - Understanding QueryTables and such Can't Post

Hello,

I just started learning perl as of last week. I need to use it for work to be able to take current VBA Macros and convert them into Perl. I used ActiveState's VBA to Perl Converter to easily convert the script. However, I have to go through the Perl script and make the proper modifications to get the code to work through the OLE Excel properties. My problem is the following:

I run all of my code and get "Can't call method 'QueryTables' on an undefined value...". Here is the portion throwing off the error.


Code
     $Batch_No = $sheet = $workbook->Worksheets(8)->Range('B9')->{Value};  
$MarkerfileName = $sheet = $workbook->Worksheets(8)->Range('I2')->{Value};
$MarkerfilePath = $sheet = $workbook->Worksheets(8)->Range('H2')->{Value};

$sheet = $workbook->Worksheets(3);
$sheet->Range('A1')->{Value} = 'M-Value';
$sheet->Range('B1')->{Value} = 'Part';
$sheet->Range('C1')->{Value} = 'Rev';
{
$_with001 = $sheet->ActiveSheet->QueryTables->Add(
{Connection => 'TEXT;' . $MarkerfilePath . '\\' . $MarkerfileName,
Destination => $sheet->Range('$A$2')});
# "TEXT;" & MarkerfilePath & "\" & MarkerfileName, Destination:=Range("$A$2"))
# "TEXT;C:\pathto\pathto\phxmarker.txt", Destination:=Range("$A$2"))
$_with001->{Name} = 'phxmarker';
$_with001->{FieldNames} = 1;
$_with001->{RowNumbers} = 0;
$_with001->{FillAdjacentFormulas} = 0;
$_with001->{PreserveFormatting} = 1;
$_with001->{RefreshOnFileOpen} = 0;
$_with001->{RefreshStyle} = xlInsertDeleteCells;
$_with001->{SavePassword} = 0;
$_with001->{SaveData} = 1;
$_with001->{AdjustColumnWidth} = 0;
$_with001->{RefreshPeriod} = 0;
$_with001->{TextFilePromptOnRefresh} = 0;
$_with001->{TextFilePlatform} = 437;
$_with001->{TextFileStartRow} = 1;
$_with001->{TextFileParseType} = xlDelimited;
$_with001->{TextFileTextQualifier} = xlTextQualifierDoubleQuote;
$_with001->{TextFileConsecutiveDelimiter} = 0;
$_with001->{TextFileTabDelimiter} = 1;
$_with001->{TextFileSemicolonDelimiter} = 0;
$_with001->{TextFileCommaDelimiter} = 0;
$_with001->{TextFileSpaceDelimiter} = 0;
$_with001->{TextFileColumnDataTypes} = [2, 2, 2];
$_with001->{TextFileTrailingMinusNumbers} = 1;
$_with001->Refresh({BackgroundQuery => 0});
}
# Get the number of rows for the Range
# Stored as iRangeRows
$sheet->Range('A1');
$sheet->Selection->End(xlDown)->Select();
$sheet->ActiveCell->Offset(1, 0)->Select();
$sheet->ActiveCell->{Value} = '=@row()-1';
$iRangeRows = $sheet->ActiveCell->{Value};
$sheet->Range('A1');


I correctly referenced the OLE at the top of the Perl script, but the moment my code is run and it comes across QueryTables, I get an error. How could I alter the code in order to conduct the same properties as VBA?

The original VBA script that I converted from is this


Code
   
Batch_No = Sheets("SetupVars").Range("B9").Value
MarkerfileName = Sheets("SetupVars").Range("I2").Value
MarkerfilePath = Sheets("SetupVars").Range("H2").Value

Sheets("Sheet3").Select
Range("A1").Value = "M-Value"
Range("B1").Value = "Part"
Range("C1").Value = "Rev"
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & MarkerfilePath & "\" & MarkerfileName, Destination:=Range("$A$2"))
' "TEXT;" & MarkerfilePath & "\" & MarkerfileName, Destination:=Range("$A$2"))
'"TEXT;C:\pathto\pathto\phxmarker.txt", Destination:=Range("$A$2"))
.Name = "phxmarker"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
'Get the number of rows for the Range
' Stored as iRangeRows
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "=@row()-1"
iRangeRows = ActiveCell.Value
Range("A1").Select


Since the converter removed the "With...End With" Statements, would this affect the Perl version?

Also, how could I adjust this portion of code into proper Perl for OLE Excel?


Code
    $sheet->Range('A1');  
$sheet->Selection->End(xlDown)->Select();
$sheet->ActiveCell->Offset(1, 0)->Select();
$sheet->ActiveCell->{Value} = '=@row()-1';
$iRangeRows = $sheet->ActiveCell->{Value};
$sheet->Range('A1');


I would greatly appreciate the assistance. Smile


(This post was edited by bsully1215 on Oct 10, 2011, 12:43 PM)

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives