Excel macro in ED

[Chiara]

Hello,

I have to create an excel starting from different .csv files, where each .csv is required to be in a different sheet of the excel workbook. I tried to record the macro in excel (it's a connection between the file that I would like to do), but I can't find the right syntax in ED.

This is the code recorded from excel:

Code: Select all

Sheets.Add After:=ActiveSheet
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\Chiara\Documents\Enterprise Dynamics 9\WMS.csv" _
        , Destination:=Range("$A$1"))
        .CommandType = 0
        .Name = "WMS"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

I have to do it starting from ED because all files (.xlsx and .csv) are destroyed and created again at each simulation, as well as I can't do a macro saved in "personal" excel because there are many users, and it would be necessary to install the macro in each pc.

what can I do?

Thanks

Chiara

[HarryBunnik]

Hello Chiara,

To execute code in Excel from ED, you'll have to use ActiveX. To give an idea, the code below opens an Excel application, execute some code and closes again. This is not actually working code, but might give you an idea how to tackle your idea and which code to use and search for. However executing such a big chunk of VBA from ED might be a challenge...

Code: Select all

do(
  var([excelOLE], vbOle),
  var([strCode], vbString), 
  
  excelOLE := CreateOLE([Excel.application]),
  
  strCode := [.....],
  
  Ole(excelOLE, strCode),
  Ole(excelOLE, [ActiveWorkbook.Saved], True),
  Ole(excelOLE, [Application.Quit])
)

Hopefully this, in combination with the ED-help and the QuickStart guide to ActiveX, helps you.

Then, more out of curiosity, why can't you provide, together with the simulation model, a basic excel file containing the Macro in a module as well? You can save the adjusted excel file with the output under a different name for each user experiment, thereby maintaining the original version.

Executing VBA code which stands in a Excel VBA module, instead of running it from ED, will make your life a lot easier... Especially when debugging and trying to figure out if the error is in the 4D script, or the VBA code.

I hope this helps you a bit further,

Cheers,

Harry

[Chiara]

Hi Harry,

I forgot to specify that yes, I'm using OLE commands in order to open and close excel.
I would like to avoid providing the users an excel file togheter with the new library because of a simple reason: the probability that a user could delete for mistake the original file.
Anyway, I agree with you, saving the macro in an excel sheet is easier and I have done it right now.

Now the question is: how I can call the macro written in excel from ED?
I need that the procedure is called from ED, not the user.

Thank you very much,
Chiara

[HarryBunnik]

Ha Chiara,

Yes, that are users

Once you've started Excel and opened your file, you can run your macro using the following code:

Code: Select all

Do(
  var([excelOLE], vbOle),
  var([strExcelPath], vbString),  

  var([valParameter1], vbValue, 0), {Parameter 1 that is need in the Excel macro}
  var([strParameter2], vbString, [0]), {Parameter 2 that is need in the Excel macro}
  var([strParameter3], vbString, [0]), {Parameter 3 that is need in the Excel macro}  


  {Start up of Excel and file}
  excelOLE := CreateOLE([Excel.application]),
  strExcelPath := [....],
  Ole(excelOLE, [visible], [0]),
  Ole(excelOLE, Concat([WorkBooks.Open(], sbo, strExcelPath, sbc, [)])),  
  
  Ole(excelOLE, Concat([Application.Run([NameOfMacro],], sbo, String(valParameter1), sbc, [,],  sbo, strParameter2, sbc, [,], sbo, strParameter3, sbc, [)]))
  
  ....  

I'm concatenating the message to include some of the parameters that are results from the simulation, only to give you an idea what is possible.

Cheers,

Harry