VBA Code to Import File on Desktop

[rmobley232]

I have created a code to import a workbook that will be saved on my desktop on a daily basis to do a few calculations. I need assistance with the two following requests:
1. There will be occasions when there will not be a file, so I would like to write a command to skip the import and move onto the next step in the code if the file does not exist
2. Since this is creating a query where the name of the tab will be the same everyday, how do I avoid the run time error message of "A query with the name 'SS' already exists' (let me know if you need the full error#)

I am running Windows 10 and Office 365 version of Excel. Thanks so much for the input!!

ActiveWorkbook.Queries.Add Name:="SS", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Excel.Workbook(File.Contents(""C:\Users\br4rkm\OneDrive - W.W Grainger, inc\Desktop\Offline Maint SS " & Format(Now(), "mmddyy") & ".xlsx""), null, true)," & Chr(13) & "" & Chr(10) & " SS_Sheet = Source{[Item=""SS"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(SS_Sheet, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers""" & _
",{{""Material"", type text}, {""Location"", type text}, {""New Safety Stock"", type number}, {""Request Type"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=SS;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [SS]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "SS"
.Refresh BackgroundQuery:=False
End With
ActiveSheet.ListObjects("SS").TableStyle = ""
ActiveSheet.ListObjects("SS").ShowAutoFilterDropDown = False

 

[John_w]

Try this:

    Dim workbookFile As String
    Dim SSworkbookQuery As WorkbookQuery
    
    workbookFile = "C:\Users\br4rkm\OneDrive - W.W Grainger, inc\Desktop\Offline Maint SS " & Format(Date, "mmddyy") & ".xlsx"
    
    If Dir(workbookFile) <> vbNullString Then
    
        Set SSworkbookQuery = Nothing
        On Error Resume Next
        Set SSworkbookQuery = ActiveWorkbook.Queries("SS")
        On Error GoTo 0
        
        If SSworkbookQuery Is Nothing Then

            ActiveWorkbook.Queries.Add Name:="SS", Formula:= _
                "let" & Chr(13) & "" & Chr(10) & " Source = Excel.Workbook(File.Contents(""" & workbookFile & """), null, true)," & Chr(13) & "" & Chr(10) & " SS_Sheet = Source{[Item=""SS"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(SS_Sheet, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers""" & _
                ",{{""Material"", type text}, {""Location"", type text}, {""New Safety Stock"", type number}, {""Request Type"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
            With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
                "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=SS;Extended Properties=""""" _
                , Destination:=Range("$A$1")).QueryTable
                .CommandType = xlCmdSql
                .CommandText = Array("SELECT * FROM [SS]")
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .PreserveColumnInfo = True
                .ListObject.DisplayName = "SS"
                .Refresh BackgroundQuery:=False
            End With
    
        Else
       
            ActiveSheet.ListObjects("SS").QueryTable.Refresh
        
        End If
        
    End If
    
    ActiveSheet.ListObjects("SS").TableStyle = ""
    ActiveSheet.ListObjects("SS").ShowAutoFilterDropDown = False

PS please use vba code tags.

 

[rmobley232]

Thank you so much, this worked great!