3305 Main Street Ste 019 Vancouver, WA 98663

How to Automate the Office Connector Import Process via TSIWriteImportFiles2

It may be desirable to automate the process of creating an import file for Sage 300 CRE via an Office Connector Import workbook. This task can be completed with a macro built into Office Connector Import.

This macro can be called via a custom macro using the Microsoft Visual Basic for Applications editor within Excel.

Microsoft Visual Basic for Applications

If you've never used this aspect of Excel, the application window can be displayed by pressing Alt-F11 after you have opened the Excel workbook of your choice.

Macro Name

TSIWriteImportFiles2

Arguments

  • FileFormat: This would be the desired Sage Import File Type. Available options include:
    • APInvoice
    • ARInvoice
    • CMCheck
    • EQEntry
    • GLBudget
    • GLTransaction
    • JCCommittment
    • JCDirecCost
    • JCEstimate
    • JCMWTran
    • JCReceivables
    • PMCharge
    • PMPayment
    • PRTime
  • Filename: This is the filename of the import file which will be created and should reference the entire path to the file.
  • ReplaceExisting: True=Replace | False=Append
  • ImportAction: Available options include:
    • 0 = No Action
    • 1 = Launch related Sage 300 CRE Application
    • 2 = Run Sage 300 CRE Macro
  • MacroFileName: This is the full path to a Sage 300 CRE macro. Only needed if you specify ImportAction=2.
  • OperatorID: Sage username.
  • Password: Sage password. Please note, TSIWriteImportFiles2 is expecting an encrypted version of your Sage password. To obtain the encrypted form, please record a macro where you select Save Import File from the Office Connector Import toolbar and successfully authenticate to the desired Sage 300 CRE Data Folder. Review the recorded macro in the VBA editor and you will be able to copy the password argument from this macro to your TSIWriteImportFile2 argument.
  • SuppressUI: True=No user intervention necessary | False=User will have to select prompts to complete process.

Example Syntax

Call Application.Run("TSIWriteImportFiles2", "APInvoice", "\\E1SQL\Timberline Office\Data\Construction Sample Data\APInvoice.TXT", True, 1, "", "e1", "F3F2", False)