Drop down menu to select Excel spreadsheets on a drive

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

Guest

I have a form that's importing files from excel into access. I need to create
a drop down menu linked to a cetain folder on a network drive that users can
pick from to import to access. I need it not to be static so it updates when
files are copied or deleted from the folder. How do I do this?

TIA
 
Sorry. I don't think I was clear enough in what I wanted. Currently there's a
text box in the middle of the form where they have to enter the name of the
file exactly as it appears in the folder. I'm trying to make it so it's
either a drop down menu in place of the text box or it's a browse command
button. I don't need any type of menu at the top of the form. Will this
method still work that way? I have a bit of experience in VB and various
outher programming languages, just never did any vb with access...
 
This is the original code. It's very simple

Option Compare Database

Private Sub btnImport_Click()

Dim txtFile As String
txtFile = txtFname

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblCell",
"c:\filegoeshere\" & txtFile & ".xls", True

Call CleanupDb

End Sub

Private Sub CleanupDb()

Dim db As Database
Dim rstRoll As Recordset
Dim intCounter As Integer
Set db = CurrentDb
Set rstRoll = db.OpenRecordset("tblCell", dbOpenDynaset)

intCounter = 0
Do While Not rstRoll.EOF
If IsNull(rstRoll!Roll) Then
rstRoll.Delete
intCounter = intCounter + 1
End If
rstRoll.MoveNext
Loop
Debug.Print intCounter & "Fini"

End Sub

Private Sub Command10_Click()

DoCmd.OpenTable "tblCell", acViewNormal, acReadOnly

End Sub
 
Okay, so what is the question?
If you put the example code I sent just before your transferspreadsheet, you
will have what you asked for.
 
I've been looking over this. I think a browse button would be little less
complicated. Do you know of some good reading material I can use? I'm really
rusty and the two books I'm using aren't exactly great. One's great for code
examples but very vague in general and the other only has the basics in it.
 
My favorite book is Access 2002 Desktop Developer's Handbook by Litwin, Getz,
and Gunderloy, published by Sybex. There is also a companion Access 2002
Enterprise Developer's Handbook that covers multi-user and deployment issues.

The code I sent will give you that browse capability. The API I referenced
creates an Open/Save dialog box. It is what you see when to do Open or Save
to any Microsoft application. It is really not that hard to use once you get
your head around it. Look through the comments in the API module, look at
the way I am using it, and do some experimenting with it. It took me a time
or two to really understand it, but now I use it a lot.

Good luck and post back if you have any questions.
 
Ok, Here is the code modified to work for your. It may need some tweeking to
get exactly what you want, but based on the original code, I think this will
work for you. If you have any questions, post back.

Dim varGetFileName As Variant 'Pass to Common Dialog to open workbook
Dim strDefaultDir As String 'Pass Directory to search for common dialog
Dim strfilter As String 'Limit common dialog search to excel workbooks
Dim lngFlags As Long 'Hide readonly check box on common dialog
Dim strFileName As String 'Initial File Name to Display

'Set up Default Directory
strDefaultDir = "c:\filegoeshere\"
'Setup Default File Name
strFileName = txtFname
'Flags Hides the Read Only Check and Only allow existing files
lngFlags = ahtOFN_HIDEREADONLY Or ahtOFN_FILEMUSTEXIST
'Set filter to show only Excel spreadsheets
strfilter = ahtAddFilterItem(strfilter, "Excel Files (*.xls)")
'Call the Open File Dialog
varGetFileName = ahtCommonFileOpenSave( _
OpenFile:=True, _
InitialDir:=strDefaultDir, _
Filter:=strfilter, _
FileName:=strFileName, _
Flags:=lngFlags, _
DialogTitle:="Import Adjusted Actuals") 'Change the text here
Me.Repaint 'May not need this, I did it because stuff was left on
screen
If varGetFileName = "" Then 'User Clicked CANCEL
Exit Sub
Else
'Import the Spreadsheet
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"tblCell", varGetFileName, True
Call CleanupDB
End If
 
Create a list box with the ROWSOURCE= "file type or wildcard", ROWSOURCETYPE=
7-files. This will list all file types you specify. Then you can add
buttons to handle the selection on the list box. If you want to see a screen
shot I can take one for you and send it.

Let me know. I use it to list tables, select from the list, view inside a
grid control and delete the file if I choose to. The list is always updated
and has never failed to work.

You can email me : (e-mail address removed) Use "FROM MS FORUM" on the subject line
when you send me something.
 
Back
Top