Mcode Test - 2

[stanl]

This is the bad news. Using the same Mcode text file from an earlier thread the code below attempts to create a Power Query from a newly opened workbook.

   
• It does create the connection and saves workbook with connection
• It DOES NOT load the data into a worksheet
• You can manually open the saved file, go to Data, open the connections, right-click on connection [when that panel opens] and from the drop down choose load into - where you will get a pop-up dialog.

There is some commented code where I tried to get around the issue and actually populate via ListObjects. Maybe someone has a solution. Also this issue may not arise with Power Queries to databases, large downloaded text files or existing excel tables or ranges.


I again included the Mcode text to go with the code.

Code (WINBATCH) Select Expand



;Winbatch 2020b - Testing Excel Power Query
;Requires Excel 2016 or Higher or Power Query added to Excel 2013
;very basic test to create Power Query from template text file
;Stan Littlefield  April 10, 2021
IntControl(73,1,0,0,0)
gosub udfs


cXLS = Dirscript():"sunrise_new.xlsx"
cMcode = Dirscript():"sunrise.txt"
If FileExist(cXLS) Then FileDelete(cXLS)
If ! FileExist(cMcode) Then Terminate(@TRUE,"Cannot Continue",cMcode:" not found")


cMonth = "April" ;change as needed
qry = "Sunrise" ; should have been displayed with chkit udf
cn = "Query - ":qry
newcode = FileGet(cMcode)
newcode = StrReplace(newcode,"|MTH|",cMonth)






BoxOpen("Please Wait","Creating Excel Power Query Web Connection")
oXL = CreateObject("Excel.Application")
oXL.Visible          = @TRUE  ; change this to @FALSE to run hidden
oXL.ScreenUpdating   = @TRUE  ; if running hidden, change this to @FALSE
oXL.UserControl      = @TRUE
oXL.DisplayAlerts    = @FALSE
oXL.WorkBooks.Add()
BoxShut()
oWS = oXL.ActiveWorkBook.Worksheets(1)
oWS.Activate()
oWS.Name = qry
BoxOpen("Please Wait","Creating Query/Saving Workbook/Then Close Excel")
oXL.ActiveWorkBook.Queries.Add(::Name=qry,Formula=newcode,Description="Sunrise Query")
;at this point the connection is created but data not sent to worksheet
;next lines were my best try - but give exception error
;this
;oWS.ListObjects.Add(::SourceType=3,Source=qry,Destination=oWS.Range("A1"))
;or this
;oWS.ListObjects.Add(::SourceType=3,Source=cn,Destination=oWS.Range("A1"))




;the connection will be created in workbook, data not displayed
oXL.ActiveWorkbook.SaveAs(cXLS)
oXL.ActiveWorkbook.Close()
oXL.Quit()
BoxShut()


oWS=0
oXL=0
Pause("All Done","Saved as ":cXLS)


Exit


;======================================================================================================
:WBERRORHANDLER
Terminate(@TRUE,"Error Encountered",geterror())
;======================================================================================================


:CANCEL
Display(2,"Thank you","Goodbye...")
Exit




:udfs
#DefineSubRoutine geterror()
   wberroradditionalinfo = wberrorarray[6]
   lasterr = wberrorarray[0]
   handlerline = wberrorarray[1]
   textstring = wberrorarray[5]
   linenumber = wberrorarray[8]
   errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
   Return(errmsg)
#EndSubRoutine




Return






[stanl]

2nd time a charm ;) .  Adapted some VBA code for creating the ListObject/QueryTable and script now works - i.e. create Power Query from M code text file and blank workbook.

Code (WINBATCH) Select Expand



;Winbatch 2020b - Testing Excel Power Query
;Requires Excel 2016 or Higher or Power Query added to Excel 2013
;very basic test to create Power Query from template text file
;Stan Littlefield  April 10, 2021
IntControl(73,1,0,0,0)
gosub udfs


cXLS = Dirscript():"sunrise_new.xlsx"
cMcode = Dirscript():"sunrise.txt"
If FileExist(cXLS) Then FileDelete(cXLS)
If ! FileExist(cMcode) Then Terminate(@TRUE,"Cannot Continue",cMcode:" not found")


cMonth = "April" ;change as needed
qry = "Sunrise"
cn = "Query - ":qry
newcode = FileGet(cMcode)
newcode = StrReplace(newcode,"|MTH|",cMonth)






BoxOpen("Please Wait","Creating Excel Power Query Web Connection")
oXL = CreateObject("Excel.Application")
oXL.Visible          = @TRUE  ; change this to @FALSE to run hidden
oXL.ScreenUpdating   = @TRUE  ; if running hidden, change this to @FALSE
oXL.UserControl      = @TRUE
oXL.DisplayAlerts    = @FALSE
oXL.WorkBooks.Add()
BoxShut()
oWS = oXL.ActiveWorkBook.Worksheets(1)
oWS.Activate()
oWS.Name = qry
BoxOpen("Please Wait","Creating Query/Saving Workbook/Then Close Excel")
oXL.ActiveWorkBook.Queries.Add(::Name=qry,Formula=newcode,Description="Sunrise Query")
;at this point the connection is created but data not sent to worksheet
;create a ListObject with OLEDB connection string
cSource = "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=%qry%;Extended Properties=''"
qt = oWS.ListObjects.Add(::SourceType=0,Source=cSource,Destination=oWS.Range("$A$1")).QueryTable
qt.CommandText = "Select * FROM [%qry%]"
qt.Refresh()
;may take some time but should save
oXL.ActiveWorkbook.SaveAs(cXLS)
oXL.ActiveWorkbook.Close()
oXL.Quit()
BoxShut()


oWS=0
oXL=0
Pause("All Done","Saved as ":cXLS)


Exit


;======================================================================================================
:WBERRORHANDLER
Terminate(@TRUE,"Error Encountered",geterror())
;======================================================================================================


:CANCEL
Display(2,"Thank you","Goodbye...")
Exit




:udfs
#DefineSubRoutine geterror()
   wberroradditionalinfo = wberrorarray[6]
   lasterr = wberrorarray[0]
   handlerline = wberrorarray[1]
   textstring = wberrorarray[5]
   linenumber = wberrorarray[8]
   errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
   Return(errmsg)
#EndSubRoutine


Return