BrdgBldr
asked on
Excel VBA to open file - how to define default folder
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)?
the function is called from the code as follows:
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))
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?
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?
Sorry to make another post, but it just occured to me another option for line 3 is simply the following:
SourceWb = ActiveWorkbook.Name
SourceWb = ActiveWorkbook.Name
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 :)
ASKER
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?
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?
ASKER
ok... I see what you mean... with F8 in break mode it doesn't work in the VBA editor :o)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
many thanks! :o)
Open in new window