debug help! getopenfilename on multiple files - Excel Help & Excel Macro Help
SEARCH ENTIRE SITE LOADING..

OZGRID Excel Help & Excel Best Practices Forums

Information Helpful? Why Not Donate.

SPECIALS PAGE FOR BARGAINS | BUILD YOUR GOLF SWING | FREE CUSTOM FUNCTIONS ADD-IN


Download Active Data For Excel Demo


Go Back Excel Help & Excel Macro Help > HELP FORUMS > EXCEL HELP
HOME Register Forum Help Calendar Search For Today's Posts Mark Forums Read

Reply

debug help! getopenfilename on multiple files

ANSWERS TO SIMILAR QUESTIONS
Open Multiple Files Via GetOpenFileNameGetOpenFileName To Open Multiple FilesMultiple Files GetOpenFileNameList Files in Order of GetOpenFileName ChoiceGetOpenFileName Slow With Multiple Files



Javascript DHTML Drop Down Menu Powered by dhtml-menu-builder.com

Create Excel dashboards quickly with Plug-N-Play reports.


 
Thread Tools Search this Thread
Old October 7th, 2003
Intelligent Converters
FREE DOWNLOADS
File/Password Recovery
kspencer kspencer is offline
Member
 
I'm a Spammer:
MS Office Version:
Op System:
Assumed Experience:
Join Date: 23rd August 2003
English is 1st Language:
Posts: 38 -- Threads: 14
debug help! getopenfilename on multiple files

The idea of the following code is that the user chooses one or many files from the same folder. All files should be .xls files. This section of code is to get and remember the path and names of the files so that it can loop through them to transfer data to a master file later. The path and number of files could vary widely from one usage to the next. The default directory is changed because that is the most likely location of the target files.

The code as written doesn't work. I'm stuck. I've gotten fairly good at "regular" code (thanks to you guys!) but this is my first attempt at working with arrays.

BTW I work for a financial services company and we have very strict download restrictions. I'd appreciate it if solutions could be pasted in here rather than set up as attached files. Sorry, I know that's not always fun.

Here's my code:
VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes".
Dim TrialChosen As String ' used as error trap Dim TrialNames() As String ' array of filenames chosen Dim TrialSelectError As Integer ' used as error trap Private Sub GetTrialsFolder() ' ' Written by Kelly Spencer ' MsgBox ("Using the next screen, select the trials you wish to import." & Chr(13) & _ "Please check to be sure that the path is correct.") TrialChosen = "Yes" ChDir "X:\ACCOUNTING\Fund_Distributions\Data Download\" Do TrialNames() = Application. GetOpenFilename _ (FileFilter:="Microsoft Excel Files (*.xls), *.xls", _ Title:="Please select the trials you wish to import.", _ MultiSelect:=True) If TrialNames(1) = "False" Then TrialSelectError = MsgBox(Prompt:="You must choose at least one trial to be " _ & "imported." & Chr(13) & "If you do not the macro will be cancelled." _ & Chr(13) & "Do you wish to try again?", Buttons:=vbYesNo) If TrialSelectError = vbNo Then TrialChosen = "No" Exit Sub End If End If Loop Until TrialNames(1) <> "False" ' End Sub


Help!!
Kelly.

PS The "TrialChosen" variable is used by the calling procedure to determine whether it should continue or quit (ie if files were or were not chosen).

Last edited by royUK : August 4th, 2005 at 04:50.
Print [Post / Thread] Reply With Quote
Old October 7th, 2003
Intelligent Converters
FREE DOWNLOADS
File/Password Recovery
Neale Neale is offline
Established Member
 
I'm a Spammer:
MS Office Version: 2003 English
Op System: Windows XP
Assumed Experience: Expert
Join Date: 24th January 2003
English is 1st Language: Yes
Location: Perth, Australia
Posts: 602 -- Threads: 25
I just did quick test and the GetOpenFilename works ok with the Array when you select something but if you cancel it errors out. Maybe an

On Error Resume Next

is called for

I don't know what the Cancel button returns to the variable.

Check the logic of your Do Loop Until using the "False" - maybe its wrong. Is it supposed to be False without the ""?

HTH
Print [Post / Thread] Reply With Quote
Old October 7th, 2003
Intelligent Converters
FREE DOWNLOADS
File/Password Recovery
kspencer kspencer is offline
Member
 
I'm a Spammer:
MS Office Version:
Op System:
Assumed Experience:
Join Date: 23rd August 2003
English is 1st Language:
Posts: 38 -- Threads: 14
Huh. Did you use the exact same code?

I just tried to run it again and got the same error:

Run-time error '13':
Type mismatch

I selected three files from a folder and hit open in the dialog box. The debug highlighted the GetOpenFileName line.

Argh. I don't get why it would work for you and not for me...

Help!!
Kelly.
Print [Post / Thread] Reply With Quote
Old October 7th, 2003
Pesky Weasel's Avatar
Pesky Weasel Pesky Weasel is offline
OZMVP
 
I'm a Spammer:
MS Office Version: 2002(XP)
Op System: Windows XP
Assumed Experience: Above Average (Know VBA & All of Excel)
Join Date: 7th February 2003
English is 1st Language: Yes
Location: Kewarra Beach, Cairns - TNQ
Posts: 1,057 -- Threads: 60
Kelly,

I am no expert with arrays, having just started - so I have only ammended the code and its probably a bit messy - but I think it does whatyou want now.

VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes".
Option Explicit Dim TrialChosen As String ' used As error trap Dim TrialNames() As String ' array of filenames chosen Dim TrialSelectError As Integer ' used As error trap Private Sub GetTrialsFolder() Dim filen As Variant Dim i As Integer Dim x As Integer ' Written by Kelly Spencer ' MsgBox ("Using the next screen, select the trials you wish to import." & Chr(13) & _ "Please check to be sure that the path is correct.") TrialChosen = "Yes" ChDir "X:ACCOUNTINGFund_DistributionsData Download" For Each filen In Application. GetOpenFilename _ (FileFilter:="Microsoft Excel Files (*.xls), *.xls", _ Title:="Please select the trials you wish to import.", _ MultiSelect:=True) i = i + 1 Redim Preserve TrialNames(1 To i) TrialNames(i) = filen Next filen If TrialNames(1) = "False" Then TrialSelectError = MsgBox(Prompt:="You must choose at least one trial to be " _ & "imported." & Chr(13) & "If you do not the macro will be cancelled." _ & Chr(13) & "Do you wish to try again?", Buttons:=vbYesNo) If TrialSelectError = vbNo Then TrialChosen = "No" Exit Sub End If End If For x = 1 To UBound(TrialNames) MsgBox TrialNames(x) Next x End Sub

Regards
Weasel

ps: you have to fix the file path as the board doesnt like backslashes
__________________
Kind Regards
Pesky Weasel
"I have a plan so cunning, you could put a tail on it and call it a Weasel." EB
Eagles may soar, but Weasels don't get sucked into jet engines.
Templates and Calculators
The Way of the Weasel
Download Ivan &amp; Colo's HTML Maker Here
101 Excel Hacks - Great New Book
Huge Savings on Excel Training
Print [Post / Thread] Reply With Quote
Old October 7th, 2003
Intelligent Converters
FREE DOWNLOADS
File/Password Recovery
kspencer kspencer is offline
Member
 
I'm a Spammer:
MS Office Version:
Op System:
Assumed Experience:
Join Date: 23rd August 2003
English is 1st Language:
Posts: 38 -- Threads: 14
As far as the error traps etc...I have successfully used this exact same code in other apps to open single files. The only difference is that the variable in the TrialNames spot is not specified or used as an array.

So I know the " " need to be there on "False". GetOpenFileName returns the string False if the user hits cancel. I assumed that this would be written to the array position 1 so TrialNames(1) would be the correct place to look for it. Perhaps that is what is incorrect? Remember I've never used array variables before...

Thanks,
Kelly.
Print [Post / Thread] Reply With Quote
Old October 7th, 2003
Andy Pope's Avatar
Andy Pope Andy Pope is offline
OzMVP (Roobarb)
 
I'm a Spammer:
MS Office Version: 2000/2003/2007
Op System: Vista Business
Assumed Experience: Ever growing
Join Date: 7th March 2003
English is 1st Language: Yes
Location: Essex, England
Posts: 11,319 -- Threads: 32
Hi Kelly,

Try this, if files are selected the variant array is automatically populated,
If the user cancels the dialog then the IsArray will catch it.
VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes".
Dim vntTrialNames As Variant ' array of filenames chosen Dim TrialChosen As String ' used As error trap Dim TrialSelectError As Integer ' used As error trap Private Sub GetTrialsFolder() ' ' Written by Kelly Spencer ' Dim intIndex As Integer MsgBox ("Using the next screen, select the trials you wish to import." & Chr(13) & _ "Please check to be sure that the path is correct.") TrialChosen = "Yes" ChDir "X:\ACCOUNTING\Fund_Distributions\Data Download\" Do vntTrialNames = Application. GetOpenFilename _ (FileFilter:="Microsoft Excel Files (*.xls), *.xls", _ Title:="Please select the trials you wish to import.", _ MultiSelect:=True) If Not IsArray(vntTrialNames) Then TrialSelectError = MsgBox(Prompt:="You must choose at least one trial to be " _ & "imported." & Chr(13) & "If you do not the macro will be cancelled." _ & Chr(13) & "Do you wish to try again?", Buttons:=vbYesNo) If TrialSelectError = vbNo Then TrialChosen = "No" Exit Sub End If Else TrialSelectError = vbNo For intIndex = LBound(vntTrialNames) To UBound(vntTrialNames) MsgBox "Choice " & intIndex & " was " & vntTrialNames(intIndex) Next End If Loop While TrialSelectError = vbYes ' End Sub

