Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004

    Unanswered: File naming in Access and Excel VBA

    I'm trying to open some files within a folder without fully naming them, at the moment I've managed to open what I want if i name the file (Example200804.xls) but ideally I'd want to use an asterix as in DOS to make part of the filename variable (Example*.xls).
    Does anyone know what VBA I could use for this?

    Last edited by Phillip123; 08-20-04 at 06:13.

  2. #2
    Join Date
    Dec 2002
    Préverenges, Switzerland
    here's a little (DAO) something i use to gather XLS names into a table (...and then process them one-by-one, but that's another story.


    Private Function PathOK() As Boolean
        'checks that the user defined path exists, and contains XLS files
        'collects the fully-defined file specs for all existing XLS into tbl.FileList
        'returns TRUE if file list contains one or more XLS, else FALSE
        On Error GoTo err_PathOK
        If IsNull(userPath.Value) Then                      'handle empty path
            PathOK = False
            Exit Function
        End If
        Dim dabs As DAO.Database
        Dim recs As DAO.Recordset
        Dim userFile As String                              'the user defined file spec
        Dim dirdFile As String                              'full file spec as collected
        If Right$(userPath.Value, 1) = "\" Then             'define userFile
            userFile = userPath.Value & "*.XLS"             'with trailing "\"
        Else                                                'or
            userFile = userPath.Value & "\*.XLS"            'without trailing "\"
        End If
        dirdFile = Dir(userFile)                            'check for a file
        If dirdFile = "" Then                               'if no files found
            PathOK = False                                  'set the sad return
            MsgBox "No XLS found in " & userFile, vbCritical + vbOKOnly, "Harvester.PathOK"
            Exit Function                                   'and quit
        End If                                              'otherwise at least one file exists so we are OK
        Set dabs = CurrentDb
        Set recs = dabs.OpenRecordset("FileList")           'open recordset on tbl.FileList
        With recs
            Do While dirdFile <> ""                         'as long as we keep finding files
                .AddNew                                     'add a record
                !fileSpec = userPath & "\" & dirdFile       'save the full filespec
                dirdFile = Dir                              'and try again
        End With
        PathOK = True                                       'set the happy return
        Set recs = Nothing
        Set dabs = Nothing                                  'tidy up
        Exit Function                                       'and quit
        PathOK = False                                      'set the sad return
        MsgBox err.Description, vbCritical + vbOKOnly, "Harvester.PathOK"
        Resume exit_PathOK
    End Function
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts