Hi All,
I run a report in SAP and then it opens in Excel to be saved, its only a read only SAP to Excel temp file and once saved, you have to then go and search and open from the location.
I would like to save the file with the date from =Now and then the text at the end, in the path of a folder on the users desktop, I have written this code and I keep getting an error, saying the path may not be valid, or I'm using a special char, can anyone see where I am going wrong please(Would it be the Space before SAP Overdue List.xlsx):
Any Help is greatly appreciated, thanks
I run a report in SAP and then it opens in Excel to be saved, its only a read only SAP to Excel temp file and once saved, you have to then go and search and open from the location.
I would like to save the file with the date from =Now and then the text at the end, in the path of a folder on the users desktop, I have written this code and I keep getting an error, saying the path may not be valid, or I'm using a special char, can anyone see where I am going wrong please(Would it be the Space before SAP Overdue List.xlsx):
VBA Code:
'
Sub SaveODlist()
'
' SaveODlist Macro
Dim wbstring As String
wbstring = "C:\Users\bchapman\Desktop\Needed\Stops\To UPLOAD\" & Format(Now(), "DD.MM.YYYY") & " SAP Overdue List.xlsx"
Windows("Worksheet in Basis (1)").Activate
ActiveWorkbook.SaveAs Filename:=wbstring
Workbooks.Open Filename:="C:\Users\" & UsersName & "\Desktop\Needed\Stops\To UPLOAD\" & Format(Now(), "DD.MM.YYYY") & " SAP Overdue List.xlsx"
Windows(Format(Now(), "DD.MM.YYYY") & " SAP Overdue List.xlsx").Activate
'To Format as number and to Text to Columns the Acc number
Columns("C:C").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("B4"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1)), TrailingMinusNumbers:=True
Selection.NumberFormat = "0"
ActiveWorkbook.Save
Windows("Web Report 06-08-2021.xlsx").Activate
Range("F2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP([@[SAP Account]],'[" & Format(Now(), "DD.MM.YYYY") & " SAP Overdue List.xlsx]Sheet1'!C2:C20,19,FALSE)"
Range("F3").Select
End Sub
Any Help is greatly appreciated, thanks