We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Solved
MS Access Macro to Open, Save, and Close and existing Excel File in SharePoint
Posted on 2010-08-12
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:
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3 Comments
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?
Active 2 days ago
From Access you need to create an instance of Excel and then you can use very similar code (see below)
--
Graham Mandeno [Access MVP]
--
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
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.Applic
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(xlFil
wbname = wb.Name
wb.Close True
MsgBox wbname & " Workbook saved"
xlApp.Quit ' <<< add
Set wb = Nothing ' <<< add
Set xlApp = Nothing ' <<< add
End Sub
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.Applic
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(xlFil
wbname = wb.Name
wb.Close True
MsgBox wbname & " Workbook saved"
xlApp.Quit ' <<< add
Set wb = Nothing ' <<< add
Set xlApp = Nothing ' <<< add
End Sub
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening. This article shows how to create and use them.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications. You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses
Course of the Month10 days, 22 hours left to enroll
719 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.