Sample Microsoft Excel / VBA Code
The code below reads rows
from an Excel spreadsheet as Audit File transactions, writes the records to a
CSV file and then calls Transpose to import the file. You can attach it to a
button or run it as a macro.
Transpose will start
without a user interface (i.e. invisibly), import the file and return a result
to the Excel procedure.
Click here to download the complete excel spreadsheet.
Option Explicit
Public Sub RunSageImport()
Dim myRecord As Range
Dim myField As Range
Dim iFileNumber As Integer
Dim Buffer As String
Dim Filename As String
Dim StartRow As Integer
Dim Jobstep As String
Dim lResult As Long
Dim strResult As String
Dim WShell As Object
Dim ProgramPath As String
Dim InputPath As String
Dim ImportLogPath As String
Dim Parameters As String
Dim ErrText As String
Const QUOTE As String = """"
On Error GoTo ErrorHandler
'*************************************
Jobstep = "Creating Scripting Object"
'*************************************
Set WShell = CreateObject("Wscript.Shell")
'*****************************
Jobstep = "Setting Start Row"
'*****************************
StartRow = 1 ' <- Set the Start row. If the first row contains headings set this
to 2 for example.
' Set the Path to the Transpose program, Transpose Input Folder and the
parameters to be used.
'********************************
Jobstep = "Setting Program Path"
'********************************
' The Program path should include the program name and is enclosed in Quotes.
' e.g. C:\Program Files\Transpose\Transpose.exe
ProgramPath = QUOTE & "C:\Program Files\Transpose\Transpose.exe" & QUOTE
'******************************
Jobstep = "Setting Input Path"
'******************************
' The InputPath MUST be set to the same folder that Transpose is configured to
use as it's
' Input Folder. e.g. C:\Program Files\Transpose\TransIn
InputPath = "C:\Program Files\Transpose\TransIn"
'*************************************
Jobstep = "Setting Import Parameters"
'*************************************
' Set Transpose Import Parameters
' /S = run silently. Omit if you want Transpose to run in the foreground.
' /F = the filename to be imported without the path
Parameters = " /S/F:" & "Auditfile.csv"
'***********************************
Jobstep = "Setting Import Log Path"
'***********************************
' If you want the import log to be displayed on Validation Error also set the
' ImportLogPath. This is always Import.txt in the Transpose Program Folder.
' e.g. C:\Program Files\Transpose\Import.txt
ImportLogPath = "C:\Program Files\Transpose\Import.txt"
'****************************
Jobstep = "Setting Filename"
'****************************
Filename = InputPath & "\Auditfile.csv" ' <- Sets the name of the file to be
created.
iFileNumber = FreeFile
'*******************************
Jobstep = "Opening Output File"
'*******************************
Open Filename For Output As #iFileNumber
'*********************************************
Jobstep = "Writing Excel Rows to Output File"
'*********************************************
For Each myRecord In Range("A" & StartRow & ":A" & Range("A" &
Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells(1),
Cells(.Row, 256).End(xlToLeft))
Buffer =
Buffer & "," & QUOTE & Replace(myField.Text, QUOTE, QUOTE & QUOTE) & QUOTE
Next myField
Print #iFileNumber, Mid(Buffer, 2)
Buffer = Empty
End With
Next myRecord
'*******************************
Jobstep = "Closing Output File"
'*******************************
Close #iFileNumber
If MsgBox("File " & Filename & " created OK, Click OK to Import the file or
Cancel to Quit", vbInformation + vbOKCancel) = vbCancel Then
GoTo ExitProcedure
End If
'*****************************************************
Jobstep = "Running Transpose via Windows Script Host"
'*****************************************************
lResult = 0
Application.Cursor = xlWait
' Using the Windows Script Host Run command allows us to wait for the program to
' finish and allows us to get the program result.
lResult = WShell.Run(ProgramPath & Parameters, 0, True)
Application.Cursor = xlNormal
'***************************
Jobstep = "Checking Result"
'***************************
Select Case lResult
Case 0
strResult = "No Records to Import"
Case Is > 0
strResult = "Records Imported /
Updated = " & lResult
Case -100
strResult = "Data Validation Error, check the Import Log (Import.txt)
in the Transpose Program Folder."
Case -203
strResult = "Sage Data Files are the
wrong Version"
Case Is < 0
strResult = "An error occurred
running Transpose, Err Number = " & _
lResult & ", please check the application log and the User Guide."
End Select
MsgBox strResult, vbInformation, "Import Completed"
If lResult = -100 And ImportLogPath <> "" Then
lResult = WShell.Run("Notepad.exe" & " " & ImportLogPath, 1,
False)
End If
ExitProcedure:
On Error Resume Next
Set WShell = Nothing
Exit Sub
ErrorHandler:
Application.Cursor = xlNormal
ErrText = "Error " & Err.Number & " at " & Jobstep & ", " &
Err.Description
MsgBox ErrText, vbInformation
Resume ExitProcedure
End Sub