I have a queries built on a linked Excel file. The Excel file has a vlookup field that is forced to "" if there is an error. In the linked files, in Access, these numbers show up as #NUM and I believe are creating me grief when trying to export the queries back to Excel. I get an error 3349: "You cannot record your changes because a value you entered violates the settings difined for this table or list. Correct the errror and try again."

Has anyone ran into this and is there a workaround for it? Possibly I could build criteria into the queries GLCode field but I don't know what to use for this.

Any help would be appreciated. The following is my export code for the above mentioned queries in case I have created the problem in the code.
I have commented out the error routine so I could determine which line of code is dying and it is the .Range("B" & i + 1).Value = rs.Fields("GLCode").Value where the halt is highlighted. That is the field in the query that has the #NUM error.


Function ExportToExcel()
' Direct procedure flow.
'On Error GoTo Error

Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
Set xlWB = objXL.Workbooks.Open("C:\Amex\CurrentChargesBisconer.xls")
Set xlWS = xlWB.Worksheets("CurrentCharges")

Dim db As Database
Dim rs As Recordset
Dim i As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("CurrentCharges11152", , dbOpenDynamic)
rs.MoveFirst
i = 1
Do Until rs.EOF
With xlWS
' assign records to specific cells
.Range("A" & i + 1).Value = rs.Fields("GLExpense").Value
.Range("B" & i + 1).Value = rs.Fields("GLCode").Value
.Range("C" & i + 1).Value = rs.Fields("LOC").Value
.Range("D" & i + 1).Value = rs.Fields("Customer").Value
.Range("E" & i + 1).Value = rs.Fields("LastName").Value
.Range("F" & i + 1).Value = rs.Fields("FirstName").Value
.Range("G" & i + 1).Value = rs.Fields("CardNo").Value
.Range("H" & i + 1).Value = rs.Fields("Date").Value
.Range("I" & i + 1).Value = rs.Fields("BusinessType").Value
.Range("J" & i + 1).Value = rs.Fields("Commodity").Value
.Range("K" & i + 1).Value = rs.Fields("SupplierName").Value
.Range("L" & i + 1).Value = rs.Fields("Amount").Value
.Range("M" & i + 1).Value = rs.Fields("Department").Value



End With
i = i + 1
rs.MoveNext
Loop
rs.Close

Set xlWB = objXL.Workbooks.Open("C:\Amex\CurrentChargesYoung.xls")
Set xlWS = xlWB.Worksheets("CurrentCharges")

Set db = CurrentDb
Set rs = db.OpenRecordset("CurrentCharges11012", , dbOpenDynamic)
rs.MoveFirst
i = 1
Do Until rs.EOF
With xlWS
' assign records to specific cells
.Range("A" & i + 1).Value = rs.Fields("GLExpense").Value
.Range("B" & i + 1).Value = rs.Fields("GLCode").Value
.Range("C" & i + 1).Value = rs.Fields("LOC").Value
.Range("D" & i + 1).Value = rs.Fields("Customer").Value
.Range("E" & i + 1).Value = rs.Fields("LastName").Value
.Range("F" & i + 1).Value = rs.Fields("FirstName").Value
.Range("G" & i + 1).Value = rs.Fields("CardNo").Value
.Range("H" & i + 1).Value = rs.Fields("Date").Value
.Range("I" & i + 1).Value = rs.Fields("BusinessType").Value
.Range("J" & i + 1).Value = rs.Fields("Commodity").Value
.Range("K" & i + 1).Value = rs.Fields("SupplierName").Value
.Range("L" & i + 1).Value = rs.Fields("Amount").Value
.Range("M" & i + 1).Value = rs.Fields("Department").Value


End With
i = i + 1
rs.MoveNext
Loop
rs.Close

Set xlWB = objXL.Workbooks.Open("C:\Amex\CurrentChargesMurphy.xls")
Set xlWS = xlWB.Worksheets("CurrentCharges")

