Opening a folder

[Daminc]

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

 

[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]

I've come across:

http://www.mentalis.org/apilist/SHBrowseForFolder.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]

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]

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:


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

 

[Daminc]

And here is the reported error:


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

 

[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]

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 |
+-------------------------------------------------------------------+

 

[Bob Phillips]

Take a look at GetSaveAsFilename in Help.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Daminc]

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