link to excel with chart

[rickscr]

I have some code that loops through some selected Excel spreadsheets, links to them, and appends the data from sheet1 to my table. The problem is that sometimes the Excel files have a chart object that gets it's own sheet. When this happens, I get an error in Access saying "External table is not in the expected format."

How can I get around this?



Here's the code where I have the problem:

DoCmd.TransferSpreadsheet acLink, , "Temporary", path, True

[ajetrumpet]

so you don't want to do anything with those sheets that have charts on them, right? if that is so, could you maybe check the name of the sheet? I noticed that your first sheet in the picture is the default name that excel gives to one when you create a chart. do all of your chart sheets have names like that? if so, just check the sheet name in your loops and skip the ones that have charts.

[rickscr]

I don't care about the chart-sheet. I only want to import Sheet1.
Yes, all of the charts will be named Chart1.

How can I skip the sheet, but not the file?

I've tried this as well:

DoCmd.TransferSpreadsheet acLink, , "Temporary", path, True, "Sheet1!"

[ajetrumpet]

i've never used the LINK option, Rick. isn't there an arg in the transferspreadsheet method to specify a sheet name? you have that in your last post. I don't know why you're using bang (!) with it though. that needs to be taken out. It doesnt mean anything in that context.

[rickscr]

I've tried it with and without the "!" and the result is the same, though it apparently is required.
http://msdn.microsoft.com/en-us/libr...ffice.12).aspx
It seems like I have the syntax right, so I don't know why it's not working.

[ajetrumpet]

maybe the problem is the linking method?? how about an import method?? you can always use that, and get around it via one other step after that.

that's one idea.

I'm sorry I can't click on that link. it's broken, but that happens to me if it's required to go through an ad page first. I have the HOSTS file from mvps.org that blocks all internet ads, so clickthrough pages fail for me.

[rickscr]

The problem with the importing is that the column headers have special characters that Access doesn't like (degree signs). Using a link was the only way I found to get around it.

[rickscr]

Just to try it out, I changed the column headers and tried it with acImport, but with the same result.

[ajetrumpet]

i dont know what to tell you rick. and I do not have time to test it for you. sorry, sir.