DKazatsky
02-15-2018, 11:58 AM
Hi all,
I have super simple code that takes the excel file you are in and saves it to a SharePoint site. This was developed and is mostly used by people running Office 2010. Now, my company is slowing changing people over to Office 2013. This particular workbook has many macros but the SharePoint upload is the only piece not working any longer. Any thoughts appreciated. Thanks.
Sub uploadToSP_Click()
Dim xlFileName As String
Dim SharepointAddress As String
Dim LocalAddress As String
Dim objNet As Object
Dim FS As Object
Dim drive As String
Dim spPath As String
Dim pos As Integer
'Determine file name
xlFileName = [projectname] & " - Playbook " & Replace([StartDate], "/", "-") & ".xlsm"
'spPath = Range("G14").Value
spPath = [spDir]
'Make sure there is a path to use
If Len(spPath) = 0 Then
MsgBox ("Please enter a Sharepoint directory.")
Exit Sub
Else
'strip everything after the status reports library
pos = InStr(1, spPath, "/Forms", vbTextCompare)
spPath = Left$(spPath, pos)
End If
'set the global vaiable to be used in the email module
spURL = spPath & xlFileName
SharepointAddress = Replace(spPath, "https:", "") & xlFileName
Worksheets("Playbook").Activate
Application.EnableEvents = False
ActiveWorkbook.SaveAs fileName:=SharepointAddress, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled
Application.EnableEvents = True
'Save the file locally to reset the working directory
savePB_Click
End Sub
I have super simple code that takes the excel file you are in and saves it to a SharePoint site. This was developed and is mostly used by people running Office 2010. Now, my company is slowing changing people over to Office 2013. This particular workbook has many macros but the SharePoint upload is the only piece not working any longer. Any thoughts appreciated. Thanks.
Sub uploadToSP_Click()
Dim xlFileName As String
Dim SharepointAddress As String
Dim LocalAddress As String
Dim objNet As Object
Dim FS As Object
Dim drive As String
Dim spPath As String
Dim pos As Integer
'Determine file name
xlFileName = [projectname] & " - Playbook " & Replace([StartDate], "/", "-") & ".xlsm"
'spPath = Range("G14").Value
spPath = [spDir]
'Make sure there is a path to use
If Len(spPath) = 0 Then
MsgBox ("Please enter a Sharepoint directory.")
Exit Sub
Else
'strip everything after the status reports library
pos = InStr(1, spPath, "/Forms", vbTextCompare)
spPath = Left$(spPath, pos)
End If
'set the global vaiable to be used in the email module
spURL = spPath & xlFileName
SharepointAddress = Replace(spPath, "https:", "") & xlFileName
Worksheets("Playbook").Activate
Application.EnableEvents = False
ActiveWorkbook.SaveAs fileName:=SharepointAddress, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled
Application.EnableEvents = True
'Save the file locally to reset the working directory
savePB_Click
End Sub