PDA

View Full Version : Accessing Excel Spreadsheet via Sharepoint



DonnaDiablo
10-27-2009, 08:55 AM
Hiya...

I've have a workbook with 2 worksheets in it, that you have to enable Macros to be able to use. I have written code for this to happen.

When Macros are Enabled, the worksheet that you can edit opens and it has 3 Command Buttons entitled SAVE, SAVE AND EXIT and just plain old EXIT.

When this Workbook is accessed directly from my network it works beautifully, everything works as it should and nothing strange happens. However, I have created a link to this workbook via Sharepoint. The workbook opens exactly as it should, yet the Command Buttons just plain don't work. If you press SAVE, nothing happens. If you press SAVE AND EXIT, you get a duplicate of the button appearing just above it - the same happens when you press the EXIT button. Now... i'm guessing that this happens because Sharepoint is opening the Workbook via the Intranet and using Internet Explorer - but as my skills are limited I can't be sure that this is why.

Does anyone have any experience of opening an Excel Wokbook via Sharepoint? Should I be doing anything differently?

My code is as follows:

Sheet 3 (Macros):

No code.

Sheet 1 (Pool Station Checksheet):

Option Explicit

Private Sub CommandButton1_Click() ' Save ONLY
Call ThisWorkbook.SaveMe(False, False)
End Sub

Private Sub CommandButton2_Click() 'Exit Without Saving
bolBail = True
ThisWorkbook.Close False
End Sub

Private Sub CommandButton3_Click() 'Save then Exit
Call ThisWorkbook.SaveMe(False, False)
bolBail = True
ThisWorkbook.Close False
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then
Target.Offset(0, 1) = Environ("username")
End If
End Sub

This Workbook:

Option Explicit
Const WelcomePage = "Macros"

Public Sub SaveMe(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call Workbook_BeforeSave(SaveAsUI, Cancel)
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If bolBail Then Exit Sub
'Turn off events to prevent unwanted loops
Application.EnableEvents = False
'Evaluate if workbook is saved and emulate default propmts
With ThisWorkbook
If Not .Saved Then
Select Case MsgBox("Are you sure that you do not want to save the changes you made to '" & .Name & "'?",vbYesNoCancel + vbExclamation)
Case Is = vbYes
' Exit as doesn't want to save
Case Is = vbNo
Call CustomSave
'Save as really does want to save
Case Is = vbCancel
'Set up procedure to cancel close
Cancel = True
End Select
End If
'If Cancel was clicked, turn events back on and cancel close,
'otherwise close the workbook without saving further changes
If Not Cancel = True Then
.Saved = True
Application.EnableEvents = True
.Close savechanges:=False
Else
Application.EnableEvents = True
End If
End With
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Turn off events to prevent unwanted loops
Application.EnableEvents = False
'Call customized save routine and set workbook's saved property to true
'(To cancel regular saving)
Call CustomSave(SaveAsUI)
Cancel = True
'Turn events back on an set saved property to true
Application.EnableEvents = True
ThisWorkbook.Saved = True
End Sub

Private Sub Workbook_Open()
'Unhide all worksheets
Application.ScreenUpdating = False
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
.Protect Password:="Airwave.1", userinterfaceonly:=True
Next ws
Call ShowAllSheets
Application.ScreenUpdating = True
DoEvents
ThisWorkbook.Saved = True
End Sub

Private Sub CustomSave(Optional SaveAs As Boolean)
Dim ws As Worksheet, aWs As Worksheet, newFname As String
'Turn off screen flashing
Application.ScreenUpdating = False
'Record active worksheet
Set aWs = ActiveSheet
'Hide all sheets
Call HideAllSheets
'Save workbook directly or prompt for saveas filename
If SaveAs = True Then
newFname = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If Not newFname = "False" Then ThisWorkbook.SaveAs newFname
Else
ThisWorkbook.Save
End If
'Restore file to where user was
Call ShowAllSheets
aWs.Activate
'Restore screen updates
Application.ScreenUpdating = True
End Sub
Private Sub HideAllSheets()
'Hide all worksheets except the macro welcome page
Dim ws As Worksheet
Worksheets(WelcomePage).Visible = xlSheetVisible
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVeryHidden
Next ws
Worksheets(WelcomePage).Activate
End Sub
Private Sub ShowAllSheets()
'Show all worksheets except the macro welcome page
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible
Next ws
Worksheets(WelcomePage).Visible = xlSheetVeryHidden
End Sub


Thanx for taking the time to read this guys... any help you can give is, as always, gratefully received.

Donna :o)