Moving text file from Access to Excel

[Guest]

Let me first say sorry for this lengthy help request. Because of it being a
bit complicated, I felt I should give as much detail as I can to make it
easier to understand my help request.
I have a bit of a complicated process and will do my best to simplify the
help I need. This process currently is mostly manually done and takes 4 – 6
hours to do. I am trying to automate the process for sanity of the person
that does this process. This is a monthly process. The end result will be the
data ending up on multiple predefined tabs that already have predefined
tables in an EXCEL spreadsheet. If I can make this work for one tab, the
other tabs for the most part will be just a repeating of the process.
I am starting by setting up a macro that will import 3 text files into 3
separate tables, strip off unneeded records and re-sort the tables into the
proper order for sending out to Excel. This part is working and the easy
part.
Now for the complicated portion. The data in the 3 Access tables is 4
records made up of multiple fields. Each table has basically the same fields
except table 3 has two extra fields that tables 1 and 2 do not. The two extra
fields on table 3 at this point are irrelevant.
On the Excel spreadsheet I have several tabs laid out somewhat the same
from tab to tab. Tab A (and subsequent tabs) has 3 tables laid out with 4
rows each for 4 different regions. The columns are a YTD Total (has formula
to total) and a column for each month, Jan to Dec.
I have a field on each of the three Access tables called Total (again each
table has matching fields of other names). I need to take the Total value for
each Region from each of the three Access tables and place them into the
corresponding table and Region in Excel under the current month. Each month I
have to move over one column for the next month. The month columns are laid
out as Jan (col C) through Dec (col N). My 4 rows for each of the three
tables are 8 – 11, 21 – 24, and 34 – 37. I have to do this same process with
the remaining field values from Access ending up on different tabs, but the
same type of process has to happen with these.
The big question now is HOW DO I DO THIS? How can I move each value from
the Access tables to their proper places within the table layouts on the
Excel spreadsheet tabs? It would be nice if it was just a straight dump from
Access to a blank tab, but not that lucky. Currently the person doing this,
copies one field at a time from the text files to the corresponding place on
each tab. There are 23 tabs in this Excel spreadsheet file.

Thanks in advance and
Sorry for all the verbiage

 

[Douglas J. Steele]

Sounds as though you need to use Excel Automation.

See whether my July, 2005 "Access Answers" column in Pinnacle Publication's
"Smart Access" is enough to get you going.

You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[Larry Daugherty]

Hi Kenny,

I suggest that you break the problem into bite sized chunks and solve
the pieces first in their respective platforms and then try putting
the solution together.

You seem to have the Access portion going pretty will. I suggest that
you simply create a text or CSV file as the first step.

Move over to Excel and attack all of the Excel issues from there.

Only after you have your Excel code perfected should you can try
moving it over to Access and get it to work from there. (Note that
the problems you posted in this Access newsgroup and, in fact, Excel
issues).

You'll end up writing more code that way and you'll end up throwing
some of it away. But, you'll solve your problems in a lot less time
than by trying to attack your Excel issues from Access in the
beginning.

HTH

 

[mantrid]

I also think that if your Access tables are more or less the same then you
would be best to combine them. Having an extra column withe a simple
numerical value to differentiate between the rows that came from different
tables.