Set db = CurrentDb
Set rs = db.OpenRecordset("CurrentCharges11020", , dbOpenDynamic)
rs.MoveFirst
i = 1
Do Until rs.EOF
With xlWS
' assign records to specific cells
.Range("A" & i + 1).Value = rs.Fields("GLExpense").Value
.Range("B" & i + 1).Value = rs.Fields("GLCode").Value
.Range("C" & i + 1).Value = rs.Fields("LOC").Value
.Range("D" & i + 1).Value = rs.Fields("Customer").Value
.Range("E" & i + 1).Value = rs.Fields("LastName").Value
.Range("F" & i + 1).Value = rs.Fields("FirstName").Value
.Range("G" & i + 1).Value = rs.Fields("CardNo").Value
.Range("H" & i + 1).Value = rs.Fields("Date").Value
.Range("I" & i + 1).Value = rs.Fields("BusinessType").Value
.Range("J" & i + 1).Value = rs.Fields("Commodity").Value
.Range("K" & i + 1).Value = rs.Fields("SupplierName").Value
.Range("L" & i + 1).Value = rs.Fields("Amount").Value
.Range("M" & i + 1).Value = rs.Fields("Department").Value


End With
i = i + 1
rs.MoveNext
Loop
rs.Close

Set xlWB = objXL.Workbooks.Open("C:\Amex\CurrentChargesHyler.xls")
Set xlWS = xlWB.Worksheets("CurrentCharges")

Set db = CurrentDb
Set rs = db.OpenRecordset("CurrentCharges11038", , dbOpenDynamic)
rs.MoveFirst
i = 1
Do Until rs.EOF
With xlWS
' assign records to specific cells
.Range("A" & i + 1).Value = rs.Fields("GLExpense").Value
.Range("B" & i + 1).Value = rs.Fields("GLCode").Value
.Range("C" & i + 1).Value = rs.Fields("LOC").Value
.Range("D" & i + 1).Value = rs.Fields("Customer").Value
.Range("E" & i + 1).Value = rs.Fields("LastName").Value
.Range("F" & i + 1).Value = rs.Fields("FirstName").Value
.Range("G" & i + 1).Value = rs.Fields("CardNo").Value
.Range("H" & i + 1).Value = rs.Fields("Date").Value
.Range("I" & i + 1).Value = rs.Fields("BusinessType").Value
.Range("J" & i + 1).Value = rs.Fields("Commodity").Value
.Range("K" & i + 1).Value = rs.Fields("SupplierName").Value
.Range("L" & i + 1).Value = rs.Fields("Amount").Value
.Range("M" & i + 1).Value = rs.Fields("Department").Value


End With
i = i + 1
rs.MoveNext
Loop
rs.Close
Set xlWB = objXL.Workbooks.Open("C:\Amex\CurrentChargesDonati.xls")
Set xlWS = xlWB.Worksheets("CurrentCharges")

Set db = CurrentDb
Set rs = db.OpenRecordset("CurrentCharges11046", , dbOpenDynamic)
rs.MoveFirst
i = 1
Do Until rs.EOF
With xlWS
' assign records to specific cells
.Range("A" & i + 1).Value = rs.Fields("GLExpense").Value
.Range("B" & i + 1).Value = rs.Fields("GLCode").Value
.Range("C" & i + 1).Value = rs.Fields("LOC").Value
.Range("D" & i + 1).Value = rs.Fields("Customer").Value
.Range("E" & i + 1).Value = rs.Fields("LastName").Value
.Range("F" & i + 1).Value = rs.Fields("FirstName").Value
.Range("G" & i + 1).Value = rs.Fields("CardNo").Value
.Range("H" & i + 1).Value = rs.Fields("Date").Value
.Range("I" & i + 1).Value = rs.Fields("BusinessType").Value
.Range("J" & i + 1).Value = rs.Fields("Commodity").Value
.Range("K" & i + 1).Value = rs.Fields("SupplierName").Value
.Range("L" & i + 1).Value = rs.Fields("Amount").Value
.Range("M" & i + 1).Value = rs.Fields("Department").Value


