I have an Access & Excel linked files. I want to open the excel file & then close the same using macro. Actually this will refresh the data in excel sheet & return the result to Access. I have tried to use the "RunApplication" command but unable to do it. Can someone explain the procedure to do the same.
Thanks Mr B for your reply & link provided. But my problem is I am not conversant with VB & thats why opted to do this using a macro. Can you please help me do this using macro? I want to know how the RunApplication command works in macro. Or else can you please explain where to write the code you have suggested. One more query is "is it possible to refresh the data in excel without opening & closing it, because that is what I want from my program".
Below is a Public user defined function that will open and close the Excel spreadsheet that you provide the full path and file name for when you call it from a macro.
Code:
Public Function OpenExcel(PathAndFileName As String)
'declare an object variable for the new instance of Excel
Dim objXLApp As Excel.Application
'delcare an object variable for the new Workbook instance
Dim objXLBook As Excel.Workbook
'create the new instance of Excel
Set objXLApp = New Excel.Application
'open the Excel workbook file using the Path
'and filename passed to this funciton
Set objXLBook = objXLApp.Workbooks.Open(PathAndFileName)
objXLApp.Visible = True
'wait for the workbook to open
DoEvents
'turn the message alerts off
objXLApp.Application.DisplayAlerts = False
'save any changes to the workbook
objXLBook.Save
'close the workbook
objXLBook.Close
'rugn the messge alerts back on
objXLApp.Application.DisplayAlerts = True
'close this instance of Excel
objXLApp.Quit
'destroy the object variables
Set objXLBook = Nothing
Set objXLApp = Nothing
End Function
I have documented each line of the function so you might be able to follow each action that is being taken.
Copy the code above and crate a new Module and past the copied code into the new module. Name this new module "modOpenAndCloseExcel". (Actually you can use any name you like as long as you don't name it with the same name as the Public Function which is "OpenExcel".)
Create a macro or add to an existing macro the RunCode action provideing the function name (OpenExcel) and the full path and filename in quotes (example: "C:\Temp\MyExcelTest.accdb":
RunCode
Function Name OpenExcel ("provide the path and filename here")
I use the procedure as described by you but the excel file doesn't open. It is showing a message of "Compile error: User-defined type not defined" & showing the code saved in the module with one line highlighted which I have highlighted in Red under quote. Actually I am not at all conversant with VB code & therefore unable to solve i by myself. I have pasted your code in the module without changing anything and saving it by name Module1.I created a macro & in its Runcode action writing OpenExcel ("C:\Users\PC2011-30\Desktop\New YEAR 2012 FINAL LIST.xls") & then running the macro but it is showing error . I think I did exactly as suggested but not getting the result. please help.
Below is a Public user defined function that will open and close the Excel spreadsheet that you provide the full path and file name for when you call it from a macro.
Code:
Public Function OpenExcel(PathAndFileName As String)
'declare an object variable for the new instance of Excel
Dim [B][COLOR="Red"]objXLApp As Excel.Application[/COLOR][/B]
'delcare an object variable for the new Workbook instance
Dim objXLBook As Excel.Workbook
'create the new instance of Excel
Set objXLApp = New Excel.Application
'open the Excel workbook file using the Path
'and filename passed to this funciton
Set objXLBook = objXLApp.Workbooks.Open(PathAndFileName)
objXLApp.Visible = True
'wait for the workbook to open
DoEvents
'turn the message alerts off
objXLApp.Application.DisplayAlerts = False
'save any changes to the workbook
objXLBook.Save
'close the workbook
objXLBook.Close
'rugn the messge alerts back on
objXLApp.Application.DisplayAlerts = True
'close this instance of Excel
objXLApp.Quit
'destroy the object variables
Set objXLBook = Nothing
Set objXLApp = Nothing
End Function
I have documented each line of the function so you might be able to follow each action that is being taken.
Copy the code above and crate a new Module and past the copied code into the new module. Name this new module "modOpenAndCloseExcel". (Actually you can use any name you like as long as you don't name it with the same name as the Public Function which is "OpenExcel".)
Create a macro or add to an existing macro the RunCode action provideing the function name (OpenExcel) and the full path and filename in quotes (example: "C:\Temp\MyExcelTest.accdb":
RunCode
Function Name OpenExcel ("provide the path and filename here")
Seems I failed to tell you that you will need to set a reference to your version of MS Excel. To do this, open the VBA editor (open your module) and then from the menu select Tools and then References. You will probably see "Missing ..." in front of the existing reference to Microsoft Excel 14.0 Object Library.". Uncheck the checkbox and then scroll down the list and locate the "Microsoft Excel Object Library" for your version of Excel and check that checkbox. When you get this complete, the code should run correctly without error.
Sorry if I am asking too much. But my problem was not solved as I was thinking after applying the suggestion. After applying the above procedure I am able to open & close the excel file but the data in the excel sheet is not being refreshed which was my prime & only requirement. I have set in the connection properties in excel to refresh the data whenever the file is opened but the data is not getting refreshed. However, when I manually open the fie the data is getting refreshed every time. I added a line of code as "objXLBook.RefreshAll" in the main code but this too didn't work. And I am at the beginning of the problem again. I tried to search the web & this forum but didn't find any answer to the problem. Can anyone please help me on this. Also, Is there anyway to refresh the data in excel from within access without opening the excel file.
