Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to updating 300,000 links in Excel 2000?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to updating 300,000 links in Excel 2000?
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to updating 300,000 links in Excel 2000?
"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. -- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to updating 300,000 links in Excel 2000?
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countifs in Excel 2000? Alternative solution sought!!! | Excel Discussion (Misc queries) | |||
re Alternative to updating 300,000 links in Excel 2000? | Excel Programming | |||
Alternative to links for updating worksheet? | Excel Programming | |||
Writing a macro to change external links to manual updating in Excel 2000 | Excel Programming | |||
Updating Links in Excel | Excel Programming |