Hi, I've been using access for a while but never really used modules... Today I'm facing a new challenge. It might be really simple for you guys but since I dont know too much about VB, it is very tricky for me.
What I want to do is build a module that would open an excel file, save and close it immediately afterwards. Basically, all I need is excel to load an excel workbook (maybe excel will already be opened) refresh its links with my access db, save and close. (id like it if the user didnt see the excel program openiong and closing...)
Someone on these boards already posted a similar question and someone answered him with the following code, that I found very interesting.:
Dim xlApp As excel.Application
Dim xlWkb As excel.Workbook
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err.number0 Then
' excel not already open
Set xlApp = New excel.Application
Set xlWkb = xlApp.Workbooks.Open("X:\Martin\AFMQ\DB\afmq.xls")
xlWkb.Visible = True
' do whatever it is you got to do.
if no xlApp.UserControl then
' opened from code
Set xlWkb = Nothing
Set xlApp = Nothing
My problem is I have no clue how to adapt it to my access db,... when i start a new module, I always get the following line at the top...
Option Compare Database
should it stay there?
And when I tried pushing the play button, it added this to teh bottom of the code...
Maybe the code i got from the boards isnt good, but it looks good to my noob eye. My guess is I only have troubles adapting it to my db.
Ok How about this
go to the references and choose Microsoft Excel ?.? Object Library
where ?.? is a number based on the version of excel you have mine is 9.0
next put this code in
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
'set references to excel objects
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Open("K:\Information\David\OP Activity.xls", True)
'save the workbook as a new name
xlBook.SaveAs "K:\Information\David\OP Activity2.xls"
'close and quit out of excel
'get rid of the object references
Set xlBook = Nothing
Set xlApp = Nothing
since we are not giving the user any control we can open a new application of excel without any problem,
you may need to put in some error handling if the workbook has been moved or renamed
but this will do the job that you want to do
Ok this is fairly simple in the Code editor
on the Menu Bar Tools-> References a box appears scroll down until you find
Microsoft Excel ?.? object library click the check box by it's name and then press ok
Do you have a module open if not open one and try again or do you have anything highlighted on your project window (should be on the left hand side if not press ctrl + R) click on something in your current DB and try again
are you assigning it to a control like a button or how do you want to call it,
if it is a button go to the properties then to events choose the click event and click the ... button choose code then when the new code window pops up you can put in the line
pedantic coder time
This is just for reference for the future what i provided for you was a sub routine, a function is a routine that returns a value and is a distinct type in VBA
Would it be possible to add something in the code to refresh the external data contained in the excel file before saving it? It already is on auto-refresh on startup but I dont think its working when called from the code... or maybe you can tell me...
And could there be a way to see the computer open the excell file? Just to be sure its working,... When I push the play button it seams very quick,.. almost too quick hehe
To answer your previous question, Id like to lauch it from a macro. How can I do it? Im not sure exactly when the event will take place, but it would probably be automatically after a form closes or maybe on a button,... So i was wondering if it was possible to insert it in a macro.
Really curious, it doesnt seem to be working....
well it doesnt update,...
In my excel file, one of the sheet imports data from an access query, another sheet caculates some totals with the imported values. Then the sheet with the new calculated values is a linked table in access....
Therefore, when values in the access query change, Excel when updating should transfer the new values from access to excel, than back to access in the linked table,... but nothing happens when i use the sub command.
also, when i open my xcell wrkbook i get a popup that asks me if im sure i want an automatic refresh, could that cause the problem?