[sims] SIMS.net scheduled report

[KieranL]

Hi,

 

I have currently made a SIMS report with macros built in and it works correctly if ran manually. However, I want the report to run automatically and for the Macros to load with it.

 

I have tried commandreporter however this only seems to save the excel file as a .csv but I am after a .xlsm.

 

Is this possible?

 

Thanks

[Boredguy]

If you use the CommandReporter to call the scheduled report, it can only export it as a csv or txt file. The xlsm only works when calling it within the main SIMS instance, since it generates the csv/txt file then launches Excel to read it and apply the macros (and if your only using OneDrive onDemand as your user storage, the Macros only works for the first report of the day)

[garbage46]

here is a bit of a hack solution, but it should work.

 

Steps below:

(1) Scheduled Batch File to Run CommandReporter to get data in .csv then open an prepared Excel workbook

(2) Use the Workbook_Open() function to run the code below that will clear the sheet and import the new .csv

(3) add in your code to create the Pivot Table that generates the house points score

(4) add in the code to export cell to .txt

 

3 and 4 can be separate functions called from Workbook_Open() or just included in one massive Sub

 

BATCH FILE

"C:\program Files (x86)\SIMS\SIMS .net\commandreporter.exe" /user:[color="#0000FF"]edugeek[/color] /password:[color="#0000FF"]12345678 [/color]/report:"[color="#0000FF"]housepoints export to text[/color]" /output:"[color="#0000FF"]f:\datasync\housepoints.txt[/color]"
start excel.exe "[color="#0000FF"]f:\datasync\spreadsheet.xlsm[/color]"

 

EXCEL CODE

Private Sub Workbook_Open()
   Dim Ws As Worksheet
   Dim FileName As String

   Set Ws = ActiveWorkbook.Sheets("[color="#FF0000"]Sheet1[/color]") [color="#008000"]'CHANGE AS NEEDED[/color]

   FileName = "[color="#0000FF"]f:\datasync\housepoints.txt[/color]"

  [color="#008000"] ' *** CODE REPEATS TWICE TO CLEAR TO PREVIOUS DATA PROPERLY ***[/color]
   Cells.Select
   Selection.ClearContents
   
   Range("A1").Select
   
   With Ws.QueryTables.Add(Connection:="TEXT;" & FileName, _
                           Destination:=Ws.Range("[color="#0000FF"]A1[/color]"))
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .Refresh
   End With
       
   Cells.Select
   Selection.QueryTable.Delete
   Selection.ClearContents
   
   Range("A1").Select
   
   With Ws.QueryTables.Add(Connection:="TEXT;" & FileName, _
                           Destination:=Ws.Range("[color="#0000FF"]A1[/color]"))
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .Refresh
   End With
  
   [color="#0000FF"][PIVOT TABLE FUNCTION OR CODE HERE]
[/color]

   [color="#008000"]' *** EXPORT CELL VALUE TO TEXT FILE ***[/color]
   Dim myFile As String, cellValue As Variant
   
   myFile = "[color="#0000FF"]C:\temp\edugeek.txt[/color]"
   cellValue = Range("[color="#0000FF"]A1[/color]").Value
   
   Open myFile For Output As #1
   Print #1, cellValue
   Close #1

   ActiveWorkbook.Save
       
[color="#008000"]    '******
   '* comment out Application.Quit line until you are happy the code works are intended. 
   '* once saved this spreadsheet will always close when opened
   '* ~WORKAROUND~ open Excel. File -> Open, browse to file 
   '* the hold down SHIFT key and open file.  Hold shift until file open.
   '******[/color]
   Application.Quit 
   
End Sub

Edited November 26, 2021 by garbage46