VBA Import data type

[DHIGHTO]

Hey guys,



I'm importing an excel file using vba. I'm getting a type conversion failure due to one field setting set to number instead of text. I'm looking for a code to set the data type of one field to text instead of the default (number). Here is my code if that helps.

Public Function EXCELIMPORT()
Call DoCmd.TransferSpreadsheet(acImport, acSpreadsheetTypeExcel9, _
      "SHEET1", "C:\Users\198657\Documents\OUTLOOK ATTACHMENTS\AR DAILY\AR DAILY " _
       & Format(Date - 1, "m-d-YYYY") & ".xlsx", True)
MsgBox "IMPORT SUCCESSFUL"
End Function

Thanks!

[ranman256]

Sometimes I've got this message if the target table field name does not match all field name spelling.

Others, I had to FORCE the excel field to text. Before the import, a macro would open the workbook, scan down the column and put a single quote in front of the cell value, save, close, import.

[JoeM]

You are not going to be able to do that with "TransferSpreadsheet". Quite frankly, importing Excel files into Access often is an exercise in frustration because of this exact issue you are having. Access and Excel try to "talk" to each other, and Access tries to figure out the data type of each field, and doesn't let you designate it yourself.

One potential workaround involves understanding how Excel and Access communicate. I believe that Access looks at about the first 10 records when trying to determine the data type of each field. So if you have some way of ensuring that this problematic field has a record that has a text value in one of the first ten rows, it should then give it the Text data type you want.

Another workaround (the one I usually employ) it to export the Excel files to text files (tab-delimited or CSV), and then use TransferText, where you have the ability to specify the format of each field yourself.

[DHIGHTO]

Right now I'm trying adding the ' in front of the numbers by using the formula ="'"&""&Sheet2!A2 to add the ', but now the ' is staying in front of the number. I have to click enter in the formula in order to just have the number appear, and I'm not sure of a good vba code to do that for a whole column. I'm not sure if this is the best way to do this.

I tried saving the files as both CSV and text. The CSV file did not export and the values turn into Japanese literally. In addition, CSV has a limit of 65,000 rows. The text file creates a new problem where time in the format of h:m:ss doesn't import.

[DHIGHTO]

Well, this is my work around. I just put a ' on the first value for the field. This way Access registers that one of the first 25 records is a text so that all the values are imported as a text.

[JoeM]

The text file creates a new problem where time in the format of h:m:ss doesn't import.

If you change the format of the column in Excel before saving as a CSV, it will export to the CSV with that format.

The CSV file did not export and the values turn into Japanese literally

How exactly are you converting the Excel file to CSV?
Where is this data coming from originally?
Is it in some special font in Excel?

[DHIGHTO]

Here is my import screen for giggles




My data looks like this. Its nothing special. I'm just saving my excel file as .csv.

ARNUM	TFILE	ACT_DATE	TIME	CODE_1	CODE_2	CODE_3	EMPNUM
928	1235921	2/17/2015	7:35:08 AM	RV	ADM	PF	155523
928	1371769	2/17/2015	7:36:14 AM	RV	ADM	PF	155523
928	1238805	2/17/2015	7:40:04 AM	RV	ADM	PF	155523
928	1366430	2/17/2015	7:39:51 AM	RV	ADM	PF	155523
928	1319690	2/17/2015	7:38:44 AM	RV	ADM	PF	155523
928	1377719	2/17/2015	7:37:29 AM	RV	ADM	PF	155523
928	1365587	2/17/2015	7:49:27 AM	RV	ADM	PF	155523
928	1377123	2/17/2015	7:43:37 AM	RV	ADM	PF	155523

[JoeM]

I see you marked it as solved. Did you figure it out?
What was the issue?

[DHIGHTO]

My work around was to write code that marks the first record as text so that when I get files that start with 25 "0"s, Access stores everything as text instead of number. On the CSV matter, I don't know what the issue is. I don't care to investigate. Thanks for the help.