Hello all,
My company just migrated over to One Drive from a corporate shared drive, our inventory spreadsheet would use ThisWorkbook.path to save a copy each time someone would email it, now with One Drive using an HTTP protocol I get the run time error. Would someone please help me with what I need to change up or insert in my code?
Thanks ahead of time.
Terminal = "Richmond"
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
saveformat = Application.DefaultSaveFormat
ans = MsgBox("This will Email a copy of this report via OUTLOOK" & vbCrLf & vbCrLf & _
"OK?", vbYesNoCancel + vbQuestion)
If ans = 2 Then Exit Function
If ans = 7 Then MsgBox "Try again": Exit Function
FileExtStr = ".xlsx": FileFormatNum = 51
FileVer = 1
salesdate = Sheets("Tank Report").Range("h4").Text
salesdate1 = Application.Text(salesdate, "mm-dd-yyyy")
VerStr = " (" & FileVer & ")"
suggname = Terminal & " Inventory Report " & salesdate1 & VerStr & FileExtStr
'>>>>>>>>>>
'Copies File over to new workbook
'use for Emailing via Notes
Set wb = ActiveWorkbook
Set Wbsf = Workbooks.Add
Set Wbsf = ActiveWorkbook
wbsf1 = suggname
' Checks to see if file exists ...
'Add Digit to version number
Do While Dir(ThisWorkbook.Path & "\" & wbsf1) <> ""
ExisFile = Dir(ThisWorkbook.Path & "\" & wbsf1)
FileVer = Mid(ExisFile, InStr(ExisFile, "(") + 1, 1)
FileVer = FileVer + 1
VerStr = " (" & FileVer & ")"
suggname = Terminal & " Inventory Report " & salesdate1 & VerStr & FileExtStr
wbsf1 = suggname
'MsgBox FileVer
'MsgBox "OOPS File Exists", vbOKCancel + vbQuestion
Loop
On Error GoTo Errorhandler1
ActiveWorkbook.SaveAs fileName:=ThisWorkbook.Path & "\" & wbsf1, FileFormat:=FileFormatNum
My company just migrated over to One Drive from a corporate shared drive, our inventory spreadsheet would use ThisWorkbook.path to save a copy each time someone would email it, now with One Drive using an HTTP protocol I get the run time error. Would someone please help me with what I need to change up or insert in my code?
Thanks ahead of time.
Terminal = "Richmond"
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
saveformat = Application.DefaultSaveFormat
ans = MsgBox("This will Email a copy of this report via OUTLOOK" & vbCrLf & vbCrLf & _
"OK?", vbYesNoCancel + vbQuestion)
If ans = 2 Then Exit Function
If ans = 7 Then MsgBox "Try again": Exit Function
FileExtStr = ".xlsx": FileFormatNum = 51
FileVer = 1
salesdate = Sheets("Tank Report").Range("h4").Text
salesdate1 = Application.Text(salesdate, "mm-dd-yyyy")
VerStr = " (" & FileVer & ")"
suggname = Terminal & " Inventory Report " & salesdate1 & VerStr & FileExtStr
'>>>>>>>>>>
'Copies File over to new workbook
'use for Emailing via Notes
Set wb = ActiveWorkbook
Set Wbsf = Workbooks.Add
Set Wbsf = ActiveWorkbook
wbsf1 = suggname
' Checks to see if file exists ...
'Add Digit to version number
Do While Dir(ThisWorkbook.Path & "\" & wbsf1) <> ""
ExisFile = Dir(ThisWorkbook.Path & "\" & wbsf1)
FileVer = Mid(ExisFile, InStr(ExisFile, "(") + 1, 1)
FileVer = FileVer + 1
VerStr = " (" & FileVer & ")"
suggname = Terminal & " Inventory Report " & salesdate1 & VerStr & FileExtStr
wbsf1 = suggname
'MsgBox FileVer
'MsgBox "OOPS File Exists", vbOKCancel + vbQuestion
Loop
On Error GoTo Errorhandler1
ActiveWorkbook.SaveAs fileName:=ThisWorkbook.Path & "\" & wbsf1, FileFormat:=FileFormatNum