Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Processing a list of excel files (2000)

    I have an import routine in Access which takes input from a file called data.xls and populates a number of tables. The import works fine.
    The imported file was always called data.xls, but I have a whole history of old xls files in the same format which I wish to import using the same routine.

    I could rename each xls file to data.xls and rerun the import, but I'd like to be able to select the files from a list and run the import for the name of the file selected.

    How do I produce a list of the .xls file names held in a specified directory which I can select from as input to the routine? Preferably I'd like to present a combo box from which the data can be selected.

    John

  2. #2
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Processing a list of excel files (2000)

    Works wonderfully well - many thanks

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Processing a list of excel files (2000)

    Create a combo box or list box on a form.
    Set its Row Source Type property (in the Data tab of the Properties window) to Value List.
    Populate the combo / list box in the On Load event of the form:

    Private Sub Form_Load()
    Dim strFile As String
    Dim strList As String
    strFile = Dir("F:Excel*.xls")
    Do While Not (strFile = "")
    strList = strList & strFile & ";"
    strFile = Dir
    Loop
    Me.cboFiles.RowSource = strList
    End Sub

    Replace cboFiles by the name of the combo/list box. You can refer to the value of the combo/list box in your routine.

Posting Permissions

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