VBA to Save the open file and then Open from location.

[cbarryb]

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):

'
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

 

[johnnyL]

See if this helps:

Sub SaveODlist()
'
' SaveODlist Macro
'
    Dim Filename    As String
    Dim PathName    As String
    Dim wbstring    As String
'
''    wbstring = "C:\Users\bchapman\Desktop\Needed\Stops\To UPLOAD\" & Format(Now(), "DD.MM.YYYY") & " SAP Overdue List.xlsx"
    PathName = "C:\Users\" & Application.UserName & "\Desktop\Needed\Stops\To UPLOAD\"
    Filename = Format(Now(), "DD.MM.YYYY") & " SAP Overdue List.xlsx"
    wbstring = PathName & Filename
'
    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"
    Workbooks.Open Filename:=wbstring
    Windows(Filename).Activate

'To Format as number and to Text to Columns the Acc number
 
    Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
'
    Range("B4").Select
    Range(Selection, Selection.End(xlDown)).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]],'[Filename]Sheet1'!C2:C20,19,FALSE)"
    Range("F3").Select
End Sub

 

[cbarryb]

See if this helps:

Sub SaveODlist()
'
' SaveODlist Macro
'
    Dim Filename    As String
    Dim PathName    As String
    Dim wbstring    As String
'
''    wbstring = "C:\Users\bchapman\Desktop\Needed\Stops\To UPLOAD\" & Format(Now(), "DD.MM.YYYY") & " SAP Overdue List.xlsx"
    PathName = "C:\Users\" & Application.UserName & "\Desktop\Needed\Stops\To UPLOAD\"
    Filename = Format(Now(), "DD.MM.YYYY") & " SAP Overdue List.xlsx"
    wbstring = PathName & Filename
'
    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"
    Workbooks.Open Filename:=wbstring
    Windows(Filename).Activate

'To Format as number and to Text to Columns the Acc number
 
    Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
'
    Range("B4").Select
    Range(Selection, Selection.End(xlDown)).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]],'[Filename]Sheet1'!C2:C20,19,FALSE)"
    Range("F3").Select
End Sub

Thank you, I had to change the Application.Username to "Environ("USERNAME")" in the end, it works perfect now though, so thank you again

 

[johnnyL]

How about:

    PathName = "Environ("USERPROFILE") & "\Desktop\Needed\Stops\To UPLOAD\"