Solved

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

Posted on 2010-08-12
3
1,070 Views
Last Modified: 2013-11-27
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

0
Comment
Question by:prodempsey
3 Comments
 
LVL 5

Expert Comment

by:mikeada
ID: 33425044
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
 
LVL 20

Accepted Solution

by:
GrahamMandeno earned 500 total points
ID: 33425077
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
 
LVL 6

Author Closing Comment

by:prodempsey
ID: 33425627
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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question