MS Access Macro to Open, Save, and Close and existing Excel File in SharePoint

How can I adapt the code below that is currently working in Excel to work in a macro module in Access 2007 to open an excel file in SharePoint in edit mode, save it, then close it.

Here is the Excel VBA that is working which MWGainesJR recently helped create:

Private Sub Workbook_Open()

Dim wb As Workbook
Dim xlFile As String
Dim wbname As String
xlFile = "http://sharepoint/sites/MYD_18434/Report%20Library/Bankruptcy/BK%20Timely%20Setup%20Removal%20Daily%20Report/Bk%20Timely%20Setups%20Removals%20Dashboard.xls"
   
'Open Excel File.
Set wb = Workbooks.Open(xlFile)

wbname = wb.Name
wb.Close True
   
MsgBox wbname & " Workbook saved"
   
End Sub

Open in new window

LVL 6
prodempseyAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
GrahamMandenoConnect With a Mentor Commented:
From Access you need to create an instance of Excel and then you can use very similar code (see below)
--
Graham Mandeno [Access MVP]

Dim xlApp as Object ' <<< add
Dim wb As Object ' <<< change
Dim xlFile As String
Dim wbname As String

Set xlApp = CreateObject("Excel.Application") ' <<< add

xlFile = "http://sharepoint/sites/MYD_18434/Report%20Library/Bankruptcy/BK%20Timely%20Setup%20Removal%20Daily%20Report/Bk%20Timely%20Setups%20Removals%20Dashboard.xls"
   
'Open Excel File.
Set wb = xlApp.Workbooks.Open(xlFile) ' <<< change

wbname = wb.Name
wb.Close True

MsgBox wbname & " Workbook saved"

xlApp.Quit ' <<< add
Set wb = Nothing ' <<< add
Set xlApp = Nothing ' <<< add

Open in new window

0
 
mikeadaCommented:
when running from in access, do you want the file to open in excel? or are you going to do something in code and save it?
0
 
prodempseyAuthor Commented:
It worked!

Option Compare Database
Sub OpenSpecific_xlFile()



Dim xlApp As Object ' <<< add
Dim wb As Object ' <<< change
Dim xlFile As String
Dim wbname As String

Set xlApp = CreateObject("Excel.Application") ' <<< add

xlFile = "http://sharepoint/sites/MYD_18434/Report%20Library/Bankruptcy/BK%20Timely%20Setup%20Removal%20Daily%20Report/Bk%20Timely%20Setups%20Removals%20Dashboard.xls"
   
'Open Excel File.
Set wb = xlApp.Workbooks.Open(xlFile) ' <<< change

wbname = wb.Name
wb.Close True

MsgBox wbname & " Workbook saved"

xlApp.Quit ' <<< add
Set wb = Nothing ' <<< add
Set xlApp = Nothing ' <<< add

End Sub
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.