Running a module to open an excel file

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've created a macro in excel for use in opening a series of files and making
the calculations. Because of my poor planning I've realized that this would
function better in Access. What I would like to do is transfer the module to
Access and then have it open the Excel files I want and do the calculations
the same as before. First, how do I open an Excel file in a module in
Access? Second, once the excel file is open will the macro make the
calculations the same as before or will I need to make changes to accomodate
the nomenclature of Access? Thanks.
 
Hi Josh,

Opening an Excel file from Access is not too hard:

Dim objXL As Excel.Application, objWbk As Excel.Workbook
Set objXL = New Excel.Application
objXL.Visible = False 'if you want the work done in the background
'if not, omit this line, or explicitly set
it to true
Set objWbk = objXL.Workbooks.Open(CurrentProject.Path & _
"\testfile1.xls")

For each file you will need a separate Excel.Workbook object...i.e., if you
want them open simultaneously. If not, you can close each file and reopen the
next under the same variable name...for example:
objWbk.Close
Set objWbk = nothing
Set objWbk = objXL.Workbooks.Open(CurrentProject.Path & _
"\testfile2.xls")
.....
and so on.

The code for the actual calculations should not change, except you might
have to refer to the workbook(s) and specific worksheet(s) more explicitly.
For example, earlier (in Excel VBA) if you had something like:
ThisWorkbook.Worksheets("Sheet1").cells("A3").value = _
ThisWorkbook.Worksheets("Sheet1").cells("A1") +
ThisWorkbook.Worksheets("Sheet1").cells("A2")

now you might want to use something like:
objWbk.Worksheets("Sheet1").cells("A3).value = ...... and so on.

depending on how many sheets you access, you could also define an object
variable of type Excel.Worksheet as that would shorten your code a lot.
Again, example:
Dim objSht as Excel.Worksheet
Set objSht = objWbk.Worksheets("Sheet1")

and now your code simply becomes:
with objSht
.cells("A3").value = .cells("A1") + .cells("A2")
end with

That's just a little bit of intro, since I am myself currently using a huge
access database with lots of VBA to practically generate custom reports in
Excel from within Access. Let me know if this helps!
 
Back
Top