End With
i = i + 1
rs.MoveNext
Loop
rs.Close

Set xlWB = objXL.Workbooks.Open("C:\Amex\CurrentChargesWatkins.xls")
Set xlWS = xlWB.Worksheets("CurrentCharges")

Set db = CurrentDb
Set rs = db.OpenRecordset("CurrentCharges11079", , dbOpenDynamic)
rs.MoveFirst
i = 1
Do Until rs.EOF
With xlWS
' assign records to specific cells
.Range("A" & i + 1).Value = rs.Fields("GLExpense").Value
.Range("B" & i + 1).Value = rs.Fields("GLCode").Value
.Range("C" & i + 1).Value = rs.Fields("LOC").Value
.Range("D" & i + 1).Value = rs.Fields("Customer").Value
.Range("E" & i + 1).Value = rs.Fields("LastName").Value
.Range("F" & i + 1).Value = rs.Fields("FirstName").Value
.Range("G" & i + 1).Value = rs.Fields("Cardo").Value
.Range("H" & i + 1).Value = rs.Fields("Date").Value
.Range("I" & i + 1).Value = rs.Fields("BusinessType").Value
.Range("J" & i + 1).Value = rs.Fields("Commodity").Value
.Range("K" & i + 1).Value = rs.Fields("SupplierName").Value
.Range("L" & i + 1).Value = rs.Fields("Amount").Value
.Range("M" & i + 1).Value = rs.Fields("Department").Value


End With
i = i + 1
rs.MoveNext
Loop
rs.Close
Set xlWB = objXL.Workbooks.Open("C:\Amex\CurrentChargesOboyle.xls")
Set xlWS = xlWB.Worksheets("CurrentCharges")

Set db = CurrentDb
Set rs = db.OpenRecordset("CurrentCharges11095", , dbOpenDynamic)
rs.MoveFirst
i = 1
Do Until rs.EOF
With xlWS
' assign records to specific cells
.Range("A" & i + 1).Value = rs.Fields("GLExpense").Value
.Range("B" & i + 1).Value = rs.Fields("GLCode").Value
.Range("C" & i + 1).Value = rs.Fields("LOC").Value
.Range("D" & i + 1).Value = rs.Fields("Customer").Value
.Range("E" & i + 1).Value = rs.Fields("LastName").Value
.Range("F" & i + 1).Value = rs.Fields("FirstName").Value
.Range("G" & i + 1).Value = rs.Fields("CardNo").Value
.Range("H" & i + 1).Value = rs.Fields("Date").Value
.Range("I" & i + 1).Value = rs.Fields("BusinessType").Value
.Range("J" & i + 1).Value = rs.Fields("Commodity").Value
.Range("K" & i + 1).Value = rs.Fields("SupplierName").Value
.Range("L" & i + 1).Value = rs.Fields("Amount").Value
.Range("M" & i + 1).Value = rs.Fields("Department").Value


End With
i = i + 1
rs.MoveNext
Loop
rs.Close
Set xlWB = objXL.Workbooks.Open("C:\Amex\CurrentChargesJohnson.xls")
Set xlWS = xlWB.Worksheets("CurrentCharges")

Set db = CurrentDb
Set rs = db.OpenRecordset("CurrentCharges11103", , dbOpenDynamic)
rs.MoveFirst
i = 1
Do Until rs.EOF
With xlWS
' assign records to specific cells
.Range("A" & i + 1).Value = rs.Fields("GLExpense").Value
.Range("B" & i + 1).Value = rs.Fields("GLCode").Value
.Range("C" & i + 1).Value = rs.Fields("LOC").Value
.Range("D" & i + 1).Value = rs.Fields("Customer").Value
.Range("E" & i + 1).Value = rs.Fields("LastName").Value
.Range("F" & i + 1).Value = rs.Fields("FirstName").Value
.Range("G" & i + 1).Value = rs.Fields("CardNo").Value
.Range("H" & i + 1).Value = rs.Fields("Date").Value
.Range("I" & i + 1).Value = rs.Fields("BusinessType").Value
.Range("J" & i + 1).Value = rs.Fields("Commodity").Value
.Range("K" & i + 1).Value = rs.Fields("SupplierName").Value
.Range("L" & i + 1).Value = rs.Fields("Amount").Value
.Range("M" & i + 1).Value = rs.Fields("Department").Value


