Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
opening file from ftp folder | Excel Discussion (Misc queries) | |||
when opening folder | New Users to Excel | |||
Opening Workbooks in a Folder One by One | Excel Programming | |||
"Folder Select" Dialogue - Opening multiple files from selected folder | Excel Programming | |||
opening each file in a folder | Excel Programming |