Export Errors/Type Conversion Errors
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
"TEMP", "C:\Users\" & username & "\Desktop\" & iName, True
Hi,
I am exporting/appending a table via the above code.
The problem is, whenever I do the appending via manual way (by going through the Access Wizard) it appends fine.
However, whenever I try to append it via the above code, it gives 'Type Conversion' Error on a certain column (column H)
This column H is definted as 'Text' on the database
Also in the excel file, defined as text (and I tried to re-define it via the following vba code)
Columns("H:H").Select
Selection.NumberFormat = "@"
Selection.TextToColumns Destination:=Range("H1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 2), TrailingMinusNumbers:=True
^ Regardless of what I do, when I do the appending manually, it imports fine. When I do it through code, it gives a list of import errors on column H.
Any help is much appreciated!
Thanks!
"TEMP", "C:\Users\" & username & "\Desktop\" & iName, True
Hi,
I am exporting/appending a table via the above code.
The problem is, whenever I do the appending via manual way (by going through the Access Wizard) it appends fine.
However, whenever I try to append it via the above code, it gives 'Type Conversion' Error on a certain column (column H)
This column H is definted as 'Text' on the database
Also in the excel file, defined as text (and I tried to re-define it via the following vba code)
Columns("H:H").Select
Selection.NumberFormat = "@"
Selection.TextToColumns Destination:=Range("H1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 2), TrailingMinusNumbers:=True
^ Regardless of what I do, when I do the appending manually, it imports fine. When I do it through code, it gives a list of import errors on column H.
Any help is much appreciated!
Thanks!
A sample of the the Excel file would help greatly...
What's all the TextToColumns stuff for:
Selection.TextToColumns Destination:=Range("H1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 2), TrailingMinusNumbers:=True
This may be the culprit....
The format may be: "@", ...but Access relies on the "Datatype".
Your code above is setting it as: xlFixedWidth
Who know how Access will try to resolve this?
Also unknown is what Access thinks of: FieldInfo:=Array(0, 2)
?
In other words, if you just manually inserted the "required" text in the cells (the text that really gets inserted after all the "massaging"), ...does this work?
JeffCoachman
What's all the TextToColumns stuff for:
Selection.TextToColumns Destination:=Range("H1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 2), TrailingMinusNumbers:=True
This may be the culprit....
The format may be: "@", ...but Access relies on the "Datatype".
Your code above is setting it as: xlFixedWidth
Who know how Access will try to resolve this?
Also unknown is what Access thinks of: FieldInfo:=Array(0, 2)
?
In other words, if you just manually inserted the "required" text in the cells (the text that really gets inserted after all the "massaging"), ...does this work?
JeffCoachman
ASKER
I am lost in your last sentance.
Please see the attached sample file.
How I am doing it that, the text to column, etc is done on the excel macro.
Then the importing is done on the Access Macro.
A a unique thing is, this (import error) happens on only certain data (or files of data) which I try to import into Access.
Thanks for the help!
National-Matrics---ProBill-Data-.xlsx
Please see the attached sample file.
How I am doing it that, the text to column, etc is done on the excel macro.
Then the importing is done on the Access Macro.
A a unique thing is, this (import error) happens on only certain data (or files of data) which I try to import into Access.
Thanks for the help!
National-Matrics---ProBill-Data-.xlsx
1. My point was that in order to test this, I would have to have your same setup and try to see if I could replicate the issue.
2. My other question was, ...If you inserted the correct data "manually", would it work?
3. There is No data in column H in the sample you posted...?
Is this the way it is normally?..
What type of data is supposed to be in this field?
(perhaps that is the issue, ...That Access does not know if this is Null, or an empty String...)
2. My other question was, ...If you inserted the correct data "manually", would it work?
3. There is No data in column H in the sample you posted...?
Is this the way it is normally?..
What type of data is supposed to be in this field?
(perhaps that is the issue, ...That Access does not know if this is Null, or an empty String...)
ASKER
1. To do this I will have to show you the database. Which I can but I want to see if it can be done without it. THanks!
2. If I manually import it via the Access wizard, it works fine.
3. There is no data...however there is data here and there which is like
S212
R221
S232
The data type should be text. Same field is defined as text in Access.
How would I let access know it an empty string?
Even when there is nothing inside cells (some of course) the error shows. Do you require the database or got any advice for me?
Thanks!
2. If I manually import it via the Access wizard, it works fine.
3. There is no data...however there is data here and there which is like
S212
R221
S232
The data type should be text. Same field is defined as text in Access.
How would I let access know it an empty string?
Even when there is nothing inside cells (some of course) the error shows. Do you require the database or got any advice for me?
Thanks!
ASKER
Please see attached sample database.
SAMPLE.accdb
SAMPLE.accdb
Still confused...
The error table states an error on row: 32, 141, 156, ...etc
...yet there are no rows (records) in the table "probills"?
What I need is a sample of this *entire* system.
So that I can do exactly as you are doing and replicate the error.
The basic error is that Access does not understand what the data is in those rows, so it fails and throws the error.
But again, the PROBILLS table has no records, so I cant even see what the data is in the rows it should have imported (row 1-31, ...for example)
The error table states an error on row: 32, 141, 156, ...etc
...yet there are no rows (records) in the table "probills"?
What I need is a sample of this *entire* system.
So that I can do exactly as you are doing and replicate the error.
The basic error is that Access does not understand what the data is in those rows, so it fails and throws the error.
But again, the PROBILLS table has no records, so I cant even see what the data is in the rows it should have imported (row 1-31, ...for example)
ASKER
Please see the attached. This is the file that I am try to append to the table Probill
National-Matrics---ProBill-Data-.xlsx
National-Matrics---ProBill-Data-.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Nope. The table Probill in the database already got a column called "Sub" which corresponds to column H in Excel.
The "Sub" is already defined as text right? So I am appending it to that table
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
"Probill", "C:\Users\" & username & "\Desktop\" & iName, True
Isnt this already a 'staging' table you as you mentioned?
The "Sub" is already defined as text right? So I am appending it to that table
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
"Probill", "C:\Users\" & username & "\Desktop\" & iName, True
Isnt this already a 'staging' table you as you mentioned?
Again, I really need a way to simulate your entire process... to investigate further.
As I stated, if you loaded column H with dummy text in all the records, does it append?
As I stated, if you loaded column H with dummy text in all the records, does it append?
ASKER
All coding is inside the Access.
The Sub "ImportExcel (iName as String)" get triggered by a sub in Excel.
What I am realllllly doing here is this
Take the Excel file. Append to Access - TEMP table (hidden). (Lets say the Excel contains the latest data)
Then taken Unique values from Probill table (say there is data), and append them to the temp table.
Delete all contents in Probill table
Then copy Temp table to Probill Table
Clear Temp table
^ all above coding is in Access Database I posted
You may temporarly run the code by creating a sub and calling it with the excel file name (I know you already know this - just suggesting)
Thanks!
The Sub "ImportExcel (iName as String)" get triggered by a sub in Excel.
What I am realllllly doing here is this
Take the Excel file. Append to Access - TEMP table (hidden). (Lets say the Excel contains the latest data)
Then taken Unique values from Probill table (say there is data), and append them to the temp table.
Delete all contents in Probill table
Then copy Temp table to Probill Table
Clear Temp table
^ all above coding is in Access Database I posted
You may temporarly run the code by creating a sub and calling it with the excel file name (I know you already know this - just suggesting)
Thanks!
<Nope.>
Nope to what?
Pleas be specific.
Again, if you posted dummy text in the excel file does it Append?
Yes or No?
Your Access code may need some tweaking in the error handling, and the way you are running the SQL.
Try not to use "On Error Resume Next", ...try to prevent or trap the specific error.
(Only use "On Error Resume Next" if the sub is simple, which yours is not)
Also use code like this to run SQL:
Currentdb.execute "Your SQL", dbfailonerror
Nope to what?
Pleas be specific.
Again, if you posted dummy text in the excel file does it Append?
Yes or No?
Your Access code may need some tweaking in the error handling, and the way you are running the SQL.
Try not to use "On Error Resume Next", ...try to prevent or trap the specific error.
(Only use "On Error Resume Next" if the sub is simple, which yours is not)
Also use code like this to run SQL:
Currentdb.execute "Your SQL", dbfailonerror
I'll play around with your code tonight...
ASKER
After reading through your comment, I thought of doing this (since Access looks at top few line) I am assigning ' to those cells.
Never gave me an error!
For count = 2 To 15
If (Cells(count, 8).Value = "") Then
Cells(count, 8).Value = "'"
End If
Next count
Thanks for all the help!
Never gave me an error!
For count = 2 To 15
If (Cells(count, 8).Value = "") Then
Cells(count, 8).Value = "'"
End If
Next count
Thanks for all the help!
LOL!
Great.
But you could have accepted your own post as the the actual "solution"
;-)
Jeff
Great.
But you could have accepted your own post as the the actual "solution"
;-)
Jeff
ASKER
:)
Yea but still your comment initiated the idea. Thanks still!
Yea but still your comment initiated the idea. Thanks still!
;-)
ASKER
http://support.microsoft.com/kb/109376
^ That did not help. The problem is, my rows are already defined as text!