Alternative:

Probably fastest way is to use ADO to read the workbook's sheets
and dump the data into 1 new workbook or mdb. Mdb is probably
fastest solution (if you're going to use Pivots anyway :)

You could use linked tables in access.. but ...


Just to give you an idea of HOW fast ADO can be..
I copied this little thingy which consolidates 1000 files
into 1 database in no time...

guess how long? .. now run the routine and be amazed


Create new workbook.. put some dummy data in, and save it as
data0000. in a new directory.

edit the path constant.. to point to the directory :)
Then run the routine which creates 1000 copies for testing.
Then run the tstPump to see how fast it consolidates things...

Option Explicit
'Code needs a reference to Microsoft ActiveX Data Objects 2.5 or higher)

Const PATH = "d:\phone"

Sub CreateTestXLS()
Dim i
ChDrive PATH
ChDir PATH
For i = 1 To 1000
FileCopy "data 0000.xls", "customer " & Format(i, "0000") & ".xls"
Next
End Sub

Sub CreateNewMDB(FileName As String)
Dim ocat As Object
Set ocat = CreateObject("ADOX.Catalog")
ocat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Jet OLEDB:Engine Type=5;Data Source=" & FileName
End Sub

Sub tstPump()
Dim t!, i%
t = Timer
If Dir(PATH & "\data.mdb") = "" Then CreateNewMDB PATH & "\data.mdb"
MDBDropDump
For i = 1 To 1000
XLS2MDB PATH & "\customer " & Format(i, "0000") & ".xls"
Next
MsgBox "done in " & Format(Timer - t, "0") & " seconds."

End Sub

Sub MDBDropDump()
Dim cnn As ADODB.Connection
Const cnnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
PATH
& "\data.mdb" & ";"

Set cnn = New Connection
cnn.Open cnnString
cnn.CursorLocation = adUseClient
On Error Resume Next
cnn.Execute ("DROP TABLE DUMP")
cnn.Close

End Sub


Sub XLS2MDB(sXlFile$)
Dim cnn As ADODB.Connection

Const cnnString = "Provider=Microsoft.Jet.OLEDB.4.0;Extended
Properties=""Excel 8.0;Header:YES"";Data Source=&FFFF;"

Set cnn = New Connection
cnn.Open (Replace(cnnString, "&FFFF", sXlFile))
cnn.CursorLocation = adUseClient
On Error GoTo errH
cnn.Execute "INSERT INTO dump IN 'd:\phone\data.mdb' select '" & _
Left(sXlFile, Len(sXlFile) - 4) & "' as Client , * from `sheet1$`"

endH:
cnn.Close
Exit Sub

errH:
If Err.Number = -2147217865 Then
'This creates the table..
cnn.Execute "select '" & Left(sXlFile, Len(sXlFile) - 4) & _
"' as Client , * INTO dump IN 'd:\phone\data.mdb' from `sheet1$`"
else
msgbox err.number & vbnewline & err.description
End If
GoTo endH

End Sub



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Steve wrote :

Hi,

I am using Excel 2000. I have a workbook with about 30 worksheets.
Each worksheet has an identical layout containing 14 sections every
45 rows with a number of empty rows and cells. I am using coumns A
through S. The columns I want to use are in column A and B, and G
through S. It is model forecasting volume, revenue, expense (months
in G:S and year in S).

Right now these I have linked these 30 worksheets into a single
worksheet in a separate workbook, the purpose for which is to
organize my data in tabular form so that I can create a pivot table.

Updating the links in 15,000 rows and 19 columns is much too slow in
Excel 2000. I am wondering if there is a macro available I could use
to copy and paste value the data i need.

I could record a macro to do this, but I am wondering if there is a
better way.

For example, if I recorded the macro, I would tell it to in worksheet
1, copy A6:B6 and paste value in new workbook sheet, copy and paste
G6:S6A6:B6, copy paste A12:B18, copy paste E12:S18, copy paste
A20:B33, copy paste G20:S33, and so on, until all my data is repasted
into a tabular format in the new workbook.

Thanks very much.

I've read
http://j-walk.com/ss/excel/tips/tip82.htm

and

http://www.google.com/groups?hl=en&l...1La.4495%24cJ5.
436%40www.newsranger.com&rnum=6

but I am not sure how to make these work for my situation.

Thanks, I will give it a try.

"keepITcool" wrote:

Alternative:

Probably fastest way is to use ADO to read the workbook's sheets
and dump the data into 1 new workbook or mdb. Mdb is probably
fastest solution (if you're going to use Pivots anyway :)

You could use linked tables in access.. but ...


Just to give you an idea of HOW fast ADO can be..
I copied this little thingy which consolidates 1000 files
into 1 database in no time...

guess how long? .. now run the routine and be amazed


Create new workbook.. put some dummy data in, and save it as
data0000. in a new directory.

edit the path constant.. to point to the directory :)
Then run the routine which creates 1000 copies for testing.
Then run the tstPump to see how fast it consolidates things...

