Blank column causing errors on import

[brickballer]

Hello-



I have a short section of code to load roughly a dozen .csv files into a table, but when I run the code I get an error for 'field 15 not found in destination table'

I think what is happening is Excel somehow marks column 15 as used (having data) despite it being empty, so Access thinks there is data in there as well.

What is the easiest way to work around this? Can I limit the import to only the first 14 columns? Here is a sample of what I'm using...

Sub importJuly2010Data()
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "07-2010", "\\Server\Share\Folder\Filename.xls", True
... (repeated several times for different filenames)...
End Sub


If I just add an extra column to the table, format as text and label as "error suppression" this should work, but seems like a hack way of working around it. I'd rather learn the proper way.

[ajetrumpet]

is the column the LAST column in your excel info?? if it and access is importing it, there IS something there. it can look blank and still not be empty.

if it's in the middle of the data columns thats a different story. if at the end though, DELETE the columns. that should fix it. don't push the delete KEY, actually delete the columns off the workbook. it's the same effect as fresh limbs growing back on an alien when you shoot them off with a pistol. like the MIB guys. they're always brand new and don't carry any of the old baggage with them.

[brickballer]

Haha I love that movie! (Movies?)

That's what I've been doing, but it's a pain to open 14 files, highlight, right click, delete, save.

Compound that with the fact that it's 14 files for each month, and I'm looking at the last year worth of data so 14 files * 12 months = notEnoughCoffeeInTheWorld.

I can't decide if I love or hate Excel.

[ajetrumpet]

so you don't want to do it manually. WRITE A SCRIPT!! that's what excel users do. the macro recorder will show you how to do it, no??

use Allen Browne's recursive file code to loop the files in the dir. not a complex task. Or am I talking to an interface user??

If you're not, here's AB's page: http://allenbrowne.com/ser-59.html

and by the way, MIB 3 comes out later this year, if I'm not mistaken. Both guys are returning. YAY!! I believe Barry Sonnenfeld is directing again. Can't remember though. He's got a great mind. But then again, so does Tim Burton!

[brickballer]

I would love to put in script to load through the files automatically, but 1) I don't trust everyone else to keep non-data files out of that directory <rolls eyes> and 2) I'm trying to 'dumb down' the code as much as possible for my coworkers.

It looks like there is a way to specify only certain columns using acImport:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "09-2010", "\\server\share\folder\filename.xls", True, "Current Benefits!$A:$N"

however I'm gettin an error

3011 cannot find the object 'Current Benefits$$A:$N'

I've checked my code a dozen times and I specify the range as "Current Benefits!$A:$N" but the error code keeps seeing "Current Benefits$$A:$N"

Any ideas on why this would error out?

[ajetrumpet]

are you sure it's in the right argument slot?? are you also sure that you're writing the argument CORRECTLY??

look up TRANSFERSPREADSHEET in the vba help file and it'll tell you how to put that argument in. That's the range argument, right?? the help file should tell you things like if it belongs in quotes, how to reference an excel range, etc...

Just thinking while I'm typing here, my guess would be that if you NAMED the range that you want and enter that actual name in the arg instead of what you got, it would work. just guessin though.

you might also consider ditching the dollar signs. Access is not PHP. it doesn't like them! again, just guessin.

[brickballer]

Hooray it works now!

I was also able to limit to the first 14 columns (thanks ajetrumpet!) by ditching the $ (which was designed to be an absolute range reference not PHP so that shows my novice level of VBA bwahahahaha!)

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "09-2010", "\\server\share\folder\file.xls", True, "Current Benefits!A:N"

Works perfectly.

[ajetrumpet]

good for you. I don't think you got the PHP joke, but that's ok.

see ya round.