Guidance Please : Programatically Automate Excel Files from Access

  • Thread starter Thread starter Dimbat
  • Start date Start date
D

Dimbat

I hope some kind soul could help me with this :

I need to process, daily, a bunch of excel files by running an excel
macro. I would like to automate this procedure from within Access (97)to
tie up a complete solution.

I have managed to retreive a listing of the files and save them to a
table by following advice from some of the previous postings.

Now I am stuck for ideas on what to do next.


Thank you in advance
 
You need to provide more info about what "processing the files" means.

Overall, what you'd do is open a recordset in VBA code that is based on the
table that contains the file names, and then loop through that recordset to
get each filename and do the "process" to the data in that file, and then
continue until you've handled all the files.

If the files are all in one folder, there is no need to store the filenames
in a table; you could use the Dir function to get the names at the time you
do the processing.

Please post more info about what you seek to do.
 
You need to provide more info about what "processing the files" means.

Overall, what you'd do is open a recordset in VBA code that is based on the
table that contains the file names, and then loop through that recordset to
get each filename and do the "process" to the data in that file, and then
continue until you've handled all the files.

If the files are all in one folder, there is no need to store the filenames
in a table; you could use the Dir function to get the names at the time you
do the processing.

Please post more info about what you seek to do.
Click to expand...
Ken

Thanks for the quick response

The Excel files are price lists submitted by various branches - the excel
macro function, initiated by ctrl m, resizes a single spreadsheet and
prints it out.
My database contains names and addresses, product requirements and order
id's imported from other spreadsheets sent out by main distribution. It
then goes onto mailmerge & batch print letters and address labels, The
various price lists for the customer are then sent with their product.

When you detail this out like this, it seems lot like a "Casting the
Runes Process" but it's much quicker than having to having to do it
individually in Excel & Word.

I thought I could batch process the price list using the retrieved list
of the spreadsheet names and "Some Code!"

I then fell into a black hole.

Hope you can help
 
This additional info does not help me understand what actions the ACCESS
database needs to do, but let me assume that you already have code that does
what you want with a single file. What you then need is to surround that
code with a loop that goes through the table and uses each filename from
that table.

For example:

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strFile As String

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("TableName", , dbReadOnly)
If rst.BOF = False And rst.EOF = False Then
rst.MoveFirst
Do While rst.EOF = False
strFile = rst![FileNameField]
' insert your code here to use
' strFile wherever you need the filename
' .
' .
' .
' end of your code
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End If
 
Thanks for your help and apologies for being unclear as to what I am
trying to achieve.

I would like the access database to open Excel run a macro and then open
the files listed in my table PricesTmp and then process them using the
macro initiated from a shortcut ctrlA

I hope that is more of an explantion


Thanks again
 
Comments inline...

--
Ken Snell
<MS ACCESS MVP>

Dimbat said:
Thanks for your help and apologies for being unclear as to what I am
trying to achieve.

I would like the access database to open Excel
Click to expand...

This can be done by adding steps to the code that I'd posted. I'll post an
example at the end of this post.

run a macro
Click to expand...

Is the macro in ACCESS? or in EXCEL? What does this macro do?
and then open the files listed in my table PricesTmp and then process them using the
macro initiated from a shortcut ctrlA
Click to expand...

Is this "ctrlA" macro the same one that you'd earlier said was run by
"ctrlM"? In which file is this macro located? How will you open that file so
that its macro can be run?
I hope that is more of an explantion



Thanks again
Click to expand...


EXAMPLE CODE (not tested):

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strFile As String
Dim xlEXCEL As Object, xlWB_Macro As Object
Dim xlWB_File As Object

Set xlEXCEL = CreateObject("Excel.Application")
Set xlWB_Macro = xlEXCEL.Workbooks.Open "PathToMacroFile"

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("TableName", , dbReadOnly)
If rst.BOF = False And rst.EOF = False Then
rst.MoveFirst
Do While rst.EOF = False
strFile = rst![FileNameField]
' ..next code step assumes that strFile contains a full path to the file
Set xlWB_File = xlEXCEL.Workbooks.Open strFile
xlWB_Macro.Run "MacroName"
DoEvents
xlWB_File.Close False
Set xlWB_File = Nothing
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
xlWB_Macro.Close False
Set xlWB_Macro = Nothing
xlEXCEL.Quit
Set xlEXCEL = Nothing
End If
 
Comments inline...

--
Ken Snell
<MS ACCESS MVP>



This can be done by adding steps to the code that I'd posted. I'll post an
example at the end of this post.



Is the macro in ACCESS? or in EXCEL? What does this macro do?
Click to expand...

The Macro is an Excel one : It resides in the Excel start up folder
(XLStart)the shortcut key that currently opens and initiates the macro is
ctrlM. There is a sub routine initiated from ctrlA resizes the price
lists and prints it out on one page.
Is this "ctrlA" macro the same one that you'd earlier said was run by
"ctrlM"? In which file is this macro located? How will you open that file so
that its macro can be run?
Click to expand...
The Complete macro is resident in excel on start up initiated by
Access, send keys from AccesscntrlM then activate the macro. This allows
me to bypass the Excel enable macro command without comprising the
overall security by disabling the Macro Warniing.Thank you for putting the code together for me, It's good of you to take
the time to help me out.
Very much appreciated

I'll integrate into my DB and let you know how I get on.
 
Back
Top