Processing a list of excel files (2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

Works wonderfully well – many thanks

Reply | Quote

Works wonderfully well – many thanks

Reply | Quote

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.

Reply | Quote