Opening a folder
 

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

Opening a folder
 

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" wrote in
message ...

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

Opening a folder
 

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
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=503265

Opening a folder
 

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

--
Damin
-----------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...fo&userid=2707
View this thread: http://www.excelforum.com/showthread.php?threadid=50326

Opening a folder
 

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" wrote in
message ...

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

Opening a folder
 

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

Opening a folder
 

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" wrote in
message ...

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

Opening a folder
 

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 :confused:

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


+-------------------------------------------------------------------+
|Filename: excel1.gif |
|Download: http://www.excelforum.com/attachment.php?postid=4257 |
+-------------------------------------------------------------------+

--
Daminc
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=503265

Opening a folder
 

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

Opening a folder
 

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" wrote in
message ...

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

Opening a folder
 

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

Opening a folder
 

Take a look at GetSaveAsFilename in Help.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Daminc" wrote in
message ...

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

Opening a folder
 

That was a great help:


Code:
--------------------
Case 2
Set wrkbkUrl = Workbooks.Open(Filename:="P:\VBA training\Excel templates for Network stats\2 Networks.xls")
Sheets(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sept", "Oct", "Nov", "Dec", "TempAnalysis", "Yearly summary")).Select
Cells.Replace What:="1st Network", Replacement:=strNetwork1, LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False
Cells.Replace What:="2nd Network", Replacement:=strNetwork2, LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False
savewrkbkname = Application.GetSaveAsFilename(wrkbkname, fileFilter:="Excel Files (*.xls), *.xls")

ActiveWorkbook.SaveAs Filename:=savewrkbkname
--------------------


This step works for now :)

Inch by inch it's getting there, cheers Bob


--
Daminc
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=503265