button that opens open dialogs but saves path to sheet

[cereldine]

Hi, I'm wondering if anyone has ever attempted a similar function to what i'm trying to achieve.
I have the problem of having to import a great deal of raw data to a excel file from a number of different workbooks. I have created a nice loop statement and number of subs that complete this requirement.

The procedure is driven by having the filenames and there paths located within a sheet in my workbook, i have done it this way because the filenames that i retrieve data from have there names changed in order to maintain an audit trail e.g import_q1, import_q2 etc and i didn't want users messing with hardcoded pathways.

I would like to create a button that allows the user to use the applications.dialogs(something) functionality or something similar to find the exact file that holds the information, when they close the dialog then the name is saved in the correct postion on the worksheet, once all files have been updated then the import procedure can be run.

I have done something similar that prompted a user to choose whether or not to use a certain path, if they chose vbno then the new file was treated as the correct one and the new filepath was saved on its completion. Setting up the filepaths beforehand would improve the interface and allow thecode to run without interuption.

Has anyone completed something like this before and could give me some tips, or come across a post that may point me in right direction? I've had a good look but not found anything yet! thanks

[Ivan Raiminius]

Hi Cereldine,

try this:

sub GetPath
dim pathandfilename as variant
pathandfilename=application.getsaveasfilename
if pathandfilename=false then
msgbox "User quits the dialog"
else
msgbox "The path is: " & pathandfilename
end if
end sub

this gives you the path and filename or false when user quits the
dialog.

Regards,
Ivan

[cereldine]

This is definitly something i'm going to investigate, probably change it to getopenfilename as this would make more sense to user i feel. Not quite sure why the file doesn't open/saveas but thats ok! Cheers

[Ivan Raiminius]

Hi Cereldine,

the file doesn't save, because you are only displaying the dialog, not
saving the file.

You are right with getopenfilename, I didn't read your post carefully
enough, I thought that user needs to set a new filename, not to choose
existing one. I am sorry.

Also getopenfilename will not open the file, it will display the dialog
and you have to open the file yourself (by code).

Regards,
Ivan

[cereldine]

Thanks for the explanation, it helps to find out what things are really doing for future reference. Ive used the code in the following way and for something so simple it works perfectly

Dim pathChange As Range
Dim pathandfilename As Variant

Public Sub NQAJ_change()
Range("C15").Select
Set pathChange = ActiveCell
GetPath
End Sub

Sub GetPath()
pathandfilename = Application.GetOpenFilename

If pathandfilename = False Then
MsgBox "User quits the dialog"
Else
' MsgBox "The path is: " & pathandfilename
pathChange = pathandfilename
End If

End Sub

This way i can use the getpath procedure from many places.

There may be one last improvement, i have bout +12 different excel sheets all located in one folder, my function saves the foldername as a string then the loop uses the offset(1,0) to loop through these files in turn(uses two strings - foldername &\& File_name). I'm thinking in this case it is only really neccasery to update the folder path not the individual files themselves(they will always have same name just be located in different folders for audit trail). Is it possible to trim the getopenfilename to only retrieve the folder pathway? If not don't worry this has been great help already, thanks

[Ivan Raiminius]

Hi Cereldine,

yes, it is possible to get path from full file name.

You can use this function:

Function Folder(FullFileNameIncludingPath)
Dim fs As Object
Set fs = CreateObject("scripting.filesystemobject")
Folder = fs.GetFolder(FullFileNameIncludingPath)
End Function

Regards,
Ivan

[cereldine]

Hi, im not sure if ive understood this correctly, its my understanding that you pass the function a variable containing the path and filename and the function just looks at the folder part of it? I can't seem to get it to work. ive tried the below. Thanks

Public Sub findFolder()
pathandfilename = Application.GetOpenFilename
folder (pathandfilename)
End Sub

Public Function folder(FullFileNameIncludingPath)
Dim fs As Object
Set fs = CreateObject("scripting.filesystemobject")
folder = fs.getfolder(FullFileNameIncludingPath)
End Function

[Ivan Raiminius]

Hi Cereldine,

you understood it correctly.

I have to apologize, I wrote the code from scratch and used wrong
method, please change:
folder = fs.getfolder(FullFileNameIncludingPath)

to be
folder = fs.getparentfoldername(FullFileNameIncludingPath)

The advantage of coding like this is that it also verifies, if the path
and file exist (it invokes error otherwise). Second possible attitude
is to take FullFileNameIncludingPath and look for last \ - everything
from the start till last \ is path.

One more tip, you can delete the input argument from the function and
use application.getopenfilename inside the function.

Best regards,
Ivan