[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel VBA to open file - how to define default folder

Posted on 2011-05-10
10
Medium Priority
?
339 Views
Last Modified: 2012-05-11
Part of the solution was the following function to open the file. How and where can I set the default folder for opening and also part of the file name (for example xdfaerew*.xls)?

Function FunctionGetFileName(FullPath As String) As String

Dim StrFind As String
Dim iCount As Integer
    Do Until Left(StrFind, 1) = "\"

        iCount = iCount + 1

        StrFind = Right(FullPath, iCount)

            If iCount = Len(FullPath) Then Exit Do

    Loop

    

    FunctionGetFileName = Right(StrFind, Len(StrFind) - 1)

    

End Function

Open in new window


the function is called from the code as follows:

SourceWb = Application.GetOpenFilename 'Allows for user to select file
Workbooks.Open SourceWb 'Open source workbook
SourceWb = FunctionGetFileName(CStr(SourceWb))

Open in new window

0
Comment
Question by:BrdgBldr
  • 6
  • 4
10 Comments
 
LVL 7

Expert Comment

by:m4trix
ID: 35732093
Is this what you're looking for?

SourceWb = Application.GetOpenFilename 'Allows for user to select file
    Workbooks.Open SourceWb 'Open source workbook
    SourceWb = Mid(SourceWb, InStrRev(SourceWb, "\") + 1)

Open in new window

0
 
LVL 7

Expert Comment

by:m4trix
ID: 35732134
I should have clarified in the previous post:
If you select the file "C:\temp\myworkbook.xlsx"
SourceWb becomes "myworkbook.xlsx" after line 3.

I'm not sure what you're looking for in terms of getting the default folder... can you elaborate?
0
 
LVL 7

Expert Comment

by:m4trix
ID: 35732160
Sorry to make another post, but it just occured to me another option for line 3 is simply the following:
SourceWb = ActiveWorkbook.Name
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:BrdgBldr
ID: 35732317
Thanks.

So do you think I don't even need this function?

I would like that when the file open dialogue opens, it is opening in the folder c:\data\ee\  and only showing files for example that start with banana and are xls files so mountain*.xls
so that I could set these defaults in the code
0
 
LVL 7

Accepted Solution

by:
m4trix earned 500 total points
ID: 35732473
Ah, ok, I gotcha.

The first part is easy:
ChDir "c:\data\ee"
SourceWb = Application.GetOpenFilename("Excel Files (*.xls), *.xls")

Open in new window


That will pop up an "open file" dialog in c:\data\ee\ and show ONLY *.xls files

The second part is not as easy. There's no "out of the box" way to show only files starting with "mountain" and ending with ".xls". However, we can have the macro automatically type some letters in which will accomplish that in a roundabout way. Consider the following completed function:
 
Sub OpenFileFromDefaultDirectory()
    ChDir "c:\data\ee"
    Application.SendKeys "mountain*{RETURN}"
    SourceWb = Application.GetOpenFilename("Excel Files (*.xls), *.xls") 'Allows for user to select file
    Workbooks.Open SourceWb 'Open source workbook
End Sub

Open in new window


It should do as you ask. However, the "SendKeys" line is a little bit clunky.
0
 
LVL 7

Expert Comment

by:m4trix
ID: 35732501
PS - the above macro can ONLY be run from the main Excel window - via a button or via the macros dialog box. If you run it from the VB Editor it will just type "mountain*" wherever your cursor is on the screen :)
0
 

Author Comment

by:BrdgBldr
ID: 35732560
ok. that pretty makes it. it even works when starting out of the VBA editor ;-)

last question: if a click on cancel in the file open dialogue... how can I handle this error?

On Error Exit Sub or smthing like this?
0
 

Author Comment

by:BrdgBldr
ID: 35732624
ok... I see what you mean... with F8 in break mode it doesn't work in the VBA editor :o)
0
 
LVL 7

Assisted Solution

by:m4trix
m4trix earned 500 total points
ID: 35732706
Yeah, break mode is what I meant :)

As for the cancel, you could do something like this maybe?
Sub OpenFileFromDefaultDirectory()
    ChDir "c:\data\ee"
    Application.SendKeys "mountain*{RETURN}"
    SourceWb = Application.GetOpenFilename("Excel Files (*.xls), *.xls") 'Allows for user to select file
    If SourceWb = False Then
        MsgBox "You didn't select a file!"
    Else
        Workbooks.Open SourceWb 'Open source workbook\
    End If
End Sub

Open in new window


If you don't want any popup at all, then yes, you could Exit Sub
0
 

Author Closing Comment

by:BrdgBldr
ID: 35732956
many thanks!   :o)
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

834 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