using excel file as data source

[boring39]

I'm having problems working with some external data in my excel document. All of the raw data is located in some reports that are saved as html so I import that into my workbook then I have a table on another sheet that performs all the calculations needed.


My problem is that when using the web data, excel doesn't allow me to turn it into a table or extract just one column. So in order to have my calculation table automatically expand to match the number of records in the web data I have created a data source that uses the current excel file as the source and use a sql query to pull the data from the sheet that has the web data into my calculation sheet. This works fine one some of the computers but others have ODBC errors, some even only having errors intermittently.


My requirements are:

1. I can't use macros/VBA

2. Only use one excel file

3. The file needs to be able to be "plug and play" as the web data changes size


Is there another way to have two columns mirror each other without needed to resort to creating a loop with data sources? Or, does anyone have any experience troubleshooting ODBC errors when it comes to connecting to excel documents?


Thanks for any help...


mike

 

[SirJB7]

Hi, boring39!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords "odbc excel connection" and press Search button. You'd retrieve many links from this website, like the following one(s), maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


http://chandoo.org/forums/topic/how-do-i-consolidate-several-tables-in-one-long-table

http://chandoo.org/wp/2012/04/02/using-excel-as-your-database/


Regards!

 

[boring39]

Ok after some research (thanks for your pointers) I have some sucess but also another error.


I followed the directions here: http://chandoo.org/forums/topic/how-do-i-consolidate-several-tables-in-one-long-table#post-11733


While the file remains open everything works well. However, once I save and reopen the file I get this error "The Microsoft Access database engine cannot open or write to the file ". It is already opened exclusively y another user, or you need permission to view and write its data."


after I choose OK I'm presented with a dialog box that says "Please Enter Microsoft Access Database Engine OLE DB Initialization Information" and asks me to verify Data Source, Username, Password, Provider String, and Open Mode.


Any help on troubleshooting would be appreciated.