Hello, this is my first post on this forum. I have a procedure that results in an "Error 462: The remote server machine does not exist or is unavailable" message. I know from the following microsoft support posting that it is an automation error by design that is the result of an unqualified object reference. I have searched my code but cannot identify the unqualified reference. I'm hoping someone with fresh eyes or more experience could help me to identify the unqualified reference in the following code.
Here is the link to the microsoft discussion:
Excel automation fails second time code runs
And here you can find the code that I am executing in Excel:
NOTE:
<!--colorc--><!--/colorc-->
SizeString is a function that allows me to pad strings to a specific number of characters, with any pad string that I provide, on either the right or left side of the string. This function is provided by Chip Pearson and can be found at the following website:
Fixed Length Strings In VBA
Function
WriteEDIToFile(strIn As String) As Boolean is a simple function that creates a text file of the concatenated string
Function CreateEDIDebug(wkbIN As Workbook, strFieldNameIn As String, strDataIn As String, strFormattedDataIn As String, intRecordCountIn As Integer) As Boolean is a function that creates an excel file and records the original string, the formatted string, and the string size where each row represents a single record in the recordset for catching formatting, padding, or justification errors. [It is much easier to see than trying to decipher the text file, which is simply rows of concatenated strings]
If I call the function in the immediate window without the optional "Y" to generate the debug file, the last function is not called, so I do not think the unqualified reference occurs in that function.
Any help that can be provided would be great, thanks!
Here is the link to the microsoft discussion:
Excel automation fails second time code runs
And here you can find the code that I am executing in Excel:
Code:
Function CreateEDI(strState As String, Optional strPrintDebug As String = "N") As Boolean
Dim connDB As ADODB.Connection
Dim adoMA_EDI As ADODB.Recordset, adoMA_Transmitter As ADODB.Recordset
Dim strRecord As String, strDB As String, strEDI As String, strFieldValue As String
Dim intFieldCounter As Integer, intArrayCounter As Integer, intRecordCount As Integer
Dim arrEDIFields() As Variant
On Error GoTo PROC_ERR
'Disable screen updating to speed process
Excel.Application.ScreenUpdating = False
'Assign the values to arrEDIFields()
ReDim arrEDIFields(1 To 4, 1 To 35)
'arrEDIFields(1,n) = Field Name
'arrEDIFields(2,n) = Number of characters
'arrEDIFields(3,n) = Justification of text(Left/Right)
'arrEDIFields(4,n) = Padding character
arrEDIFields(1, 1) = "RecordIdentifier"
arrEDIFields(2, 1) = 1
arrEDIFields(3, 1) = TextLeft
arrEDIFields(4, 1) = "0"
arrEDIFields(1, 2) = "SequenceNumber"
arrEDIFields(2, 2) = 6
arrEDIFields(3, 2) = TextRight
arrEDIFields(4, 2) = "0"
arrEDIFields(1, 3) = "PeriodEndDate"
arrEDIFields(2, 3) = 8
arrEDIFields(3, 3) = TextRight
arrEDIFields(4, 3) = "0"
arrEDIFields(1, 4) = "FEIN"
arrEDIFields(2, 4) = 9
arrEDIFields(3, 4) = TextRight
arrEDIFields(4, 4) = "0"
arrEDIFields(1, 5) = "FilingEntityCode"
arrEDIFields(2, 5) = 2
arrEDIFields(3, 5) = TextRight
arrEDIFields(4, 5) = "0"
arrEDIFields(1, 6) = "BusinessName"
arrEDIFields(2, 6) = 30
arrEDIFields(3, 6) = TextLeft
arrEDIFields(4, 6) = " "
arrEDIFields(1, 7) = "ReturnRecordFlag"
arrEDIFields(2, 7) = 1
arrEDIFields(3, 7) = TextLeft
arrEDIFields(4, 7) = " "
arrEDIFields(1, 8) = "Non-AlcoholReceipts"
arrEDIFields(2, 8) = 12
arrEDIFields(3, 8) = TextRight
arrEDIFields(4, 8) = "0"
arrEDIFields(1, 9) = "AlcoholReceipts"
arrEDIFields(2, 9) = 12
arrEDIFields(3, 9) = TextRight
arrEDIFields(4, 9) = "0"
arrEDIFields(1, 10) = "GrossReceipts"
arrEDIFields(2, 10) = 12
arrEDIFields(3, 10) = TextRight
arrEDIFields(4, 10) = "0"
arrEDIFields(1, 11) = "ExemptMealsTotal"
arrEDIFields(2, 11) = 12
arrEDIFields(3, 11) = TextRight
arrEDIFields(4, 11) = "0"
arrEDIFields(1, 12) = "TotalTaxableReceipts"
arrEDIFields(2, 12) = 12
arrEDIFields(3, 12) = TextRight
arrEDIFields(4, 12) = "0"
arrEDIFields(1, 13) = "StateTaxRate"
arrEDIFields(2, 13) = 6
arrEDIFields(3, 13) = TextRight
arrEDIFields(4, 13) = "0"
arrEDIFields(1, 14) = "StateTaxDue"
arrEDIFields(2, 14) = 12
arrEDIFields(3, 14) = TextRight
arrEDIFields(4, 14) = "0"
arrEDIFields(1, 15) = "LocalTaxRate"
arrEDIFields(2, 15) = 6
arrEDIFields(3, 15) = TextRight
arrEDIFields(4, 15) = "0"
arrEDIFields(1, 16) = "LocalTaxDue"
arrEDIFields(2, 16) = 12
arrEDIFields(3, 16) = TextRight
arrEDIFields(4, 16) = "0"
arrEDIFields(1, 17) = "TotalAmountDue"
arrEDIFields(2, 17) = 12
arrEDIFields(3, 17) = TextRight
arrEDIFields(4, 17) = "0"
arrEDIFields(1, 18) = "PaymentRecord"
arrEDIFields(2, 18) = 1
arrEDIFields(3, 18) = TextRight
arrEDIFields(4, 18) = " "
arrEDIFields(1, 19) = "RoutingTransitNumber"
arrEDIFields(2, 19) = 9
arrEDIFields(3, 19) = TextRight
arrEDIFields(4, 19) = " "
arrEDIFields(1, 20) = "BankName"
arrEDIFields(2, 20) = 30
arrEDIFields(3, 20) = TextLeft
arrEDIFields(4, 20) = " "
arrEDIFields(1, 21) = "BankAccountNumber"
arrEDIFields(2, 21) = 18
arrEDIFields(3, 21) = TextLeft
arrEDIFields(4, 21) = " "
arrEDIFields(1, 22) = "AccountType"
arrEDIFields(2, 22) = "2"
arrEDIFields(3, 22) = TextRight
arrEDIFields(4, 22) = " "
arrEDIFields(1, 23) = "PaymentAmount"
arrEDIFields(2, 23) = 12
arrEDIFields(3, 23) = TextRight
arrEDIFields(4, 23) = "0"
arrEDIFields(1, 24) = "SettlementDate"
arrEDIFields(2, 24) = 8
arrEDIFields(3, 24) = TextRight
arrEDIFields(4, 24) = " "
arrEDIFields(1, 25) = "Reserved"
arrEDIFields(2, 25) = 35
arrEDIFields(3, 25) = TextRight
arrEDIFields(4, 25) = " "
'Transmitter Record
arrEDIFields(1, 26) = "FileIdentifier"
arrEDIFields(2, 26) = 6
arrEDIFields(3, 26) = TextRight
arrEDIFields(4, 26) = " "
arrEDIFields(1, 27) = "TotalReturnCount"
arrEDIFields(2, 27) = 6
arrEDIFields(3, 27) = TextRight
arrEDIFields(4, 27) = "0"
arrEDIFields(1, 28) = "TransmitterFEIN"
arrEDIFields(2, 28) = 9
arrEDIFields(3, 28) = TextRight
arrEDIFields(4, 28) = "0"
arrEDIFields(1, 29) = "TransmitterName"
arrEDIFields(2, 29) = 30
arrEDIFields(3, 29) = TextLeft
arrEDIFields(4, 29) = " "
arrEDIFields(1, 30) = "TransmitterAddress"
arrEDIFields(2, 30) = 30
arrEDIFields(3, 30) = TextLeft
arrEDIFields(4, 30) = " "
arrEDIFields(1, 31) = "TransmitterCity"
arrEDIFields(2, 31) = 30
arrEDIFields(3, 31) = TextLeft
arrEDIFields(4, 31) = " "
arrEDIFields(1, 32) = "TransmitterState"
arrEDIFields(2, 32) = 2
arrEDIFields(3, 32) = TextRight
arrEDIFields(4, 32) = " "
arrEDIFields(1, 33) = "TransmitterZip"
arrEDIFields(2, 33) = 5
arrEDIFields(3, 33) = TextRight
arrEDIFields(4, 33) = " "
arrEDIFields(1, 34) = "TransmitterZipCodeExtension"
arrEDIFields(2, 34) = 5
arrEDIFields(3, 34) = TextRight
arrEDIFields(4, 34) = " "
arrEDIFields(1, 35) = "TransmitterReserved"
arrEDIFields(2, 35) = 157
arrEDIFields(3, 35) = TextRight
arrEDIFields(4, 35) = " "
'Initialize the record count
intRecordCount = 1
'If the caller asked to produce the debug file
If strPrintDebug = "Y" Then
'Instantiate the debug workbook
Dim wkbDebug As Excel.Workbook
Dim strMyFile As String
Set wkbDebug = Excel.Workbooks.Add
strMyFile = "C:\Users\" & GetUserName() & "\Desktop\EDI_Debug"
Else
'Do not instantiate the debug workbook
End If
'Instantiate the database and connection objects
Set connDB = New ADODB.Connection
Set adoMA_EDI = New ADODB.Recordset
Set adoMA_Transmitter = New ADODB.Recordset
'Set the path to the dbase.
strDB = "C:\Users\csturgis\Desktop\Projects\FoodAndBeverageReturns\Solutions\Access Solutions\Food&Beverage.accdb"
'Open the connection to the DBase.
With connDB
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Mode = adModeShareExclusive
.Open strDB
End With
'Open the recordset "MS_EDI"
With adoMA_EDI
.ActiveConnection = connDB
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Open Source:="MA_EDI"
End With
'Open the recordset "MA_EDI_Transmitter"
With adoMA_Transmitter
.ActiveConnection = connDB
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Open Source:="MA_EDI_Transmitter"
End With
'Move to the first record in the recordset
adoMA_EDI.MoveFirst
adoMA_Transmitter.MoveFirst
'Move through the transmitter recordset, and format each field as specified by the State or Jurisdiction. NOTE: The transmitter recordset should only contain one record
Do While Not adoMA_Transmitter.EOF
For intFieldCounter = 0 To adoMA_Transmitter.Fields.Count - 1
For intArrayCounter = 1 To UBound(arrEDIFields, 2)
Select Case adoMA_Transmitter.Fields.Item(intFieldCounter).Name
Case arrEDIFields(1, intArrayCounter)
'Remove characters ",", "/", ".", "-" : " " as a test
strFieldValue = VBA.CStr(Nz(adoMA_Transmitter.Fields.Item(intFieldCounter).Value, ""))
strFieldValue = VBA.Replace(strFieldValue, ",", "")
strFieldValue = VBA.Replace(strFieldValue, "/", "")
strFieldValue = VBA.Replace(strFieldValue, ".", "")
strFieldValue = VBA.Replace(strFieldValue, "-", "")
strFieldValue = VBA.Replace(strFieldValue, " ", "")
strRecord = strRecord & SizeString(strFieldValue, VBA.CLng(arrEDIFields(2, intArrayCounter)), arrEDIFields(3, intArrayCounter), VBA.CStr(arrEDIFields(4, intArrayCounter)))
'If caller set strPrintDebug = "Y", then call PrintEDIDebug file
If strPrintDebug = "Y" Then Call CreateEDIDebug(Excel.Workbooks(wkbDebug), adoMA_Transmitter.Fields.Item(intFieldCounter).Name, adoMA_Transmitter.Fields.Item(intFieldCounter).Value, SizeString(strFieldValue, VBA.CLng(arrEDIFields(2, intArrayCounter)), arrEDIFields(3, intArrayCounter), VBA.CStr(arrEDIFields(4, intArrayCounter))), intRecordCount)
GoTo NextField
End Select
'Stop
Next intArrayCounter
NextField:
'Stop
Next intFieldCounter
'Add a carriage return and a line feed to the end of the record
strRecord = strRecord & Chr(13) & Chr(10)
'Print this record to a text file
strEDI = strEDI & strRecord
'Clear the contents of strRecord
strRecord = ""
Debug.Print "Transmitter Record: " & intRecordCount
intRecordCount = intRecordCount + 1
adoMA_Transmitter.MoveNext
Loop
'Move through each record in the recordset, and format each field as specified by the State or Jurisdiction
Do While Not adoMA_EDI.EOF
For intFieldCounter = 0 To adoMA_EDI.Fields.Count - 1
For intArrayCounter = 1 To UBound(arrEDIFields, 2)
Select Case adoMA_EDI.Fields.Item(intFieldCounter).Name
Case arrEDIFields(1, intArrayCounter)
strFieldValue = VBA.CStr(Nz(adoMA_EDI.Fields.Item(intFieldCounter).Value, ""))
'Remove characters ",", "/", ".", "-" : " " as a test
Select Case (adoMA_EDI.Fields.Item(intFieldCounter).Name)
Case "StateTaxRate": 'Do not remove decimal
Case "LocalTaxRate": 'Do not remove decimal
Case Else: strFieldValue = VBA.Replace(strFieldValue, ".", "") 'Remove decimals
End Select
strFieldValue = VBA.Replace(strFieldValue, ",", "")
strFieldValue = VBA.Replace(strFieldValue, "/", "")
strFieldValue = VBA.Replace(strFieldValue, "-", "")
strFieldValue = VBA.Replace(strFieldValue, " ", "")
strRecord = strRecord & SizeString(strFieldValue, VBA.CLng(arrEDIFields(2, intArrayCounter)), arrEDIFields(3, intArrayCounter), VBA.CStr(arrEDIFields(4, intArrayCounter)))
'If caller set strPrintDebug = "Y", then call PrintEDIDebug file
If strPrintDebug = "Y" Then Call CreateEDIDebug(Excel.Workbooks(wkbDebug), adoMA_EDI.Fields.Item(intFieldCounter).Name, adoMA_EDI.Fields.Item(intFieldCounter).Value, SizeString(strFieldValue, VBA.CLng(arrEDIFields(2, intArrayCounter)), arrEDIFields(3, intArrayCounter), VBA.CStr(arrEDIFields(4, intArrayCounter))), intRecordCount)
GoTo NextField1
End Select
Next intArrayCounter
NextField1:
Next intFieldCounter
'Add a carriage return and a line feed to the end of the record
strRecord = strRecord & Chr(13) & Chr(10)
'Print this record to a text file
strEDI = strEDI & strRecord
'Clear the contents of strRecord
strRecord = ""
Debug.Print "Store Record: " & intRecordCount
intRecordCount = intRecordCount + 1
adoMA_EDI.MoveNext
Loop
'Write the entire string to a text file
Call WriteEDIToFile(strEDI)
PROC_EXIT:
'Enable screen updating to normal mode
Excel.Application.ScreenUpdating = True
'If call set strPrintDebug = "Y" then close and destroy the workbook object
If strPrintDebug = "Y" Then
Excel.Workbooks(wkbDebug).SaveAs Filename:=strMyFile
Excel.Workbooks(wkbDebug).Close SaveChanges:=True
Set Excel.Workbooks(wkbDebug) = Nothing
End If
'Close both the recordset and the dbase connection.
If Not adoMA_EDI Is Nothing Then
If Not adoMA_EDI.State = 0 Then
adoMA_EDI.Close
End If
End If
If Not connDB Is Nothing Then
If Not connDB.State = 0 Then
connDB.Close
End If
End If
'Destroy the recordset and dbase connection objects.
Set adoMA_EDI = Nothing
Set connDB = Nothing
On Error Resume Next
Access.Application.Visible = True
If Err.Number <> 462 Then
Access.Application.Quit
Err.Clear
On Error GoTo PROC_ERR
End If
If Err.Number = 0 Then
MsgBox Prompt:="The EDI has been created"
Else
MsgBox Prompt:="The EDI process has encountered an error and has ended."
End If
Exit Function
PROC_ERR:
MsgBox Prompt:="Error in CreateEDI! Error#: " & Err.Number & vbNewLine & Err.Description
'Return false due to the error
CreateEDI = False
Resume PROC_EXIT
End Function
NOTE:
<!--colorc--><!--/colorc-->
SizeString is a function that allows me to pad strings to a specific number of characters, with any pad string that I provide, on either the right or left side of the string. This function is provided by Chip Pearson and can be found at the following website:
Fixed Length Strings In VBA
Function
WriteEDIToFile(strIn As String) As Boolean is a simple function that creates a text file of the concatenated string
Function CreateEDIDebug(wkbIN As Workbook, strFieldNameIn As String, strDataIn As String, strFormattedDataIn As String, intRecordCountIn As Integer) As Boolean is a function that creates an excel file and records the original string, the formatted string, and the string size where each row represents a single record in the recordset for catching formatting, padding, or justification errors. [It is much easier to see than trying to decipher the text file, which is simply rows of concatenated strings]
If I call the function in the immediate window without the optional "Y" to generate the debug file, the last function is not called, so I do not think the unqualified reference occurs in that function.
Any help that can be provided would be great, thanks!