Blank cells on import

[Ziggy1]

Hi,

I'm having a real hard time with this, I have a spreadsheet set up to IMPORT at the click of a button. The sheet is updated daily via a Macro, the macro also formats the data to text. I thought that would fix my problem, but it doesn't seem to matter.

Whenever a cell in Excel alphanumeric, the cell is blank following the import. What do I have to do to get it to accept? What makes it more frustrating is that sometimes it works and sometimes it doesn't.

So I don't know if the position of the alpha causes it to be rejected, most of the data is numeric.

Sample

These are the last few records sorted Ascending, all the cells preceded by a Alpha do not import, if I change the sort to Descending they will import but it is possible for me to have some that end with a Alpha, I don't know how they will respond?

830635
830777
903306
6841101
681951
780376
C37761
C37773
C37792
C37803
C37805
C37814
C37841
C37896
C37903


MY Excel Code for sorting and saving from disk as I am copying from one network to another. Some is done with the recorder, I modified the rest for the variable file name

Sub RecLogSave()

Dim datenow As String
Dim savename As String
'
datenow = Format(Now(), "m-d-yyyy")


savename = "reclog" & (datenow) & ".xls"



ChDir "A:\"
Workbooks.Open Filename:="A:\RECLOG.xls"
ChDir "\\MyDoc\fw6\3PR&3SRTrailers"
Columns("A:F").Select

Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

'what does this line do?
Selection.NumberFormat = "@"

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:= _
"\\MyDoc\fw6\3PR&3SRTrailers\reclog.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

ActiveWorkbook.SaveAs Filename:= _
"\\MyDoc\fw6\3PR&3SRTrailers\" & savename, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False


ActiveWorkbook.Close
Application.DisplayAlerts = True

End Sub



Thanks

Ziggy

 

[ghudson]

You must format that field [column] in Excel as TEXT since some of the values contain alpha characters. You must ensure that the table you are importing the data into is also formatted as TEXT. You can not have a mixture of data formats within a field [column] or else you will have problems when Access try's to import your data.

This piece of code in Excel will select column A and format all rows in Column A as TEXT...

Columns("A:A").Select
Selection.NumberFormat = "@"

 

[Ziggy1]

You must format that field [column] in Excel as TEXT since some of the values contain alpha characters. You must ensure that the table you are importing the data into is also formatted as TEXT. You can not have a mixture of data formats within a field [column] or else you will have problems when Access try's to import your data.

This piece of code in Excel will select column A and format all rows in Column A as TEXT...

Columns("A:A").Select
Selection.NumberFormat = "@"

Thanks Ghudson,

I've done all that, but like I said it didn't do it when I sorted the list in Descending order.

I'll double check this part

Columns("A:A").Select
Selection.NumberFormat = "@"


I did it for all the columns at once, it just seems like a bug more than anything?

Previously in another database, I had to link my spreadsheets to get around this problem.

I was hoping perhaps some others have had this happen otherwise it must be something I'm doing wrong?

 

[ghudson]

Try saving your Excel file as a TEXT file and then import the text file into your db.

 

[Pat Hartman]

Access uses the first n rows to determine the datatype of each column of the spreadsheet and frustratingly will not allow you to change what it has determined. If your first n rows are integers, that is the datatype that will be assigned. The only way to alter this is to add a dummy row at the top of the spreadsheet where the contents reflect the datatype you want assigned.

The alternative is to create the table in Access with the correct datatypes and then append rows from the spreadsheet rather than doing a make-table.

 

[Ziggy1]

Thanks, G/Pat


That would explain why it works when I resort in Descending order, I will try both your suggestions to see which works best.

thanks

Ziggy