Hoping this is only a slight vba tweak needed.

[123rickfear]

Hi all,

I would like to be able to save a filename directly to our sharepoint.

However, when I run the macro I get an error message from the upload center saying the path is not found.

below is my code. Please can someone help me.

Sub SubmitAandAData()
Dim Sh As Worksheet
Dim folder As String
Dim filename As String
Application.DisplayAlerts = False
Worksheets("DataEntry").Range("A1:Z500").Copy
Worksheets("DataEntry").Range("A1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
For Each Sh In Worksheets
If Sh.Name <> "DataEntry" Then Sh.Delete
Next
Application.DisplayAlerts = True
filename = "[URL]https://xxxxxxxxxx.sharepoint.com/teams/grouphr/hrsystems/Absence%20and%20Attendance/Forms/AllItems/[/URL]" & Range("B3") & Range("B5") & ".xlsm"
     
    ActiveWorkbook.SaveAs filename
  
    Application.DisplayAlerts = True
    
    ActiveWorkbook.Close
MsgBox "Saved"
End Sub

 

[Perpa]

123rickfear,
You might clarify what values are in B3 and B5...However, it could be the way you refer to the two Ranges, B3 and B5 in this line of code:

filename = "[URL]https://xxxxxxxxxx.sharepoint.com/te...orms/AllItems/[/URL]" & Range("B3") & Range("B5") & ".xlsm"

Assuming those 'values' require no separation or separator such as '/', then this should work:

filename = "[URL]https://xxxxxxxxxx.sharepoint.com/te...orms/AllItems/[/URL]" & Range("B3").value & Range("B5").value & ".xlsm"

'OR... 
filename = "[URL]https://xxxxxxxxxx.sharepoint.com/te...orms/AllItems/[/URL]" & cells(3,"B") & cells(5,"B") & ".xlsm"

The 'cells' format defaults to the 'value'...the 'Range' format requires the clarification of 'value', or 'address' or whatever.
Perpa

 

[PatOBrien198]

I don't believe that you can use the SaveAs method to save to an "HTTP://" site.
You'll need to find out the actual server name and share name.

 

[123rickfear]

Our sharepoint is cloud based, so doesn't have a server address.

I need to save a file to a location, but don't want people to be able to access that location (unless they have permission).

Any suggestions?

 

[Fluff]

AFAIK if you don't have access permission to a site, then you cannot save to it.