Listbox to show files

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Before I dive into this I was wondering if this is at all psossible. I have
a subfolder located at the BE of my DB. In this folder I have several Excel
workbooks all with just one worsheet that I need to select to import data to
a temp table then update/append into my regular tables. There are new Excel
forms which get placed into this folder on a routine basis. So it is not
possible to link to them. Is it possible to have a listbox to show all the
workbooks (approximately 20-50) then select which one I want to import? Any
input on this idea would be greatly appreciated, in a time crunch.
 
Thanks for the response. However, I a few problems and questions that you or
someone might assist me on. Almost everything is working.

Here is my code that I came up with so far for test purposes. This works as
is, but I rather just see the name of the file in the list box rather than
the whole string.

Private Sub cmdQuery_Click()
Dim strLoc As String
strLoc = DataMdb & "Excel Info\"
CurrentDb.Execute "DELETE * FROM tblExcelImports"

With Application.FileSearch
.NewSearch
.LookIn = strLoc
.FileName = "*.xls"
If .Execute(SortBy:=msoSortByLastModified, _
SortOrder:=msoSortOrderAscending) > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count

strSQL = "Insert Into tblExcelImports (ExcelTitle)" & _
"SELECT " & "'" & .FoundFiles(i) & "'" & ";"

CurrentDb.Execute strSQL, dbFailOnError

Next i
Else
MsgBox "There were no files found."
End If
End With
Me.List0.Requery
End Sub

I placed a couple of buttons and a list box on a form. I have the button
search for the excel files and input there location in a temp table which
populates the listbox.

One problem I am having is for it to start searching where my back end is
located. I used a const variable in Refreshlinks, but that does not seem to
work.

Also, I am placing the whole string in a text field in the table. Should I
do that or just the filename? So when I click another button to import the
data from the excel file into another temp table.

Here is the code for that imports the info and it works.

Private Sub cmdImport_Click()
DoCmd.TransferSpreadsheet acImport, , "tblPersonnelTemp",
Me.List0.Column(1), True

End Sub

Everything is working so far except. I need it to search where my backend
is located and the subfolder. I also would like just the name of the file in
the listbox not the whole filepath.

Thanks
 
Ok I got the backend location working. However, by chance could you tell me
how to use just the file name and possibly the modification date. I think If
I place these in a temp table every time they run the query then I can show
them in columns in a listbox.

Thanks for any suggestions
 
Back
Top