macros
 

I am trying to set up a macro that will allow me to open up an excel template
from a folder from a tree of folders that will automatically prompt me for a
file path to fill in data from the file into the desired columbs and rows
necessary after i open the template so its just one open for me and then a
prompt. If anyone knows of a way to do this i'd appreciate whatever codes you
think might work the best. Also in this template there are multiple
sheets/workbook.

Getting a file name...
 

To getting a file name, run the Sub

TryGettingFileName

HTH,
--
Gary Brown

If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".


'/=============================================/
Sub TryGettingFileName()

MsgBox GetFileName

End Sub
'/=============================================/
Function GetFileName()
'select a single file and return name
'
' created by Gary L. Brown
' Kinneson Consulting
' 02/28/2001
'
'
On Error GoTo Err_GetFileName

Dim iFilterIndex As Integer, strTitle As String
Dim strFilter As String, StrDialogBoxTitle As String
Dim varFileName As Variant

strTitle = "Kinneson.com: "
'List of file filters allowed
strFilter = "Excel Files (*.xl?),*.xl?," & _
"Text Files (*.txt; *.prn; *.asc;*.ini)," & _
"*.txt;*.prn;*.asc;*.ini," & _
"Comma Separated Files (*.csv),*.csv," & _
"All Files (*.*), *.*"

'Display Txt Files as default
' - 2nd on StrFilter list above
iFilterIndex = 4

'Set the dialog box caption
StrDialogBoxTitle = strTitle & "Select a File..."

'Get the File Name
varFileName = _
Application.GetOpenFilename(fileFilter:=strFilter, _
FilterIndex:=iFilterIndex, Title:=StrDialogBoxTitle)

'exit if dialog box canceled
If varFileName = False Then
MsgBox "No File was selected.", vbInformation + vbOKOnly, _
"Open File Procedure has been canceled..."
GetFileName = ""
Exit Function
End If

GetFileName = varFileName

Exit_GetFileName:
Exit Function

Err_GetFileName:
MsgBox "Error: " & Err & " - " & Err.Description
Resume Exit_GetFileName

End Function

'/=============================================/

To get a folder name
 

To get a folder name, run the Sub

TryGettingDirectory

HTH,
--
Gary Brown

If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".

'created using John Walkenbach's "Microsoft Excel 2000 Power
' Programming with VBA" example as a basic starting point
'================================================= =====
'32-bit API declarations
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

'================================================= =====
'Public Type BROWSEINFO
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
'================================================
Sub TryGettingDirectory()
MsgBox GetDirectory("Try Me")
End Sub

'================================================
Private Function GetDirectory(Optional Msg) As String
Dim bInfo As BROWSEINFO
Dim Path As String
Dim r As Long, x As Long, pos As Integer

' Root folder = Desktop
bInfo.pidlRoot = 0&

' Title in the dialog
If IsMissing(Msg) Then
bInfo.lpszTitle = "Select a folder."
Else
bInfo.lpszTitle = Msg
End If

' Type of directory to return
bInfo.ulFlags = &H1

' Display the dialog
x = SHBrowseForFolder(bInfo)

' Parse the result
Path = Space$(512)
r = SHGetPathFromIDList(ByVal x, ByVal Path)
If r Then
pos = InStr(Path, Chr$(0))
GetDirectory = Left(Path, pos - 1)
Else
GetDirectory = ""
End If
End Function
'================================================= ==