Option Explicit
'Code needs a reference to Microsoft ActiveX Data Objects 2.5 or higher)

Const PATH = "d:\phone"

Sub CreateTestXLS()
Dim i
ChDrive PATH
ChDir PATH
For i = 1 To 1000
FileCopy "data 0000.xls", "customer " & Format(i, "0000") & ".xls"
Next
End Sub

Sub CreateNewMDB(FileName As String)
Dim ocat As Object
Set ocat = CreateObject("ADOX.Catalog")
ocat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Jet OLEDB:Engine Type=5;Data Source=" & FileName
End Sub

Sub tstPump()
Dim t!, i%
t = Timer
If Dir(PATH & "\data.mdb") = "" Then CreateNewMDB PATH & "\data.mdb"
MDBDropDump
For i = 1 To 1000
XLS2MDB PATH & "\customer " & Format(i, "0000") & ".xls"
Next
MsgBox "done in " & Format(Timer - t, "0") & " seconds."

End Sub

Sub MDBDropDump()
Dim cnn As ADODB.Connection
Const cnnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
PATH
& "\data.mdb" & ";"

Set cnn = New Connection
cnn.Open cnnString
cnn.CursorLocation = adUseClient
On Error Resume Next
cnn.Execute ("DROP TABLE DUMP")
cnn.Close

End Sub


Sub XLS2MDB(sXlFile$)
Dim cnn As ADODB.Connection

Const cnnString = "Provider=Microsoft.Jet.OLEDB.4.0;Extended
Properties=""Excel 8.0;Header:YES"";Data Source=&FFFF;"

Set cnn = New Connection
cnn.Open (Replace(cnnString, "&FFFF", sXlFile))
cnn.CursorLocation = adUseClient
On Error GoTo errH
cnn.Execute "INSERT INTO dump IN 'd:\phone\data.mdb' select '" & _
Left(sXlFile, Len(sXlFile) - 4) & "' as Client , * from `sheet1$`"

endH:
cnn.Close
Exit Sub

errH:
If Err.Number = -2147217865 Then
'This creates the table..
cnn.Execute "select '" & Left(sXlFile, Len(sXlFile) - 4) & _
"' as Client , * INTO dump IN 'd:\phone\data.mdb' from `sheet1$`"
else
msgbox err.number & vbnewline & err.description
End If
GoTo endH

End Sub



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Steve wrote :

Hi,

I am using Excel 2000. I have a workbook with about 30 worksheets.
Each worksheet has an identical layout containing 14 sections every
45 rows with a number of empty rows and cells. I am using coumns A
through S. The columns I want to use are in column A and B, and G
through S. It is model forecasting volume, revenue, expense (months
in G:S and year in S).

Right now these I have linked these 30 worksheets into a single
worksheet in a separate workbook, the purpose for which is to
organize my data in tabular form so that I can create a pivot table.

Updating the links in 15,000 rows and 19 columns is much too slow in
Excel 2000. I am wondering if there is a macro available I could use
to copy and paste value the data i need.

I could record a macro to do this, but I am wondering if there is a
better way.

For example, if I recorded the macro, I would tell it to in worksheet
1, copy A6:B6 and paste value in new workbook sheet, copy and paste
G6:S6A6:B6, copy paste A12:B18, copy paste E12:S18, copy paste
A20:B33, copy paste G20:S33, and so on, until all my data is repasted
into a tabular format in the new workbook.

Thanks very much.

I've read
http://j-walk.com/ss/excel/tips/tip82.htm

and

http://www.google.com/groups?hl=en&l...1La.4495%24cJ5.
436%40www.newsranger.com&rnum=6

but I am not sure how to make these work for my situation.

"keepITcool" wrote ...

Just to give you an idea of HOW fast ADO can be..
I copied this little thingy which consolidates 1000 files
into 1 database in no time...

guess how long?

I give up: how long?

Jamie.

--

Steve..

Read carefully what I posted a week ago..:(
I've snipped the rest.. but left what's now bugging you.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Steve wrote :

I just found time to try this out... I get an error message while
running tstpump: Compile Error: User-defined type not defined.

The debugger highlights the following in yellow:
Dim cnn As ADODB.Connection


Any suggestions? Thanks your coolness.

"keepITcool" wrote:

Alternative:


Option Explicit
'Code needs a reference to Microsoft ActiveX Data Objects 2.5 or
higher)

Steve,

very likely no donwload needed as ADO should be installed on your
machine,.

in the VBE in Tools/References find
Microsoft Active Data Objects Version xx

there are probably a few veriosn listed.
2.5 is well regarded
(latest version is 2.8 but for this there's no need to download it)

put a checkmark on the 2.5 version (or newer)
close the References dialog)

Then / Debug /Compile 'VBAprojectname' and it should compile ok.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Steve wrote :

Hi, thanks for getting back to me. I'm kind of new at this. I am
not sure what I have to download and install for me to get started...
if you get a chance, can you point me to the correct link? thanks...


I found the following, but don't know exactly what I need.
http://msdn.microsoft.com/data/