Cheers
Andy
Print [Post / Thread] Reply With Quote
Old October 7th, 2003
Intelligent Converters
FREE DOWNLOADS
File/Password Recovery
kspencer kspencer is offline
Member
 
I'm a Spammer:
MS Office Version:
Op System:
Assumed Experience:
Join Date: 23rd August 2003
English is 1st Language:
Posts: 38 -- Threads: 14
Weasel's works, but only if the user doesn't hit cancel.

Now I'm going to try Andy's.

You guys are awesome!

Kelly.
Print [Post / Thread] Reply With Quote
Old October 7th, 2003
Intelligent Converters
FREE DOWNLOADS
File/Password Recovery
kspencer kspencer is offline
Member
 
I'm a Spammer:
MS Office Version:
Op System:
Assumed Experience:
Join Date: 23rd August 2003
English is 1st Language:
Posts: 38 -- Threads: 14
Andy's solution works great :-)

Now a question: I was trying not to spec it as a variant array because I've read that they are not ideal (memory or speed wise? can't remember). I know I'm getting string data back from my GetOpenFileName, so why do I need a variant array rather than a string array?

Kelly.
Print [Post / Thread] Reply With Quote
Old October 7th, 2003
Richie(UK)'s Avatar
Richie(UK) Richie(UK) is offline
OzMVP
 
I'm a Spammer:
MS Office Version: 2003
Op System: Windows XP
Assumed Experience: Still learning!
Join Date: 25th January 2003
English is 1st Language:
Location: UK
Posts: 2,464 -- Threads: 23
Hi Kelly,

Rather than an explicit array variable, try using a Variant to contain the array of entries and checking these for False.

Alternatively, use TyeName to see if a Boolean value was returned (ie the False generated by Cancel).

Like this:
VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes".
Option Base 1 Private Sub Test() Dim varNames As Variant, i As Integer varNames = Application. GetOpenFilename(MultiSelect:=True) If TypeName(varNames) = "Boolean" Then MsgBox "You cancelled!" Exit Sub End If For i = LBound(varNames) To UBound(varNames) MsgBox varNames(i) Next i End Sub

HTH

EDIT : Sorry - work distractions! Well done Andy
__________________


Want to be a solution provider rather than a question asker? Check out the Excel books here
There's more good stuff here too :XL Download Package and XL/VBA Tips & Tricks
Posting guidelines : Help via PM
Print [Post / Thread] Reply With Quote
Old October 7th, 2003
Pesky Weasel's Avatar
Pesky Weasel Pesky Weasel is offline
OZMVP
 
I'm a Spammer:
MS Office Version: 2002(XP)
Op System: Windows XP
Assumed Experience: Above Average (Know VBA & All of Excel)
Join Date: 7th February 2003
English is 1st Language: Yes
Location: Kewarra Beach, Cairns - TNQ
Posts: 1,057 -- Threads: 60
Quote:
Originally posted by kspencer
Andy's solution works great :-)

Now a question: I was trying not to spec it as a variant array because I've read that they are not ideal (memory or speed wise? can't remember). I know I'm getting string data back from my GetOpenFileName, so why do I need a variant array rather than a string array?

Kelly.
I believe that the For...Next loop will not accept a string, thats why I went for a variant.

regards
Weasel

ps: Sorry about the error catching I was caught up in getting first part to work - nice one Andy
__________________
Kind Regards
Pesky Weasel
"I have a plan so cunning, you could put a tail on it and call it a Weasel." EB
Eagles may soar, but Weasels don't get sucked into jet engines.
Templates and Calculators
The Way of the Weasel
Download Ivan &amp; Colo's HTML Maker Here
101 Excel Hacks - Great New Book
Huge Savings on Excel Training
Print [Post / Thread] Reply With Quote
Reply Lifetime Upgrade To Ad Free Styles

   « PREVIOUS [Solved] VBA: Custom Number Format Based on Cell Reference || [Solved] Array Formulas NEXT »
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT +9. The time now is 20:27.


Powered by vBulletin Version 3.5.4
Copyright ©2000 - 2010, Jelsoft Enterprises Ltd.
Ozgrid is Not Associated With Microsoft. Ozgrid Retains the Rights to ALL Posts and Threads