Printable View
hi
i have been as to automate some excel gfiles that we get.
what i want to happen is read in the info from the excel files put it into tables in a database
and the create a form to view the info.
has anyone do anything like this or no a good tutorial on this
any help would be great
I'm actually working through a similar scenario right now.
However, you cannot automize importing an excel file. Import specs(automizing your imports) only work for txt or csv files.
So what you're going to have to do is control excel through vba. (this is how i am working around it, im sure there are plenty of other routes you could take) use vba to grab the excel file location. save that file as a txt or csv format, and then close excel through vba. then you will be able to do your usual import specs using a txt file.
I'm about to post a new thread on a similar topic. I have gotten to this point, but when the resulting txt file is imported, it is not carrying the same information that it was when it was an excel file. it is just a bunch of jibberish.
dont mean to barge in on your thread, but im thinking you could possibly have the same problem in a bit.
You didn't specify a database but with SQL Server you can import Excel files to the database a number of ways. Reference:
http://support.microsoft.com/kb/321686
Also Google "importing excel to sql server" for more links.
I'd take that approach if possible and then just create your form standalone.
The Jet OLEDB Provider has an Excel IISAM that can select Ranges within Worksheets and treat them as tables as well. No need for exporting or any VBA, however the Excel files have to be formatted in a predictable way.
Excel is terrible for data entry. People will add extra blank or junk columns and rows, name Worksheets at random, etc. Expect to write a ton of error handling logic.
This is really what InfoPath is for. But that's been with us since Office 2003 and basically nobody ever uses it.
what database are you importing into?
are the gfiles always the same?
here to help (importer of such for a long time)
If you are working with Jet ("MS Access") databases, then this might have everything you need:
http://www.vbforums.com/showthread.php?t=672137