I have XP professional operating system at home and windows 2000 at
work. I have excel 2002 at home and excel 2000 at work.

Thanks...


Option Explicit
'Code needs a reference to Microsoft ActiveX Data Objects 2.5 or
higher)

Holy Moly Cool

That macro you sent me took 1000 471KB excel workbooks (1 worksheet each) and consolidated them into a single access database 525,532 Kb in 308 seconds.

I then adjusted the macro for 30 workbooks (more than I need) and consolidated them into a 15mb Access database file in 7 seconds. Very fast! Awesome.

Now I just have to figure out how to make your macro apply to my specific case at work. I'll let you know how it goes (and will probably have to ask you for more pointers). Thanks again!

Steve




"keepITcool" wrote:

Alternative:

Probably fastest way is to use ADO to read the workbook's sheets
and dump the data into 1 new workbook or mdb. Mdb is probably
fastest solution (if you're going to use Pivots anyway :)

You could use linked tables in access.. but ...


Just to give you an idea of HOW fast ADO can be..
I copied this little thingy which consolidates 1000 files
into 1 database in no time...

guess how long? .. now run the routine and be amazed


Create new workbook.. put some dummy data in, and save it as
data0000. in a new directory.

edit the path constant.. to point to the directory :)
Then run the routine which creates 1000 copies for testing.
Then run the tstPump to see how fast it consolidates things...

Option Explicit
'Code needs a reference to Microsoft ActiveX Data Objects 2.5 or higher)

Const PATH = "d:\phone"

Sub CreateTestXLS()
Dim i
ChDrive PATH
ChDir PATH
For i = 1 To 1000
FileCopy "data 0000.xls", "customer " & Format(i, "0000") & ".xls"
Next
End Sub

Sub CreateNewMDB(FileName As String)
Dim ocat As Object
Set ocat = CreateObject("ADOX.Catalog")
ocat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Jet OLEDB:Engine Type=5;Data Source=" & FileName
End Sub

Sub tstPump()
Dim t!, i%
t = Timer
If Dir(PATH & "\data.mdb") = "" Then CreateNewMDB PATH & "\data.mdb"
MDBDropDump
For i = 1 To 1000
XLS2MDB PATH & "\customer " & Format(i, "0000") & ".xls"
Next
MsgBox "done in " & Format(Timer - t, "0") & " seconds."

End Sub

Sub MDBDropDump()
Dim cnn As ADODB.Connection
Const cnnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
PATH
& "\data.mdb" & ";"

Set cnn = New Connection
cnn.Open cnnString
cnn.CursorLocation = adUseClient
On Error Resume Next
cnn.Execute ("DROP TABLE DUMP")
cnn.Close

End Sub


Sub XLS2MDB(sXlFile$)
Dim cnn As ADODB.Connection

Const cnnString = "Provider=Microsoft.Jet.OLEDB.4.0;Extended
Properties=""Excel 8.0;Header:YES"";Data Source=&FFFF;"

Set cnn = New Connection
cnn.Open (Replace(cnnString, "&FFFF", sXlFile))
cnn.CursorLocation = adUseClient
On Error GoTo errH
cnn.Execute "INSERT INTO dump IN 'd:\phone\data.mdb' select '" & _
Left(sXlFile, Len(sXlFile) - 4) & "' as Client , * from `sheet1$`"

endH:
cnn.Close
Exit Sub

errH:
If Err.Number = -2147217865 Then
'This creates the table..
cnn.Execute "select '" & Left(sXlFile, Len(sXlFile) - 4) & _
"' as Client , * INTO dump IN 'd:\phone\data.mdb' from `sheet1$`"
else
msgbox err.number & vbnewline & err.description
End If
GoTo endH

End Sub



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Steve wrote :

Hi,

I am using Excel 2000. I have a workbook with about 30 worksheets.
Each worksheet has an identical layout containing 14 sections every
45 rows with a number of empty rows and cells. I am using coumns A
through S. The columns I want to use are in column A and B, and G
through S. It is model forecasting volume, revenue, expense (months
in G:S and year in S).

Right now these I have linked these 30 worksheets into a single
worksheet in a separate workbook, the purpose for which is to
organize my data in tabular form so that I can create a pivot table.

Updating the links in 15,000 rows and 19 columns is much too slow in
Excel 2000. I am wondering if there is a macro available I could use
to copy and paste value the data i need.

I could record a macro to do this, but I am wondering if there is a
better way.

For example, if I recorded the macro, I would tell it to in worksheet
1, copy A6:B6 and paste value in new workbook sheet, copy and paste
G6:S6A6:B6, copy paste A12:B18, copy paste E12:S18, copy paste
A20:B33, copy paste G20:S33, and so on, until all my data is repasted
into a tabular format in the new workbook.

Thanks very much.

I've read
http://j-walk.com/ss/excel/tips/tip82.htm

and

http://www.google.com/groups?hl=en&l...1La.4495%24cJ5.
436%40www.newsranger.com&rnum=6

but I am not sure how to make these work for my situation.