End With
i = i + 1
rs.MoveNext
Loop
rs.Close
Set xlWB = objXL.Workbooks.Open("C:\Amex\CurrentChargesBourgeois.xls")
Set xlWS = xlWB.Worksheets("CurrentCharges")

Set db = CurrentDb
Set rs = db.OpenRecordset("CurrentCharges11111", , dbOpenDynamic)
rs.MoveFirst
i = 1
Do Until rs.EOF
With xlWS
' assign records to specific cells
.Range("A" & i + 1).Value = rs.Fields("GLExpense").Value
.Range("B" & i + 1).Value = rs.Fields("GLCode").Value
.Range("C" & i + 1).Value = rs.Fields("LOC").Value
.Range("D" & i + 1).Value = rs.Fields("Customer").Value
.Range("E" & i + 1).Value = rs.Fields("LastName").Value
.Range("F" & i + 1).Value = rs.Fields("FirstName").Value
.Range("G" & i + 1).Value = rs.Fields("CardNo").Value
.Range("H" & i + 1).Value = rs.Fields("Date").Value
.Range("I" & i + 1).Value = rs.Fields("BusinessType").Value
.Range("J" & i + 1).Value = rs.Fields("Commodity").Value
.Range("K" & i + 1).Value = rs.Fields("SupplierName").Value
.Range("L" & i + 1).Value = rs.Fields("Amount").Value
.Range("M" & i + 1).Value = rs.Fields("Department").Value


End With
i = i + 1
rs.MoveNext
Loop
rs.Close
Set xlWB = objXL.Workbooks.Open("C:\Amex\CurrentChargesKowaleski.xls")
Set xlWS = xlWB.Worksheets("CurrentCharges")

Set db = CurrentDb
Set rs = db.OpenRecordset("CurrentCharges12002", , dbOpenDynamic)
rs.MoveFirst
i = 1
Do Until rs.EOF
With xlWS
' assign records to specific cells
.Range("A" & i + 1).Value = rs.Fields("GLExpense").Value
.Range("B" & i + 1).Value = rs.Fields("GLCode").Value
.Range("C" & i + 1).Value = rs.Fields("LOC").Value
.Range("D" & i + 1).Value = rs.Fields("Customer").Value
.Range("E" & i + 1).Value = rs.Fields("LastName").Value
.Range("F" & i + 1).Value = rs.Fields("FirstName").Value
.Range("G" & i + 1).Value = rs.Fields("CardNo").Value
.Range("H" & i + 1).Value = rs.Fields("Date").Value
.Range("I" & i + 1).Value = rs.Fields("BusinessType").Value
.Range("J" & i + 1).Value = rs.Fields("Commodity").Value
.Range("K" & i + 1).Value = rs.Fields("SupplierName").Value
.Range("L" & i + 1).Value = rs.Fields("Amount").Value
.Range("M" & i + 1).Value = rs.Fields("Department").Value


End With
i = i + 1
rs.MoveNext
Loop
rs.Close
Set xlWB = objXL.Workbooks.Open("C:\Amex\CurrentChargesBriare.xls")
Set xlWS = xlWB.Worksheets("CurrentCharges")

