Import Macro not pulling in all values from Excel Column

[baxman]

I have an Excel spreadsheet with 10 columns. Column 9 has rows of data where some have a 6 digit number and some rows are blank. Out of the rows with data, some of the 6 digit numbers are stored as number, others are stored as text. The ones stored as text didn't get "recognized" on the import into Access as a table. Is there anything I can do so that it would have recognized all data, whether it was a number or text?

Thanks for your help, New to the forumn.... Thanks!

[alansidman]

Before you import the file to Access you could save it as a .csv file. Everything would be text then and you can determine how you want to import each column in Excel into your table fields. You can specify by field what the makeup of that field will be. You cannot have mixed fields in Access. All records in a particular field must have the same property. Text, number, date, etc.

Alan

[baxman]

So are you saying that whether the column in excel has the 6 digit value stored as text or a number, the saving it to a CSV first will ensure the values are picked up as long as I then import it into Access from the CSV instead of the XLS?

[alansidman]

When you import from a CSV you can set the property for each of the fields. Do a manual import and set the specifications in the advanced screen. Once you have set the specifications, save it and remember the name of the specification. Use the specification name in your VBA when you do a transferspreadsheet function. This will assure you that all fields are imported as you desire and not leave Access to guess.

Alan