Open a seperate SharePoint excel file and save it in VBA

[pasjauo]

Hey all VBA geniusses,

I have a hopefully simple question I can't seem to work.

I have on sharepoint two different excel files. Because of the obvious user edit limitations in a file on SharePoint I have created a "data" file and a "input" file, where the user use the input file which automatically inputs the data into the data file. This will do so the data file is not being used for other than the time than it takes to open-save input-save-close.

To open, save and close the workbook i use:

Workbooks.Open ("LINK TO SHAREPOINT.xlsm")

ActiveWorkbook.Save


ActiveWorkbook.Close

There is alot of code inbetween that has no influence of the above afaik.

The opening of the documents works fine, it is the save that is the problematic one. No matter how i present the code it always saves the file local instead of saving the file at its destination, which is SharePoint.

Is there something I'm missing? ActiveWorkbook must the right definition as it is in fact opened and the "active" workbook?

 

[pasjauo]

To perhaps clarify more about how the code is used in context here is all of it:

Private Sub tilfoejsag_knap_Click()


Dim emptyRow As Long
Dim i As Long


If OptionButton1.Value = True Then
Workbooks.Open ("Link to Sharepoint")
Worksheets("AutomaterLukkedeSager").Activate
ActiveSheet.Unprotect "pw"
End If


If OptionButton2.Value = True Then
Workbooks.Open ("Link to Sharepoint")
Worksheets("AutomaterÅbneSager").Activate
ActiveSheet.Unprotect "pw"
End If


If OptionButton3.Value = True Then
Workbooks.Open ("Link to Sharepoint")
Worksheets("AutomaterÅbnesager").Activate
ActiveSheet.Unprotect "pw"
End If


If OptionButton4.Value = True Then
Workbooks.Open ("Link to Sharepoint")
Worksheets("AutomaterÅbneSager").Activate
ActiveSheet.Unprotect "pw"
End If


'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("B:B")) + 1
      
'Transfer information
If UlovligSpil_cbox = True Then
Cells(emptyRow, 16).Value = Ulovligspil_text.Value
End If


Cells(emptyRow, 18).Value = Dato_box.Value
Cells(emptyRow, 2).Value = OpstillerCVR_box.Value
Cells(emptyRow, 4).Value = SpillestedNavn_box.Value
Cells(emptyRow, 5).Value = SpillestedAdresse_box.Value
Cells(emptyRow, 6).Value = Postnummer_box.Value
Cells(emptyRow, 8).Value = Captia_box.Value
Cells(emptyRow, 9).Value = Tilsyn_list.Value
Cells(emptyRow, 10).Value = antalautomater_box.Value
Cells(emptyRow, 11).Value = ComboBox1.Value
Cells(emptyRow, 12).Value = ComboBox2.Value
Cells(emptyRow, 13).Value = ComboBox3.Value


If OptionButton1.Value = True Then
    Cells(emptyRow, 14).Value = "Ingen Bemærkninger"
End If
If OptionButton2.Value = True Then
    Cells(emptyRow, 14).Value = "Option2"
End If
If OptionButton3.Value = True Then
    Cells(emptyRow, 14).Value = "Option3"
End If
If OptionButton4.Value = True Then
    Cells(emptyRow, 14).Value = "Option4"
End If


Cells(emptyRow, 17).Value = Medarbejder_text.Value


Unload Me


ActiveSheet.Protect "pw"


ActiveWorkbook.Save


ActiveWorkbook.Close


MsgBox "Sag tilføjet"


End Sub

 

[pasjauo]

I might have found the problem myself. It seems that because i use Excel 2016 it opens the data file as read-only and not as write-read. Therefore i should find a way to make it write-read in the VBA.

Something like this should work, yes?

[COLOR=#333333][FONT=&quot]Private Sub Workbook_Open()
[/FONT][/COLOR][COLOR=#333333][FONT=&quot]If Application.Version = 16 then[/FONT][/COLOR]
[COLOR=#333333][FONT=&quot]ActiveWorkbook.LockServerFile[/FONT][/COLOR]
[COLOR=#333333][FONT=&quot]Else[/FONT][/COLOR]
[COLOR=#333333][FONT=&quot]End If[/FONT][/COLOR]
[COLOR=#333333][FONT=&quot]End Sub[/FONT][/COLOR]

 

[pasjauo]

Now I have a minor update to this problem.

The users are using Excel 2013 and Excel 365. The users with Excel 365 cannot use the spreadsheet atm (i have not been using the code in #3 because that makes the 2013 users not able to use the spreadsheet).

Is there a way to give both user-versions access? It is possible to make two different input-sheets but i cannot make two different data-sheets unfortunately.

Hope people understand my blabber

 

[hamiltpj]

hi pasjauo, did you ever solve your problem? i'm trying to do the exact same thing.