
bsully1215
New User
Oct 10, 2011, 12:07 PM
Post #1 of 1
(1361 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. $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 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? $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.
(This post was edited by bsully1215 on Oct 10, 2011, 12:43 PM)
|