importing data if it's a certain filename

[token_remedie]

Hey all,

bit of a noob here, I'm trying to import data if it's worksheet is called 'Master' I eventually want to expand and if it's called 'Master' import and run these queries, if it's called redistributed then import and run these queries. Basically I'm importing to a new table, data clensing and then merging with the existing table. But I can't get it working and I'm stuck. Any help would be much appreciated. The errors I'm getting are the next and end with are in the wrong spot, eg. next without for, end with without with.

Private Sub Command0_Click()
    Dim fd As FileDialog
    Dim strfile As String
    Dim strsearchpath As String
    Dim vrtSelectedItem As Variant
    Dim strSql As String
    Dim SheetName As String:   SheetName = "Master"
    Dim wb As Workbook, ws As Worksheet

      
 Set myDialog = Application.FileDialog(msoFileDialogOpen)
 'Use strSearchPath  if you want to start in a particular directory. You can leave it out if you wish (as I have it commented out right now) and it will start with a default directory of Windows choosing.
 strsearchpath = "c:\"
 
 With myDialog
 
    .AllowMultiSelect = False
    .Filters.Add "Excel Files", "*.xlsx", 1
     .Title = "locate files"
     .InitialFileName = strsearchpath
     DoCmd.SetWarnings False
 
If .Show = True Then
    For Each vrtSelectedItem In .SelectedItems
    'doublechecking file name
     MsgBox "file chosen = " & vrtSelectedItem
       'I pass the file information to another routine that handles the transfer
      
       
       
      If vrtSelectedItems.SheetExists(SheetName) Then
            DoCmd.TransferSpreadsheet acImport, , "importtable", vrtSelectedItem, True, SheetName
       Next vrtSelectedItem
       Else
       MsgBox "not master"
       
   
     End If

      End With
      
    
    ' delete blank rows
    strSql = "Delete FROM importtable Where Len(Trim([asset number] & ' '))=0 "
    'runSQL for strsql
    DoCmd.RunSQL (strSql)
     
     MsgBox "imported"

     
 
 End Function

[token_remedie]

I fixed the first problem, if anyone is interested I did this:

 With myDialog
 
    .AllowMultiSelect = False
    .Filters.Add "Excel Files", "*.xlsx", 1
     .Title = "locate files"
     .InitialFileName = strsearchpath
     DoCmd.SetWarnings False
 
If .Show = True Then
    For Each vrtSelectedItem In .SelectedItems
    'doublechecking file name
     MsgBox "file chosen = " & vrtSelectedItem
       'I pass the file information to another routine that handles the transfer
           Next
            End If
             End With
      If vrtSelectedItems.SheetExists(SheetName) Then
            DoCmd.TransferSpreadsheet acImport, , "importtable", vrtSelectedItem, True, SheetName
       
       Else
       MsgBox "not master"
          
     End If

but now it's saying obbject required.....where am I going wrong there?

[orange]

Have you considered using
DoCmd.TransferSpreadsheet acImport.....
directly? You could have a form or option group to select the "sheet" you want and run the Transferspreadsheet......

[token_remedie]

that is a REALLY good idea. *high five*
Thanks orange