Docmd.TransferSpreadsheet TYPES

[ajetrumpet]

Is there a way to associate the spreadsheet types of this action with versions of office? The following types are what I have in my dropdown:


acSpreadsheetTypeExcel3
acSpreadsheetTypeExcel4
acSpreadsheetTypeExcel5
acSpreadsheetTypeExcel7
acSpreadsheetTypeExcel8
acSpreadsheetTypeExcel9


I have tried all of these to import a downloaded Excel spreadsheet from the internet, and everyone of them throws the error: External table is not in the specified format. Help please anyone? Thanks!

 

[jal]

Is there a way to associate the spreadsheet types of this action with versions of office? The following types are what I have in my dropdown:


acSpreadsheetTypeExcel3
acSpreadsheetTypeExcel4
acSpreadsheetTypeExcel5
acSpreadsheetTypeExcel7
acSpreadsheetTypeExcel8
acSpreadsheetTypeExcel9


I have tried all of these to import a downloaded Excel spreadsheet from the internet, and everyone of them throws the error: External table is not in the specified format. Help please anyone? Thanks!

Have you made sure that the spreadsheet itself is properly formatted?
Checklist:
(1) No spaces in column names.
(2) No invalid column names (for instance reserved keywords)
(3) No weird chars in column names.
(4) Every column must have a column name.
(5) No null columns (To test this, open the sheet with your mouse and select all empty rows and columns, re-deleting them, then try the import again).


Also, paste some of your code so we can see it.

 

[jal]

By the way, I doubt you can use the TransferSpreadsheet method from Access 2003 for a 2007 sheet. You would have to probably use ADODB I'm guessing, to go that route.

 

[ajetrumpet]

Here are the comments based on what I've done:

Have you made sure that the spreadsheet itself is properly formatted?
Checklist:
(1) No spaces in column names. <---This is irrelevant. I've already transferred with spaces successfully.
(2) No invalid column names (for instance reserved keywords) <---This is also irrelevant, as I've done the same thing here as in #1.
(3) No weird chars in column names. <---does not matter for me, like in #1 & #2.
(4) Every column must have a column name. <---This sheet does NOT. Is that the real problem?

As a consqequence to not having field names, I guess I'll just have to write some code to open the stupid thing, put column names in with VBA, close it, and then do the transfer after that. Is this correct you think?

 

[ajetrumpet]

Jal,

Ever seen the attached message before with a download from the internet? The file is an XLS piece, but the message suggests not. I will have to save these things as TRUE xls files i guess to make it work...


I apologize for the view too, that's the way it came out. I have two monitors going right now...

 

[jal]

I'm not too sure about that dialog. And I apologize for my suggestions being inaccurate (I was taking my best guess).

The only other suggestion that comes to mind is adding a ref to the Excel object model (I take it you're doing this in Access?) and trying to load the file using that model, and then immediately re-save to another file. Maybe that will "fix" it somehow.

 

[ajetrumpet]

It is very interesting Jal. I do that, but not by code. I opened the files, saved them as excel 97-2003 formatted XLS files using the overwriting of the file with the same name. But, what was really weird, was that the default file TYPE in the SAVEAS dialog box was set to CSV. I have no idea, but that's not consistent enough to be done with code. It wasn't important though, but if you want another teaser, I can't seem to stop Excel from opening in the 2007 version when I use this code:[/code]dim exl as new excel.application
exl.visible = true[/code]I want 2003 to open, but alas, it doesn't ever happen!

 

[jal]

It is very interesting Jal. I do that, but not by code. I opened the files, saved them as excel 97-2003 formatted XLS files using the overwriting of the file with the same name. But, what was really weird, was that the default file TYPE in the SAVEAS dialog box was set to CSV. I have no idea, but that's not consistent enough to be done with code. It wasn't important though, but if you want another teaser, I can't seem to stop Excel from opening in the 2007 version when I use this code:[/code]dim exl as new excel.application
exl.visible = true[/code]I want 2003 to open, but alas, it doesn't ever happen!

Maybe you could try the Shell command specifying the path to the Excel 2003 EXE file, and then get a handle on the running instance. I don't know the details of the shell syntax but it starts like this:

Dim exe As String
exe = "C:\Program Files\Windows NT\Accessories\wordpad.exe"
Dim dblQuote As String
dblQuote = """"
exe = dblQuote & exe & dblQuote 'quotes are needed if there are any spaces in the path.
'use a space as a separator
Shell exe & " " & "C:\notes.txt"


And then to get a handle on the running instance, do something like this (at least this worked for Ms Word):

Dim App As Word.Application
' Set App = GetObject(, "Word.Application")
' If App Is Nothing Then Set App = CreateObject("Word.Application")