Import spreadsheet into table but.... (1 Viewer)

I have a client that gets data sent to them on a daily basis from four locations. The data is from a customer of there and the customer isn't very flexible. The data comes in the form of an Excel spreadsheet. The columns in the spreadsheets from the four locations is the same, however in the spreadsheets sent from two of the four locations, the column heading for one column is blank. I use the DoCmd.TransferSpreadsheet function and it works great for transferring the spreadsheet data into a table, from two of the locations. For the two locations that are sending spreadsheets with the one column that has a blank header, it fails with the System error: 2391 - Field F5 doesn't exist in the destination table. It fails because the blank column heading is trying to find a field in the table to match, but all of the fields in the table have field names, like they should.

Does anyone know how I can use something like an If-Then statement where if the spreadsheet column heading is blank place the data in the field called TypeX?

Thank you in advance.

Hi. What you could try is import the bad spreadsheet into a new table and then use an APPEND query to move the imported data into the real table.

Thank you db. I wanted to think about your suggestion before I replied. The issue I see with your suggestion is that I'm not sure how using vba you can import a spreadsheet into another table and then append that into the needed table. I looked at the DoCmd commands and the only thing I saw associated with a spreadsheet is what I used, the DoCmd.TransferSpreadsheet function. If the issue is taking the contents of a spreadsheet and one of the columns doesn't have a header to tie it to the appropriate fields in a table, I'm not sure how this helps.

Any other ideas, or am I overlooking something?

The least failure-prone way to do this might be to open the spreadsheet first as an Excel Application object and examine the offending column header. If it is blank, you can simply make it not blank. Then save & close the worksheet, close the app object, and do your import. Use this forum's search feature (thin ribbon near top of page; find SEARCH as 3rd from right) to find Excel Application Object topics. You should be able to find a pot-load of articles on that subject.

Thank you. I'll look tomorrow when the sun comes up.

Chuck

Hi. Let me see if I can elaborate. What I was saying was you can use TransferSpreadsheet to import the bad Excel file to a new or empty table rather than into the actual table you really want to use. If it works, then you should be able to append the newly imported data from the temp table into your actual table, especially if the name of the header is always going to be F5.

db, I was successful using your last suggestion. Thank you. I used an OnError line to delete the contents of the temporary table, transfer the spreadsheet into the temporary table that had a field that matched the column in the spreadsheet without a column name, (F5 in my case), appended the temp table to the real one and life is good.

Thank you again for clarifying what you meant. Thinking about your suggestions while watching TV, getting up in the middle of the night thinking about them, brought everything together.

Hi. Congratulations! Glad to hear you were able to get it to work. Good luck with your project.