Opening a folder

[Daminc]

Within my code I've got:

Please Login or Register  to view this content.

which saves all right however, in my next line, I wish to open up the folder in which the workbook was saved but I can't seem to find a way to do this.

Later on I plan to have the file path as a variable but at the moment I'm working with just this one.

Cheers

[Bob Phillips]

I don't know what you mean by open up a folder, but you have the folder path
so just store it in a variable

sPath = "P:\VBA training\Excel templates for Network stats\"
Activeworkbook.SaveAs Filename:= sPath & wrkbname

and you can still use sPath


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Daminc" <[email protected]> wrote in
message news:[email protected]...
>
> Within my code I've got:
>
> Code:
> --------------------
> ActiveWorkbook.SaveAs Filename:="P:\VBA training\Excel templates for
Network stats\" & wrkbkname
> --------------------
>
> which saves all right however, in my next line, I wish to open up the
> folder in which the workbook was saved but I can't seem to find a way
> to do this.
>
> Later on I plan to have the file path as a variable but at the moment
> I'm working with just this one.
>
> Cheers
>
>
> --
> Daminc
> ------------------------------------------------------------------------
> Daminc's Profile:
http://www.excelforum.com/member.php...o&userid=27074
> View this thread: http://www.excelforum.com/showthread...hreadid=503265
>

[Daminc]

I've come across:

http://www.mentalis.org/apilist/SHBr...rFolder.shtml#

which claims to do what I want.

Right now I'm reading a printed version of it trying to understand what on earth the code is trying to do before I try and use it (currently reading about the 'Type Statement').

The idea of storing the user path as a varible is a good one though and I'll impliment that asap

[Daminc]

I can't figure out what the code does

When I put it in a new module it doesn't seem to do anything.

I don't know what hWnd is supposed to be or most of the other stuff.

Looks like I'll have to find an alternative option.

[Bob Phillips]

Are u just trying to select a folder to get the folder name? If so, and you
have Excel XP/2002 you can use

With Application.FileDialog(msoFileDialogFolderPicker)
.Show


MsgBox .SelectedItems(1)


End With


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Daminc" <[email protected]> wrote in
message news:[email protected]...
>
> I can't figure out what the code does
>
> When I put it in a new module it doesn't seem to do anything.
>
> I don't know what hWnd is supposed to be or most of the other stuff.
>
> Looks like I'll have to find an alternative option.
>
>
> --
> Daminc
> ------------------------------------------------------------------------
> Daminc's Profile:
http://www.excelforum.com/member.php...o&userid=27074
> View this thread: http://www.excelforum.com/showthread...hreadid=503265
>

[Daminc]

Nah, unfortunately I'm using Excel 2000. I don't know if there's an equivalent command?

[Bob Phillips]

This works pre-2002, but it may be similar to what you already have tried.

test it with

MsgBox GetFolder
Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" _
(ByVal pidl As Long, _
ByVal pszPath As String) As Long


Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long


Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type


'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name As String = _
"Select a folder.") As String
'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long


bInfo.pidlRoot = 0& 'Root folder = Desktop


bInfo.lpszTitle = Name


bInfo.ulFlags = &H1 'Type of directory to Return
oDialog = SHBrowseForFolder(bInfo) 'display the dialog


'Parse the result
path = Space$(512)


GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then
GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
End If


End Function


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Daminc" <[email protected]> wrote in
message news:[email protected]...
>
> Nah, unfortunately I'm using Excel 2000. I don't know if there's an
> equivalent command?
>
>
> --
> Daminc
> ------------------------------------------------------------------------
> Daminc's Profile:
http://www.excelforum.com/member.php...o&userid=27074
> View this thread: http://www.excelforum.com/showthread...hreadid=503265
>

[Daminc]

I didn't get the function to work but I don't know whether it's something I've done wrong or it's excel 2000 not liking it

Here's what I've got when I tried to run it:

[Daminc]

And here is the reported error:

[Bob Phillips]

You cannot issue a VBA command from the declaratives section, it must be
within a macro. So

MsgBox GetFolder

has to be in a macro.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Daminc" <[email protected]> wrote in
message news:[email protected]...
>
> And here is the reported error:
>
>
> +-------------------------------------------------------------------+
> |Filename: excel2.gif |
> |Download: http://www.excelforum.com/attachment.php?postid=4258 |
> +-------------------------------------------------------------------+
>
> --
> Daminc
> ------------------------------------------------------------------------
> Daminc's Profile:
http://www.excelforum.com/member.php...o&userid=27074
> View this thread: http://www.excelforum.com/showthread...hreadid=503265
>

[Daminc]

I've got that partially to work. Enough to realise that wasn't what I was trying to achieve. What I was hoping for was something akin to the Save As dialog box to the person can direct the name and location of where the new excel book is saved.

Do you know if this is possible?

[Bob Phillips]

Take a look at GetSaveAsFilename in Help.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Daminc" <[email protected]> wrote in
message news:[email protected]...
>
> I've got that partially to work. Enough to realise that wasn't what I
> was trying to achieve. What I was hoping for was something akin to the
> Save As dialog box to the person can direct the name and location of
> where the new excel book is saved.
>
> Do you know if this is possible?
>
>
> +-------------------------------------------------------------------+
> |Filename: saveas_dialog_box.gif |
> |Download: http://www.excelforum.com/attachment.php?postid=4260 |
> +-------------------------------------------------------------------+
>
> --
> Daminc
> ------------------------------------------------------------------------
> Daminc's Profile:
http://www.excelforum.com/member.php...o&userid=27074
> View this thread: http://www.excelforum.com/showthread...hreadid=503265
>

[Daminc]

That was a great help:

Please Login or Register  to view this content.

This step works for now

Inch by inch it's getting there, cheers Bob