Set db = CurrentDb
Set rs = db.OpenRecordset("CurrentCharges12085", , dbOpenDynamic)
rs.MoveFirst
i = 1
Do Until rs.EOF
With xlWS
' assign records to specific cells
.Range("A" & i + 1).Value = rs.Fields("GLExpense").Value
.Range("B" & i + 1).Value = rs.Fields("GLCode").Value
.Range("C" & i + 1).Value = rs.Fields("LOC").Value
.Range("D" & i + 1).Value = rs.Fields("Customer").Value
.Range("E" & i + 1).Value = rs.Fields("LastName").Value
.Range("F" & i + 1).Value = rs.Fields("FirstName").Value
.Range("G" & i + 1).Value = rs.Fields("CardNo").Value
.Range("H" & i + 1).Value = rs.Fields("Date").Value
.Range("I" & i + 1).Value = rs.Fields("BusinessType").Value
.Range("J" & i + 1).Value = rs.Fields("Commodity").Value
.Range("K" & i + 1).Value = rs.Fields("SupplierName").Value
.Range("L" & i + 1).Value = rs.Fields("Amount").Value
.Range("M" & i + 1).Value = rs.Fields("Department").Value
.Range("J" & i + 1).Value = rs.Fields("Customer").Value


End With
i = i + 1
rs.MoveNext
Loop
rs.Close
Set xlWB = objXL.Workbooks.Open("C:\Amex\CurrentChargesLund.xls")
Set xlWS = xlWB.Worksheets("CurrentCharges")
Set db = CurrentDb
Set rs = db.OpenRecordset("CurrentCharges13067", , dbOpenDynamic)
rs.MoveFirst
i = 1
Do Until rs.EOF
With xlWS
' assign records to specific cells
.Range("A" & i + 1).Value = rs.Fields("GLExpense").Value
.Range("B" & i + 1).Value = rs.Fields("GLCode").Value
.Range("C" & i + 1).Value = rs.Fields("LOC").Value
.Range("D" & i + 1).Value = rs.Fields("Customer").Value
.Range("E" & i + 1).Value = rs.Fields("LastName").Value
.Range("F" & i + 1).Value = rs.Fields("FirstName").Value
.Range("G" & i + 1).Value = rs.Fields("CardNo").Value
.Range("H" & i + 1).Value = rs.Fields("Date").Value
.Range("I" & i + 1).Value = rs.Fields("BusinessType").Value
.Range("J" & i + 1).Value = rs.Fields("Commodity").Value
.Range("K" & i + 1).Value = rs.Fields("SupplierName").Value
.Range("L" & i + 1).Value = rs.Fields("Amount").Value
.Range("M" & i + 1).Value = rs.Fields("Department").Value
.Range("J" & i + 1).Value = rs.Fields("Customer").Value


End With
i = i + 1
rs.MoveNext
Loop
rs.Close
db.Close

'Exit Function
'ErrorHandler:
' Display error information.
'MsgBox "Error number " & Err.Number & ": " & Err.Description
' Resume with statement following occurrence of error.
'Resume Next

End Function

The Excel file has a vlookup field that is forced to "" if there is an error.Two things that come to mind about this. 1) they are not real values in the fields because they are being looked up. And 2) A zero-length string is not a number (which I'm assuming the cells are formatted as numeric somehow?In the linked files, in Access, these numbers show up as #NUM and I believe are creating me grief when trying to export the queries back to Excel.I think this is saying that there is a mismatch, as I stated above (but I could be wrong!) I get an error 3349: "You cannot record your changes because a value you entered violates the settings difined for this table or list. Correct the errror and try again."Again here too, same thing. Remember, Access takes the first 3 values (or is it 5?) of the records in an imported or linked table and assigns the data type based on the findings...


One other question too: Do you get the #Num error in the actual Access table that is linked to the Excel table, or do you get it only when you have queried the Access table's data (that is in turn comming from Excel)?

thanks for looking at my problem.

The linked table property for that field is numeric Double. When opened the fields not already populated are #NUM. This is the same with the Queries. The fields are generated in Excel with a lookup to a table that forces unfound lookup to "".

This is the criteria for the field in Excel:
Sheets(1).Range("B2").Formula = "=IF(ISNA(VLOOKUP(A2,GlCode,2)),"""",VLOOKUP(A2,GlCode,2))" Where A2 is a text field and is associated in the table with a 4 digit numeric field. It comes about with code that prepopulates some common transactons with a General Ledger code.

During a test of the export process previous to adding this code to the XL workbook worked OK.