Perl Guru Forums: Perl Programming Help: Beginner: VBA to Perl Conversion

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)


	MAIN INDEX	SEARCH POSTS	WHO'S ONLINE	LOG IN