Excel open as ADODB.Connection
-
Hello Everyone, Can you please advice on why I cant connect to excel file without saving it on my machine. I tried using these to connection string but still got an error
External table is not in the expected format.conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";Extended Properties=Excel 12.0;"
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";Extended Properties=Excel 8.0;"
When I try to open and save as the spreadsheet I can successfully connect. The file is on FTP and will be downloaded by automation and get information. I cannot make it automatic if I need to open the excel file and save it. The terminal I will deploy the program dont have office installed so I need to use a different tool to get data. Any suggestion appreciated thanks in advance.
- Be who you are and say what you feel because those who mind don't matter and those who matter don't mind. - Most of the problem in your life are due to two reasons: you act without thinking, or think without acting.
-
Hello Everyone, Can you please advice on why I cant connect to excel file without saving it on my machine. I tried using these to connection string but still got an error
External table is not in the expected format.conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";Extended Properties=Excel 12.0;"
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";Extended Properties=Excel 8.0;"
When I try to open and save as the spreadsheet I can successfully connect. The file is on FTP and will be downloaded by automation and get information. I cannot make it automatic if I need to open the excel file and save it. The terminal I will deploy the program dont have office installed so I need to use a different tool to get data. Any suggestion appreciated thanks in advance.
- Be who you are and say what you feel because those who mind don't matter and those who matter don't mind. - Most of the problem in your life are due to two reasons: you act without thinking, or think without acting.
Because you cannot use it over an application protocol connection, like FTP or HTTP. That isn't supported and, frankly, is not a reliable connection. The file MUST be on a drive letter or UNC path. If the extension of the Excel file ends in .XLSX, you have to use the ACE engine. If it's .XLS you can use Jet.
A guide to posting questions on CodeProject
Click this: Asking questions is a skill. Seriously, do it.
Dave Kreskowiak -
Because you cannot use it over an application protocol connection, like FTP or HTTP. That isn't supported and, frankly, is not a reliable connection. The file MUST be on a drive letter or UNC path. If the extension of the Excel file ends in .XLSX, you have to use the ACE engine. If it's .XLS you can use Jet.
A guide to posting questions on CodeProject
Click this: Asking questions is a skill. Seriously, do it.
Dave KreskowiakI download the file first from FTP to local files then connect locally. I tried this
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";Extended Properties=Excel 12.0;"
but still got the error. When I open the file and saved locally then retry to use the above connection string, I can now connect.
- Be who you are and say what you feel because those who mind don't matter and those who matter don't mind. - Most of the problem in your life are due to two reasons: you act without thinking, or think without acting.
-
I download the file first from FTP to local files then connect locally. I tried this
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";Extended Properties=Excel 12.0;"
but still got the error. When I open the file and saved locally then retry to use the above connection string, I can now connect.
- Be who you are and say what you feel because those who mind don't matter and those who matter don't mind. - Most of the problem in your life are due to two reasons: you act without thinking, or think without acting.
I'm confused. You never mentioned anything about any error message in your OP nor this post. You also posted connection string making it appear as though you can't open a connection to the database. So, what's the problem and what's the error message?
A guide to posting questions on CodeProject
Click this: Asking questions is a skill. Seriously, do it.
Dave Kreskowiak -
I'm confused. You never mentioned anything about any error message in your OP nor this post. You also posted connection string making it appear as though you can't open a connection to the database. So, what's the problem and what's the error message?
A guide to posting questions on CodeProject
Click this: Asking questions is a skill. Seriously, do it.
Dave KreskowiakOk so I have a spreadsheet which has a .xlsx extension and downloaded this on a FTP site. As I search in the internet you need to use this connection string for .xlsx files using ADODB.
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";Extended Properties=Excel 12.0;"
But this gives error on
conn.Open, this is the errorExternal table is not in the expected format.. When I manually open the spreadsheet on MS Office Excel then save it, I can successfully connect to said spreadsheet. Note: On my machine I have office installed but on the machines where my program will be deployed don't so I need to use ADODB to read the contents.- Be who you are and say what you feel because those who mind don't matter and those who matter don't mind. - Most of the problem in your life are due to two reasons: you act without thinking, or think without acting.
-
Ok so I have a spreadsheet which has a .xlsx extension and downloaded this on a FTP site. As I search in the internet you need to use this connection string for .xlsx files using ADODB.
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";Extended Properties=Excel 12.0;"
But this gives error on
conn.Open, this is the errorExternal table is not in the expected format.. When I manually open the spreadsheet on MS Office Excel then save it, I can successfully connect to said spreadsheet. Note: On my machine I have office installed but on the machines where my program will be deployed don't so I need to use ADODB to read the contents.- Be who you are and say what you feel because those who mind don't matter and those who matter don't mind. - Most of the problem in your life are due to two reasons: you act without thinking, or think without acting.
hansoctantan wrote:
When I manually open the spreadsheet on MS Office Excel then save it, I can successfully connect to said spreadsheet.
Excel, the application, is very, very forgiving in terms of which data it accepts; but sounds like the original file may already be in an invalid format. In that case, ADODB will fail to read it, as it is not as forgiving as Excel. On the plusside, it is also not as big as the entire Excel-package. Run your file through a validator, or unzip it and take a look at the contents.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
-
hansoctantan wrote:
When I manually open the spreadsheet on MS Office Excel then save it, I can successfully connect to said spreadsheet.
Excel, the application, is very, very forgiving in terms of which data it accepts; but sounds like the original file may already be in an invalid format. In that case, ADODB will fail to read it, as it is not as forgiving as Excel. On the plusside, it is also not as big as the entire Excel-package. Run your file through a validator, or unzip it and take a look at the contents.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
Really appreciate your help but the excel file came from our client so I cannot put it in any site as a sample. What do you mean by run it on a validator?
- Be who you are and say what you feel because those who mind don't matter and those who matter don't mind. - Most of the problem in your life are due to two reasons: you act without thinking, or think without acting.
-
Really appreciate your help but the excel file came from our client so I cannot put it in any site as a sample. What do you mean by run it on a validator?
- Be who you are and say what you feel because those who mind don't matter and those who matter don't mind. - Most of the problem in your life are due to two reasons: you act without thinking, or think without acting.
To see if it is a valid zipped XML file. Excel even accepts CSV files with the wrong extension.
hansoctantan wrote:
What do you mean by run it on a validator?
Something like this[^]. If the file is not valid then the connection will not succeed, but it might still be "good enough" for Excel.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
-
Really appreciate your help but the excel file came from our client so I cannot put it in any site as a sample. What do you mean by run it on a validator?
- Be who you are and say what you feel because those who mind don't matter and those who matter don't mind. - Most of the problem in your life are due to two reasons: you act without thinking, or think without acting.
An Excel .XLSX file is really a .ZIP file with a bunch of XML files in it. Open it in WinZip or 7ZIP and take a look around. If it isn't a .ZIP file, it's not really a .XLSX file and cannot be opened by the ACE engine as your connection string has specified.
A guide to posting questions on CodeProject
Click this: Asking questions is a skill. Seriously, do it.
Dave Kreskowiak -
An Excel .XLSX file is really a .ZIP file with a bunch of XML files in it. Open it in WinZip or 7ZIP and take a look around. If it isn't a .ZIP file, it's not really a .XLSX file and cannot be opened by the ACE engine as your connection string has specified.
A guide to posting questions on CodeProject
Click this: Asking questions is a skill. Seriously, do it.
Dave KreskowiakWow didn't know this. Yeah I can open it on 7Zip.
- Be who you are and say what you feel because those who mind don't matter and those who matter don't mind. - Most of the problem in your life are due to two reasons: you act without thinking, or think without acting.