Flling an array
 

I need to fill an array with all (variabel) excel files in a specific
folder.
Example: Folder = d:\temp contains file1.xls, file2.xls etc
In stead of typing array("file1.xls", file2.xls", etc) the array must be
filled with the VBA code.
How can I do this.

Kind regards
Adri

Flling an array
 

Hi Adri

Use Getopenfilename to do this

Sub testing()
Dim FName As Variant
Dim N As Long
FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", _
MultiSelect:=True)

If IsArray(FName) Then
For N = LBound(FName) To UBound(FName)
Workbooks.Open (FName(N))
Next
End If
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Adri" wrote in message ...
I need to fill an array with all (variabel) excel files in a specific
folder.
Example: Folder = d:\temp contains file1.xls, file2.xls etc
In stead of typing array("file1.xls", file2.xls", etc) the array must be
filled with the VBA code.
How can I do this.

Kind regards
Adri

Flling an array
 

This function will return an array containing all the XLS files from a given folder

Function GetXLSFiles(ByVal Folder As String) As Varian
Set fso = CreateObject("Scripting.FileSystemObject"
a = "
For Each file In fso.getfolder(Folder).File
If fso.getextensionname(file) = "xls" The
a = a & file.Name & ",
End I
Nex
Set fso = Nothin
a = Left(a, Len(a) - 1
GetXLSFiles = Split(a, ","
End Functio

CA The array that is returned use option base 0 --irrecpective of the global setting. You might need to redimension it to comply with the global setting.

Flling an array
 

Hi Ron,
Thans you for answering but I don't understand how I can use it solves my
problem.
In my code I have this line:

myWorkbooks = Array("file1.xls", "file2.xls", "file4.xls")

How can Array automaticly be filled with all files from folder D:\temp
(preferred not opening all files first)?

Kind regards, Adri


Ron de Bruin wrote:
| Hi Adri
|
| Use Getopenfilename to do this
|
| Sub testing()
| Dim FName As Variant
| Dim N As Long
| FName = Application.GetOpenFilename(filefilter:="Excel Files
| (*.xls), *.xls", _ MultiSelect:=True)
|
| If IsArray(FName) Then
| For N = LBound(FName) To UBound(FName)
| Workbooks.Open (FName(N))
| Next
| End If
| End Sub
|
|
|
|
| "Adri" wrote in message
| ...
|| I need to fill an array with all (variabel) excel files in a specific
|| folder.
|| Example: Folder = d:\temp contains file1.xls, file2.xls etc
|| In stead of typing array("file1.xls", file2.xls", etc) the array
|| must be filled with the VBA code.
|| How can I do this.
||
|| Kind regards
|| Adri

Flling an array
 

Hi Adri

You can select the files you want with the CTRL key down
If you use CTRL-A in the dialog you select all the files

Fname is the Array of the selected files

You can loop through the files in the Array with this
For N = LBound(FName) To UBound(FName)

I use open the files as a example but you can do what you want.

What do you want to do with the files in the Array?
Maybe we can give you a better answer then


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Adri" wrote in message ...
Hi Ron,
Thans you for answering but I don't understand how I can use it solves my
problem.
In my code I have this line:

myWorkbooks = Array("file1.xls", "file2.xls", "file4.xls")

How can Array automaticly be filled with all files from folder D:\temp
(preferred not opening all files first)?

Kind regards, Adri


Ron de Bruin wrote:
| Hi Adri
|
| Use Getopenfilename to do this
|
| Sub testing()
| Dim FName As Variant
| Dim N As Long
| FName = Application.GetOpenFilename(filefilter:="Excel Files
| (*.xls), *.xls", _ MultiSelect:=True)
|
| If IsArray(FName) Then
| For N = LBound(FName) To UBound(FName)
| Workbooks.Open (FName(N))
| Next
| End If
| End Sub
|
|
|
|
| "Adri" wrote in message
| ...
|| I need to fill an array with all (variabel) excel files in a specific
|| folder.
|| Example: Folder = d:\temp contains file1.xls, file2.xls etc
|| In stead of typing array("file1.xls", file2.xls", etc) the array
|| must be filled with the VBA code.
|| How can I do this.
||
|| Kind regards
|| Adri

Flling an array
 

Ron de Bruin wrote:
| Hi Adri
|
| You can select the files you want with the CTRL key down
| If you use CTRL-A in the dialog you select all the files
|
| Fname is the Array of the selected files
|
| You can loop through the files in the Array with this
| For N = LBound(FName) To UBound(FName)
|
| I use open the files as a example but you can do what you want.
|
| What do you want to do with the files in the Array?
| Maybe we can give you a better answer then
|
Hi Ron,
I have this code (from the internet). It copies the contence of the seprate
files to one workbook .
To prevent a lot of typing (the file are at a network server with long
names) I want to use the macro to fill the array.
I hope you understand the problem now.


Option Explicit
Sub GetData()

Dim myWorkbooks As Variant
Dim myFolder As String
Dim testStr As String
Dim newWkbk As Workbook
Dim tempWkbk As Workbook
Dim wCtr As Long

myFolder = "D:\Temp"
If Right(myFolder, 1) < "\" Then
myFolder = myFolder & "\"
End If

Set newWkbk = Workbooks.Add(1)
ActiveSheet.Name = "DummyToDelete"

myWorkbooks = Array("file1.xls", "file2.xls", "file4.xls")

For wCtr = LBound(myWorkbooks) To UBound(myWorkbooks)
testStr = ""
On Error Resume Next
testStr = Dir(myFolder & myWorkbooks(wCtr))
On Error GoTo 0
If testStr = "" Then
MsgBox myWorkbooks(wCtr) & " Is missing!" & vbLf & _
"Processing stopped"
Exit Sub
End If

Set tempWkbk = Workbooks.Open(Filename:=myFolder &
myWorkbooks(wCtr), _
ReadOnly:=True)

tempWkbk.Worksheets(1).Copy _
after:=newWkbk.Worksheets(newWkbk.Worksheets.Count )

tempWkbk.Close savechanges:=False

Next wCtr

Application.DisplayAlerts = False
newWkbk.Worksheets("DummyToDelete").Delete
Application.DisplayAlerts = True

Call RenSheets
End Sub

Flling an array
 

Hi Adri

Look at this examples first
http://www.rondebruin.nl/copy3.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Adri" wrote in message ...
Ron de Bruin wrote:
| Hi Adri
|
| You can select the files you want with the CTRL key down
| If you use CTRL-A in the dialog you select all the files
|
| Fname is the Array of the selected files
|
| You can loop through the files in the Array with this
| For N = LBound(FName) To UBound(FName)
|
| I use open the files as a example but you can do what you want.
|
| What do you want to do with the files in the Array?
| Maybe we can give you a better answer then
|
Hi Ron,
I have this code (from the internet). It copies the contence of the seprate
files to one workbook .
To prevent a lot of typing (the file are at a network server with long
names) I want to use the macro to fill the array.
I hope you understand the problem now.


Option Explicit
Sub GetData()

Dim myWorkbooks As Variant
Dim myFolder As String
Dim testStr As String
Dim newWkbk As Workbook
Dim tempWkbk As Workbook
Dim wCtr As Long

myFolder = "D:\Temp"
If Right(myFolder, 1) < "\" Then
myFolder = myFolder & "\"
End If

Set newWkbk = Workbooks.Add(1)
ActiveSheet.Name = "DummyToDelete"

myWorkbooks = Array("file1.xls", "file2.xls", "file4.xls")

For wCtr = LBound(myWorkbooks) To UBound(myWorkbooks)
testStr = ""
On Error Resume Next
testStr = Dir(myFolder & myWorkbooks(wCtr))
On Error GoTo 0
If testStr = "" Then
MsgBox myWorkbooks(wCtr) & " Is missing!" & vbLf & _
"Processing stopped"
Exit Sub
End If

Set tempWkbk = Workbooks.Open(Filename:=myFolder &
myWorkbooks(wCtr), _
ReadOnly:=True)

tempWkbk.Worksheets(1).Copy _
after:=newWkbk.Worksheets(newWkbk.Worksheets.Count )

tempWkbk.Close savechanges:=False

Next wCtr

Application.DisplayAlerts = False
newWkbk.Worksheets("DummyToDelete").Delete
Application.DisplayAlerts = True

Call RenSheets
End Sub

Flling an array
 

Ron, thank you very much
TestFile6() wil do the job for me.
Lats little request. How can the sheetnames get a name without .xls
Adri


Ron de Bruin wrote:
| Hi Adri
|
| Look at this examples first
| http://www.rondebruin.nl/copy3.htm
|
|
|
| "Adri" wrote in message
| ...
|| Ron de Bruin wrote:
||| Hi Adri
|||
||| You can select the files you want with the CTRL key down
||| If you use CTRL-A in the dialog you select all the files
|||
||| Fname is the Array of the selected files
|||
||| You can loop through the files in the Array with this
||| For N = LBound(FName) To UBound(FName)
|||
||| I use open the files as a example but you can do what you want.
|||
||| What do you want to do with the files in the Array?
||| Maybe we can give you a better answer then
|||
|| Hi Ron,
|| I have this code (from the internet). It copies the contence of the
|| seprate files to one workbook .
|| To prevent a lot of typing (the file are at a network server with
|| long names) I want to use the macro to fill the array.
|| I hope you understand the problem now.
||
||
|| Option Explicit
|| Sub GetData()
||
|| Dim myWorkbooks As Variant
|| Dim myFolder As String
|| Dim testStr As String
|| Dim newWkbk As Workbook
|| Dim tempWkbk As Workbook
|| Dim wCtr As Long
||
|| myFolder = "D:\Temp"
|| If Right(myFolder, 1) < "\" Then
|| myFolder = myFolder & "\"
|| End If
||
|| Set newWkbk = Workbooks.Add(1)
|| ActiveSheet.Name = "DummyToDelete"
||
|| myWorkbooks = Array("file1.xls", "file2.xls", "file4.xls")
||
|| For wCtr = LBound(myWorkbooks) To UBound(myWorkbooks)
|| testStr = ""
|| On Error Resume Next
|| testStr = Dir(myFolder & myWorkbooks(wCtr))
|| On Error GoTo 0
|| If testStr = "" Then
|| MsgBox myWorkbooks(wCtr) & " Is missing!" & vbLf & _
|| "Processing stopped"
|| Exit Sub
|| End If
||
|| Set tempWkbk = Workbooks.Open(Filename:=myFolder &
|| myWorkbooks(wCtr), _
|| ReadOnly:=True)
||
|| tempWkbk.Worksheets(1).Copy _
|| after:=newWkbk.Worksheets(newWkbk.Worksheets.Count )
||
|| tempWkbk.Close savechanges:=False
||
|| Next wCtr
||
|| Application.DisplayAlerts = False
|| newWkbk.Worksheets("DummyToDelete").Delete
|| Application.DisplayAlerts = True
||
|| Call RenSheets
|| End Sub

Flling an array
 

Try this

ActiveSheet.Name = Left(mybook.Name, Len(mybook.Name) - 4)


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Adri" wrote in message ...
Ron, thank you very much
TestFile6() wil do the job for me.
Lats little request. How can the sheetnames get a name without .xls
Adri


Ron de Bruin wrote:
| Hi Adri
|
| Look at this examples first
| http://www.rondebruin.nl/copy3.htm
|
|
|
| "Adri" wrote in message
| ...
|| Ron de Bruin wrote:
||| Hi Adri
|||
||| You can select the files you want with the CTRL key down
||| If you use CTRL-A in the dialog you select all the files
|||
||| Fname is the Array of the selected files
|||
||| You can loop through the files in the Array with this
||| For N = LBound(FName) To UBound(FName)
|||
||| I use open the files as a example but you can do what you want.
|||
||| What do you want to do with the files in the Array?
||| Maybe we can give you a better answer then
|||
|| Hi Ron,
|| I have this code (from the internet). It copies the contence of the
|| seprate files to one workbook .
|| To prevent a lot of typing (the file are at a network server with
|| long names) I want to use the macro to fill the array.
|| I hope you understand the problem now.
||
||
|| Option Explicit
|| Sub GetData()
||
|| Dim myWorkbooks As Variant
|| Dim myFolder As String
|| Dim testStr As String
|| Dim newWkbk As Workbook
|| Dim tempWkbk As Workbook
|| Dim wCtr As Long
||
|| myFolder = "D:\Temp"
|| If Right(myFolder, 1) < "\" Then
|| myFolder = myFolder & "\"
|| End If
||
|| Set newWkbk = Workbooks.Add(1)
|| ActiveSheet.Name = "DummyToDelete"
||
|| myWorkbooks = Array("file1.xls", "file2.xls", "file4.xls")
||
|| For wCtr = LBound(myWorkbooks) To UBound(myWorkbooks)
|| testStr = ""
|| On Error Resume Next
|| testStr = Dir(myFolder & myWorkbooks(wCtr))
|| On Error GoTo 0
|| If testStr = "" Then
|| MsgBox myWorkbooks(wCtr) & " Is missing!" & vbLf & _
|| "Processing stopped"
|| Exit Sub
|| End If
||
|| Set tempWkbk = Workbooks.Open(Filename:=myFolder &
|| myWorkbooks(wCtr), _
|| ReadOnly:=True)
||
|| tempWkbk.Worksheets(1).Copy _
|| after:=newWkbk.Worksheets(newWkbk.Worksheets.Count )
||
|| tempWkbk.Close savechanges:=False
||
|| Next wCtr
||
|| Application.DisplayAlerts = False
|| newWkbk.Worksheets("DummyToDelete").Delete
|| Application.DisplayAlerts = True
||
|| Call RenSheets
|| End Sub

Flling an array
 

Ron, perfect
I used Left(naam, Len(naam) - 4) and that didn't work.
Now I know why.
Thank you, learned a lot today.
Kind regards, Adri

Ron de Bruin wrote:
| Try this
|
| ActiveSheet.Name = Left(mybook.Name, Len(mybook.Name) - 4)
|
|
|
| "Adri" wrote in message
| ...
|| Ron, thank you very much
|| TestFile6() wil do the job for me.
|| Lats little request. How can the sheetnames get a name without .xls
|| Adri
||
||
|| Ron de Bruin wrote:
||| Hi Adri
|||
||| Look at this examples first
||| http://www.rondebruin.nl/copy3.htm
|||
|||
|||
||| "Adri" wrote in message
||| ...
|||| Ron de Bruin wrote:
||||| Hi Adri
|||||
||||| You can select the files you want with the CTRL key down
||||| If you use CTRL-A in the dialog you select all the files
|||||
||||| Fname is the Array of the selected files
|||||
||||| You can loop through the files in the Array with this
||||| For N = LBound(FName) To UBound(FName)
|||||
||||| I use open the files as a example but you can do what you want.
|||||
||||| What do you want to do with the files in the Array?
||||| Maybe we can give you a better answer then
|||||
|||| Hi Ron,
|||| I have this code (from the internet). It copies the contence of the
|||| seprate files to one workbook .
|||| To prevent a lot of typing (the file are at a network server with
|||| long names) I want to use the macro to fill the array.
|||| I hope you understand the problem now.
||||
||||
|||| Option Explicit
|||| Sub GetData()
||||
|||| Dim myWorkbooks As Variant
|||| Dim myFolder As String
|||| Dim testStr As String
|||| Dim newWkbk As Workbook
|||| Dim tempWkbk As Workbook
|||| Dim wCtr As Long
||||
|||| myFolder = "D:\Temp"
|||| If Right(myFolder, 1) < "\" Then
|||| myFolder = myFolder & "\"
|||| End If
||||
|||| Set newWkbk = Workbooks.Add(1)
|||| ActiveSheet.Name = "DummyToDelete"
||||
|||| myWorkbooks = Array("file1.xls", "file2.xls", "file4.xls")
||||
|||| For wCtr = LBound(myWorkbooks) To UBound(myWorkbooks)
|||| testStr = ""
|||| On Error Resume Next
|||| testStr = Dir(myFolder & myWorkbooks(wCtr))
|||| On Error GoTo 0
|||| If testStr = "" Then
|||| MsgBox myWorkbooks(wCtr) & " Is missing!" & vbLf & _
|||| "Processing stopped"
|||| Exit Sub
|||| End If
||||
|||| Set tempWkbk = Workbooks.Open(Filename:=myFolder &
|||| myWorkbooks(wCtr), _
|||| ReadOnly:=True)
||||
|||| tempWkbk.Worksheets(1).Copy _
|||| after:=newWkbk.Worksheets(newWkbk.Worksheets.Count )
||||
|||| tempWkbk.Close savechanges:=False
||||
|||| Next wCtr
||||
|||| Application.DisplayAlerts = False
|||| newWkbk.Worksheets("DummyToDelete").Delete
|||| Application.DisplayAlerts = True
||||
|||| Call RenSheets
|||| End Sub

Flling an array
 

You are welcome


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Adri" wrote in message ...
Ron, perfect
I used Left(naam, Len(naam) - 4) and that didn't work.
Now I know why.
Thank you, learned a lot today.
Kind regards, Adri

Ron de Bruin wrote:
| Try this
|
| ActiveSheet.Name = Left(mybook.Name, Len(mybook.Name) - 4)
|
|
|
| "Adri" wrote in message
| ...
|| Ron, thank you very much
|| TestFile6() wil do the job for me.
|| Lats little request. How can the sheetnames get a name without .xls
|| Adri
||
||
|| Ron de Bruin wrote:
||| Hi Adri
|||
||| Look at this examples first
||| http://www.rondebruin.nl/copy3.htm
|||
|||
|||
||| "Adri" wrote in message
||| ...
|||| Ron de Bruin wrote:
||||| Hi Adri
|||||
||||| You can select the files you want with the CTRL key down
||||| If you use CTRL-A in the dialog you select all the files
|||||
||||| Fname is the Array of the selected files
|||||
||||| You can loop through the files in the Array with this
||||| For N = LBound(FName) To UBound(FName)
|||||
||||| I use open the files as a example but you can do what you want.
|||||
||||| What do you want to do with the files in the Array?
||||| Maybe we can give you a better answer then
|||||
|||| Hi Ron,
|||| I have this code (from the internet). It copies the contence of the
|||| seprate files to one workbook .
|||| To prevent a lot of typing (the file are at a network server with
|||| long names) I want to use the macro to fill the array.
|||| I hope you understand the problem now.
||||
||||
|||| Option Explicit
|||| Sub GetData()
||||
|||| Dim myWorkbooks As Variant
|||| Dim myFolder As String
|||| Dim testStr As String
|||| Dim newWkbk As Workbook
|||| Dim tempWkbk As Workbook
|||| Dim wCtr As Long
||||
|||| myFolder = "D:\Temp"
|||| If Right(myFolder, 1) < "\" Then
|||| myFolder = myFolder & "\"
|||| End If
||||
|||| Set newWkbk = Workbooks.Add(1)
|||| ActiveSheet.Name = "DummyToDelete"
||||
|||| myWorkbooks = Array("file1.xls", "file2.xls", "file4.xls")
||||
|||| For wCtr = LBound(myWorkbooks) To UBound(myWorkbooks)
|||| testStr = ""
|||| On Error Resume Next
|||| testStr = Dir(myFolder & myWorkbooks(wCtr))
|||| On Error GoTo 0
|||| If testStr = "" Then
|||| MsgBox myWorkbooks(wCtr) & " Is missing!" & vbLf & _
|||| "Processing stopped"
|||| Exit Sub
|||| End If
||||
|||| Set tempWkbk = Workbooks.Open(Filename:=myFolder &
|||| myWorkbooks(wCtr), _
|||| ReadOnly:=True)
||||
|||| tempWkbk.Worksheets(1).Copy _
|||| after:=newWkbk.Worksheets(newWkbk.Worksheets.Count )
||||
|||| tempWkbk.Close savechanges:=False
||||
|||| Next wCtr
||||
|||| Application.DisplayAlerts = False
|||| newWkbk.Worksheets("DummyToDelete").Delete
|||| Application.DisplayAlerts = True
||||
|||| Call RenSheets
|||| End Sub