reading data from textfile into a excel-template.

[kingz2000]

Hi,

I have recently automated a report in Access reports. The result, however, is supposed to appear in excel in a certain template.

I know I can export a snapshot into excel, but this doesn't give me the data in the given data. It just isn't presentable. Weirdly, all the design work in the report just vanishes, and the excel export just has the data and column name.

So now my idea, tell me if there is a better one, is to export the data to be presented in the report as a textfile, and then to read the data, and insert apropriately into designated squares in my exceltemplate. I just need a bit of help with reading data from a textfile. Can someone just give me a few function names that will help in my research?? Or maybe there is generally a better way to do fill an excel template??
Thanks in advance!!

 

[KenPuls]

Hi Kingz,

Skip the text file. You can program this in a couple of ways.

I have an example of coding Excel to pull the results of an SQL query FROM and Access database to Excel here:

http://www.excelguru.ca/node/23

I've also done a few project where I've written functions to get the value I need (via ADO and the SQL query) to drop it right into the range I need. Likewise, pull over the results of the entire query, drop it on a results page in my Excel template (hidden) and given my template a pre-crafted master page that pulls the appropriate data by formula from the page of source data.

Alternately, you could program a routine in Access to push your source data into a separate page of your Excel tempalte, then use a master page to pull out what you need from it, OR even just send over a piece at a time to fill up the fields you need.

Obviousley, the key is determining the approach you want to go, first, but either way, the text file is a layer you can avoid.

Ken Puls, CMA

http://www.excelguru.ca