Sample Microsoft Access / VBA Code
The code below reads a
database Table called "Import", writes the records to a CSV file and then calls
Transpose to import the file.
Transpose will start
without a user interface (i.e. invisibly), import the file and return a result
to the Access procedure.
Private Sub cmdRunTranspose_Click()
'*******************************************************************************
' Procedure reads a database table, extracts the records to a CSV File and then
' calls Transpose in Silent Mode to Import the file.
'*******************************************************************************
On Error GoTo Err_cmdRunTranspose_Click
Dim Jobstep As String
Dim ErrText As String
Dim ProgramPath As String
Dim Parameters As String
Dim ImportFileName As String
Dim iFileNumber As Integer
Dim lResult As Long
Dim Wshell As Object
Set Wshell = CreateObject("Wscript.Shell")
Me.txtStatus = ""
iFileNumber = FreeFile
ImportFileName = "C:\Program Files\Transpose\TransIn\" & "AuditImport.csv"
'*********************************************************
Jobstep = "Creating Import File '" & ImportFileName & "'"
'*********************************************************
Open ImportFileName For Output As iFileNumber
Jobstep = "Opening Database"
Set db = CurrentDb
Jobstep = "Opening Recordset"
Set rs = db.OpenRecordset("Import")
If Not rs.EOF Then
rs.MoveLast
rs.MoveFirst
MsgBox "There are " & rs.RecordCount & " Records",
vbInformation
Do While Not rs.EOF
Jobstep = "Writing Record to Output
File"
Write #iFileNumber,rs("TransType"), _
rs("AccountRef"), _
rs("NominalCode"), _
rs("DepartmentNumber"), _
CStr(rs("TransDate")), _
rs("TransRef"), _
rs("TransDetails"), _
rs("NetAmount"), _
rs("TaxCode"), _
rs("TaxAmount")
rs.MoveNext
Loop
Jobstep = "Closing Recordset and Database"
rs.Close
db.Close
End If
Jobstep = "Closing File"
Close #iFileNumber
'*****************************************************
Jobstep = "Running Transpose via Windows Script Host"
'*****************************************************
Me.txtStatus = "Importing Invoices to Sage"
lResult = 0
Screen.MousePointer = 11
' Using the Windows Script Host Run command allows us to wait for the program to
' finish and allows us to get the program result. The VB Shell command doesn't
wait and
' returns the TaskID rather than the program result.
'(Chr(34) is the Quote Character)
ProgramPath = Chr(34) & "C:\Program Files\Transpose\Transpose.exe" & Chr(34)
Parameters = " /S/F:AuditImport.csv"
lResult = Wshell.Run(ProgramPath & Parameters, 0, True)
Screen.MousePointer = 0
'***************************
Jobstep = "Checking Result"
'***************************
Select Case lResult
Case 0
Me.txtStatus = "No Records to Import"
Case Is > 0
Me.txtStatus = "Records Imported /
Updated = " & lResult
Case -100
Me.txtStatus = "Data Validation
Error, check the Import Log(import.txt)"
Case Is < 0
Me.txtStatus = "An error occurred
running Transpose, Err Number = " & _
lResult & ", please check the
application log”
End Select
Exit_cmdRunTranspose_Click:
On Error Resume Next
Set Wshell = Nothing
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
Exit Sub
Err_cmdRunTranspose_Click:
Screen.MousePointer = 0
ErrText = "Error " & Err.Number & " at " & Jobstep & ", " &
Err.Description
MsgBox ErrText, vbInformation, "Run Transpose"
Me.txtStatus = ErrText
Resume Exit_cmdRunTranspose_Click
Exit Sub
End Sub
Return to Transpose