Solved

# Excel VBA to open file - how to define default folder

Posted on 2011-05-10
311 Views
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


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))

0
Question by:BrdgBldr

LVL 7

Expert Comment

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)

0

LVL 7

Expert Comment

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

Sorry to make another post, but it just occured to me another option for line 3 is simply the following:
SourceWb = ActiveWorkbook.Name
0

Author Comment

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

Ah, ok, I gotcha.

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


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


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

LVL 7

Expert Comment

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

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

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

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


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

Author Closing Comment

many thanks!   :o)
0

## Featured Post

### Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.