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


--
cereldine
------------------------------------------------------------------------
cereldine's Profile: http://www.excelforum.com/member.php...o&userid=32069
View this thread: http://www.excelforum.com/showthread...hreadid=536745

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

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


--
cereldine
------------------------------------------------------------------------
cereldine's Profile: http://www.excelforum.com/member.php...o&userid=32069
View this thread: http://www.excelforum.com/showthread...hreadid=536745

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

Thanks for the explanation, it helps to find out what things are reall
doing for future reference. Ive used the code in the following way an
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 exce
sheets all located in one folder, my function saves the foldername as
string then the loop uses the offset(1,0) to loop through these files i
turn(uses two strings - foldername &\& File_name). I'm thinking in thi
case it is only really neccasery to update the folder path not th
individual files themselves(they will always have same name just b
located in different folders for audit trail). Is it possible to tri
the getopenfilename to only retrieve the folder pathway? If not don'
worry this has been great help already, thank

--
cereldin
-----------------------------------------------------------------------
cereldine's Profile: http://www.excelforum.com/member.php...fo&userid=3206
View this thread: http://www.excelforum.com/showthread.php?threadid=53674

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

Hi, im not sure if ive understood this correctly, its my understandin
that you pass the function a variable containing the path and filenam
and the function just looks at the folder part of it? I can't seem t
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 Functio

--
cereldin
-----------------------------------------------------------------------
cereldine's Profile: http://www.excelforum.com/member.php...fo&userid=3206
View this thread: http://www.excelforum.com/showthread.php?threadid=53674

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