Solved

Access VBA Browse Function

Posted on 2011-09-08
18
235 Views
Last Modified: 2012-05-12
I have used this code hundreds of times -- for some reason when I browse no files appear for selection. I verified that the folder contains spreadsheets and other files. Any ideas?

Thanks.
Public Function BrowseForFile()
    
    Dim fDialog As Office.FileDialog
    Dim varFile As Variant
    Dim strLinkToFile As String

    strLinkToFile = ""

   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   With fDialog
      .AllowMultiSelect = False
      .Title = "Browse for Spreadsheet"
      .Filters.Clear
      .Filters.Add "Spreadsheets", "*.xls"
      .Filters.Add "All Files", "*.*"
      If .Show = True Then
         For Each varFile In .SelectedItems
            strLinkToFile = varFile
            LinkExcelSpreadsheet (strLinkToFile)
         Next
      Else
         Exit Function
      End If
   End With
   
End Function

Open in new window

0
Comment
Question by:Ei0914
  • 6
  • 6
  • 3
  • +2
18 Comments
 

Author Comment

by:Ei0914
ID: 36503784
Seems like it wants me to pick a folder - not a file.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36503827
It works perfectly for me.

However, it DOES filter things down to only .XLS files.

If this is not your intent, then remove this line:

>>>        .Filters.Add "Spreadsheets", "*.xls"
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36503832
try doing a compact and repair
from VBA window do a DEBUG > Compile
correct any errors raised

your code looks ok
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 77

Expert Comment

by:peter57r
ID: 36503847
Works fine here.

I suggest you copy the code to a new module and delete it from its current module and then compile and try again.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36503887
Just a thought...

Are your spreadsheets .xls files or .xlsx ?

Again, your code flters to exclude files other than .xls files.

If you don't have any .xls files, your code will cause the browse window to display exactly what you described (folders only).
0
 

Author Comment

by:Ei0914
ID: 36503926
I did as suggested above. Still getting it --

See attached. It's wacky.
BrowseError.doc
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36503972
What happens if you manually remove the "Temp" from the filename box in the file dialog?

(I'm not sure where the code you posted would be getting 'Temp')
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36503987
are you sure you are on the same path as the one which shows the .xls file?

click on the Look In drop down to verify..

also try to browse other directories..
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36504048
I'll agree with @mbizup.
How and why you are getting TEMP in the filename box is what's queering it up.
Manually clear that, and move up a level / down a level and you should be good.

But now, where is TEMP coming from?
It's not from the code
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36504064
Explaining my comment at http:#a36503972... according to the picture you posted, it is looking for Temp.xls, and from the lower image it is not there.

If you remove the 'Temp' from the filename box, it should show all .xls files.

If you manually remove 'Temp' from the filename box, you'll have to click outside of the filename box for that change to be reflected in the filtered files.
0
 

Author Comment

by:Ei0914
ID: 36504232
That did it! Where is that "Temp" coming from???
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36504296
<Where is that "Temp" coming from??? >

That's the mystery!

It does not look like it is coming from the code you posted.

Is the code in your original post *exactly* (a copy/paste of) what you have in this part of your database?

If you post your database (remove any sensitive data), we can take a look and see if it might be coming from somewhere else in your database...
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36504313
Or just a database file with just the form, code, etc  necessary to illustrate this issue.
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 36504377
As a stopgap --since we don't know where TEMP is coming from -- you could force it to not have an initial value

Public Function BrowseForFile()
   
    Dim fDialog As Office.FileDialog
    Dim varFile As Variant
    Dim strLinkToFile As String

    strLinkToFile = ""

   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   With fDialog
      .AllowMultiSelect = False
      .Title = "Browse for Spreadsheet"
      .InitialFileName =""
      .Filters.Clear
      .Filters.Add "Spreadsheets", "*.xls"
      .Filters.Add "All Files", "*.*"
      If .Show = True Then
         For Each varFile In .SelectedItems
            strLinkToFile = varFile
            LinkExcelSpreadsheet (strLinkToFile)
         Next
      Else
         Exit Function
      End If
   End With
   
End Function

0
 

Author Comment

by:Ei0914
ID: 36504387
Code is exact. The only thing named Temp is a temp table. Why that'd make a difference is odd.

I haven't tied the code to any form yet -- just working in a module right now.

I searched for "TEMP" in the code, and it's nowhere to be found.

I wonder if it's some Windows thing??
0
 

Author Comment

by:Ei0914
ID: 36504453
This did the trick!!!


--->>>        .InitialFileName = ""
0
 

Author Closing Comment

by:Ei0914
ID: 36504473
Thanks everyone!!! Learned something new.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36504500
It is weird that it puts TEMP in there unless you forced it to have nothing.
And that this is the only instance where the code does so.

But if you got it going, great

Going forward, I'd maybe change this line
.Filters.Add "Spreadsheets", "*.xls"

to

.Filters.Add "Spreadsheets", "*.xls*"

That way you cover xls, xlsm, and xlsx files
Sooner or later those are going to start showing up in your operation!
:)
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question