hi. as the tile says, i am trying to create a macro that copies data from a quattro pro file from specific cells into calc. these are the mappings it’s intended to do:
from sheet “Daily” in quattro pro H47:H49 go to calcsheet “MetersTotal” p4:p6
from sheet “Daily” in quattro pro H55 goes to calc sheet “MetersTotal” p7
from sheet “Daily” in quattro pro O4:O16 go to calc sheet “MetersTotal” D3:D27
from sheet “Daily” in quattro pro O22:O31 go to calc sheet “MetersTotal” D37:46
from sheet “Daily” in quattro pro o37:46 go to calc sheet “MetersTotal” D46:d55
from sheet “Daily” in quattro pro o50 goes to calc sheet “MetersTotal” P7
from sheet “Daily” in quattro pro U4:U9 go to calc sheet “MetersTotal” P14:P19
from sheet “Daily” in quattro pro U15:u37 go to calc sheet “MetersTotal” J4:J26
from sheet “Daily” in quattro pro U43:u53 go to calc sheet “MetersTotal” J31:j42
Sub ImportFromQuattroProToCalc()
Dim oDoc As Object
Dim oSourceSheet As Object
Dim oDestSheet As Object
Dim oImporter As Object
Dim sFileURL As String
Dim sTempFileURL As String
' Set the file URL of the Quattro Pro spreadsheet to import
sFileURL = "file:///C:/Users/ale/desktop/seniorchuckies/2023-template.qpw"
' Create a TextImport service object
oImporter = CreateUnoService("com.sun.star.text.TextImport")
' Set the properties of the TextImport service object to import from Quattro Pro
oImporter.FilterName = "QuattroPro"
oImporter.SourceURL = sFileURL
oImporter.IsFirstRowHeader = True
' Import the data from cells H47:H49 in Quattro Pro to cells P4:P6 in Calc
oImporter.insertDocument(oSourceSheet.getCellRangeByName("H47:H49"), oDestSheet.getCellRangeByName("P4:P6"), Array())
' Import the data from cell H55 in Quattro Pro to cell P7 in Calc
oImporter.insertDocument(oSourceSheet.getCellRangeByName("H55:H55"), oDestSheet.getCellRangeByName("P7:P7"), Array())
' Import the data from cells O4:O16 in Quattro Pro to cells D3:D27 in Calc
oImporter.insertDocument(oSourceSheet.getCellRangeByName("O4:O16"), oDestSheet.getCellRangeByName("D3:D27"), Array())
' Import the data from cells O22:O31 in Quattro Pro to cells D37:D46 in Calc
oImporter.insertDocument(oSourceSheet.getCellRangeByName("O22:O31"), oDestSheet.getCellRangeByName("D37:D46"), Array())
' Import the data from cells O37:O46 in Quattro Pro to cells D46:D55 in Calc
oImporter.insertDocument(oSourceSheet.getCellRangeByName("O37:O46"), oDestSheet.getCellRangeByName("D46:D55"), Array())
' Import the data from cell O50 in Quattro Pro to cell P7 in Calc
oImporter.insertDocument(oSourceSheet.getCellRangeByName("O50:O50"), oDestSheet.getCellRangeByName("P7:P7"), Array())
' Import the data from cells U4:U9 in Quattro Pro to cells P14:P19 in Calc
oImporter.insertDocument(oSourceSheet.getCellRangeByName("U4:U9"), oDestSheet.getCellRangeByName("P14:P19"), Array())
' Import the data from cells U15:U37 in Quattro Pro to cells J4:J26 in Calc
oImporter.insertDocument(oSourceSheet.getCellRangeByName("U15:U37"), oDestSheet.getCellRangeByName("J4:J26"), Array())
' Import the data from cells U43:U53 in Quattro Pro to cells J31:J42 in Calc
oImporter.insertDocument(oSourceSheet.getCellRangeByName("U43:U53"), oDestSheet.getCellRangeByName("J31:J42"), Array())
End Sub
i am not a code writter, but i am having issues running that script with BASIC runtime error.
Object variable not set. can anyone be so kind as to help me fix it ?