Browse and extract excel file - almost there

[frispee]

Hi,

I have to extract certain cells of an excel file and update an access database. I have already found code in this form which does exactly that. The problem is, this code has the path of the file built into it. I want the ability to be able to browse and choose the file. I have tried out a piece of code that browses for a file and displays the number of files chosen. Could anyone help me on how to get the path of the file to give it as input to the extracting portion of the code?

The code which browses and gets the number of files chosen is:

Dim f    As Object 
 Set f = Application.FileDialog(3) 
 f.AllowMultiSelect = True 
 f.Show 
 MsgBox "file choosen = " & f.SelectedItems.Count

The portion of the code that uses the file path (For extracting cells) is:

Set xlw = xlx.Workbooks.Open("C:\Filename.xls", , True) ' 
Set xls = xlw.Worksheets("WorksheetName")

I am new to VBA and any help would be greatly appreciated Thank you in advance.

 

[pr2-eugin]

Hi.. Frispee.. I hope this is what you are trying to do.. i.e.

* Open the File choosing dialog.
* Select a File
* Use the selected file to manipulate.
If so, use the following..

Dim fileDump As FileDialog
Set fileDump = Application.FileDialog(msoFileDialogOpen)
fileDump.Show           [COLOR=SeaGreen]'This[/COLOR] [COLOR=SeaGreen]will POP up the File choose dialog box.[/COLOR]
Dim Yourroute,YourFile, midName As String
Yourroute = fileDump.SelectedItems(1) [COLOR=SeaGreen]'here goes the route to your document[/COLOR]
midName = StrReverse(Yourroute)
YourFile = StrReverse(Mid(midName, 1, InStr(midName, "\") - 1)) 
[COLOR=SeaGreen]' YourFile holds the [/COLOR][COLOR=SeaGreen]theFile Name of your document..
[/COLOR]

Please add "Microsoft Office 14.0" in your reference under Tools menu.

 

[frispee]

@pr2-eugin: Thanks a lot! That worked exactly as I wanted it to.