Is there a way of doing a macro of VBscript that you can select what folder
that contains your .xls files and save it into a variable?

explanation,

macro 1 = specify path to all .xls files
macro 2= copys cells A1- A7 from all .xls files in that folder

I've got the second macro worked out thanks to Ron de Bruins site but i cant
really get a working macro to specify a workpath easy.

Another alternative is that you could write the path inside a Cell and then
the macro claims that line, but i dont know how to do this either.

The following Declarations and Function will let you browse to a folder and
select it. The short macro at the bottom (FolderPath) will use this function
to assign the path of the folder you select to the string variable FPath.

Option Explicit
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

Sub FolderPath()
Dim FPath as String
FPath = GetFolder
End Sub

Mike F
"Tobie" wrote in message
...
Is there a way of doing a macro of VBscript that you can select what
folder
that contains your .xls files and save it into a variable?

explanation,

macro 1 = specify path to all .xls files
macro 2= copys cells A1- A7 from all .xls files in that folder

I've got the second macro worked out thanks to Ron de Bruins site but i
cant
really get a working macro to specify a workpath easy.

Another alternative is that you could write the path inside a Cell and
then
the macro claims that line, but i dont know how to do this either.

Worked out excactly the way i wanted, perfect!


"Mike Fogleman" wrote:

The following Declarations and Function will let you browse to a folder and
select it. The short macro at the bottom (FolderPath) will use this function
to assign the path of the folder you select to the string variable FPath.

Option Explicit
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

Sub FolderPath()
Dim FPath as String
FPath = GetFolder
End Sub

Mike F
"Tobie" wrote in message
...
Is there a way of doing a macro of VBscript that you can select what
folder
that contains your .xls files and save it into a variable?

explanation,

macro 1 = specify path to all .xls files
macro 2= copys cells A1- A7 from all .xls files in that folder

I've got the second macro worked out thanks to Ron de Bruins site but i
cant
really get a working macro to specify a workpath easy.

Another alternative is that you could write the path inside a Cell and
then
the macro claims that line, but i dont know how to do this either.