Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
transfer range of cells from excel to Acess using vba
Hi there,
I am working on a script to transfer a *lot* of data between excel and MS Access. Currently it has a lot of loops, transfering a single value each time, for example: Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=C:\...\Output.mdb;" Set rs = New ADODB.Recordset rs.Open "Output_Table", cn, adOpenKeyset, adLockOptimistic, adCmdTable for i in 1:10000 ' calculate some number ' export i'th number to access rs.fields(i) = worksheets.range(... next i I am hoping that it might be possible to do all the calculation, then transfer the whole lot in one go to access - I have a feeling that this may speed it up a bit. Any views on this? My main question is, is it possible to transfer a range of cells from excel to access via vba? Many thanks, Chris |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
transfer range of cells from excel to Acess using vba
You can't do it from the Access side can you? Then you can use:
DoCmd.TransferSpreadsheet acImport, 8, "Access table", "path to Excel file", True, "range name in workbook" Alternatively, you might want to create a 2d variant array out of your Excel data before populating: Dim vntData As Variant vntData = Worksheets("worksheet").Range("...") You can then use vntData(x,y) to access each data element. "christian_spaceman" wrote: Hi there, I am working on a script to transfer a *lot* of data between excel and MS Access. Currently it has a lot of loops, transfering a single value each time, for example: Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=C:\...\Output.mdb;" Set rs = New ADODB.Recordset rs.Open "Output_Table", cn, adOpenKeyset, adLockOptimistic, adCmdTable for i in 1:10000 ' calculate some number ' export i'th number to access rs.fields(i) = worksheets.range(... next i I am hoping that it might be possible to do all the calculation, then transfer the whole lot in one go to access - I have a feeling that this may speed it up a bit. Any views on this? My main question is, is it possible to transfer a range of cells from excel to access via vba? Many thanks, Chris |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
transfer range of cells from excel to Acess using vba
Thanks for replying
hmm - that first example is exactly what I was after, the ability to transfer a whole range at once. I think in the second, I'd still have to iterate through all the cells in the array and transfer them individually wouldn't I? I could do it from the access side, the only thing thats stopping me is that the rest of the model is written and distributed in excel (only really using access to store resutls because it has more rows). Do you know of anyway to transfer a range from the excel side? Cheers Chris On Dec 10, 3:22 pm, Smallweed wrote: You can't do it from the Access side can you? Then you can use: DoCmd.TransferSpreadsheet acImport, 8, "Access table", "path to Excel file", True, "range name in workbook" Alternatively, you might want to create a 2d variant array out of your Excel data before populating: Dim vntData As Variant vntData = Worksheets("worksheet").Range("...") You can then use vntData(x,y) to access each data element. "christian_spaceman" wrote: Hi there, I am working on a script to transfer a *lot* of data between excel and MS Access. Currently it has a lot of loops, transfering a single value each time, for example: Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=C:\...\Output.mdb;" Set rs = New ADODB.Recordset rs.Open "Output_Table", cn, adOpenKeyset, adLockOptimistic, adCmdTable for i in 1:10000 ' calculate some number ' export i'th number to access rs.fields(i) = worksheets.range(... next i I am hoping that it might be possible to do all the calculation, then transfer the whole lot in one go to access - I have a feeling that this may speed it up a bit. Any views on this? My main question is, is it possible to transfer a range of cells from excel to access via vba? Many thanks, Chris- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
transfer range of cells from excel to Acess using vba
You are going to use code similar to what you have. Your recordset object is
exactly what the name implies... An object designed to hold a set of records. What you want to do is to write records into the recordset using the rst.AddNew (list fields and values) method. When the recordset is complete then you commit the recordset to the database with the rst.Update method of the recordset object. -- HTH... Jim Thomlinson "christian_spaceman" wrote: Thanks for replying hmm - that first example is exactly what I was after, the ability to transfer a whole range at once. I think in the second, I'd still have to iterate through all the cells in the array and transfer them individually wouldn't I? I could do it from the access side, the only thing thats stopping me is that the rest of the model is written and distributed in excel (only really using access to store resutls because it has more rows). Do you know of anyway to transfer a range from the excel side? Cheers Chris On Dec 10, 3:22 pm, Smallweed wrote: You can't do it from the Access side can you? Then you can use: DoCmd.TransferSpreadsheet acImport, 8, "Access table", "path to Excel file", True, "range name in workbook" Alternatively, you might want to create a 2d variant array out of your Excel data before populating: Dim vntData As Variant vntData = Worksheets("worksheet").Range("...") You can then use vntData(x,y) to access each data element. "christian_spaceman" wrote: Hi there, I am working on a script to transfer a *lot* of data between excel and MS Access. Currently it has a lot of loops, transfering a single value each time, for example: Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=C:\...\Output.mdb;" Set rs = New ADODB.Recordset rs.Open "Output_Table", cn, adOpenKeyset, adLockOptimistic, adCmdTable for i in 1:10000 ' calculate some number ' export i'th number to access rs.fields(i) = worksheets.range(... next i I am hoping that it might be possible to do all the calculation, then transfer the whole lot in one go to access - I have a feeling that this may speed it up a bit. Any views on this? My main question is, is it possible to transfer a range of cells from excel to access via vba? Many thanks, Chris- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
transfer range of cells from excel to Acess using vba
Thanks Jim,
It looks like I misunderstand the recordset - I can keep adding as much *stuff* to it, then do a single update once it is full ( I am currently updating once per loop iteration). No idea if this will speed it up any but worth a try! Thanks for your help, Cheers, Chris On Dec 10, 4:41 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move- This-.com wrote: You are going to use code similar to what you have. Your recordset object is exactly what the name implies... An object designed to hold a set of records. What you want to do is to write records into the recordset using the rst.AddNew (list fields and values) method. When the recordset is complete then you commit the recordset to the database with the rst.Update method of the recordset object. -- HTH... Jim Thomlinson "christian_spaceman" wrote: Thanks for replying hmm - that first example is exactly what I was after, the ability to transfer a whole range at once. I think in the second, I'd still have to iterate through all the cells in the array and transfer them individually wouldn't I? I could do it from the access side, the only thing thats stopping me is that the rest of the model is written and distributed in excel (only really using access to store resutls because it has more rows). Do you know of anyway to transfer a range from the excel side? Cheers Chris On Dec 10, 3:22 pm, Smallweed wrote: You can't do it from the Access side can you? Then you can use: DoCmd.TransferSpreadsheet acImport, 8, "Access table", "path to Excel file", True, "range name in workbook" Alternatively, you might want to create a 2d variant array out of your Excel data before populating: Dim vntData As Variant vntData = Worksheets("worksheet").Range("...") You can then use vntData(x,y) to access each data element. "christian_spaceman" wrote: Hi there, I am working on a script to transfer a *lot* of data between excel and MS Access. Currently it has a lot of loops, transfering a single value each time, for example: Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=C:\...\Output.mdb;" Set rs = New ADODB.Recordset rs.Open "Output_Table", cn, adOpenKeyset, adLockOptimistic, adCmdTable for i in 1:10000 ' calculate some number ' export i'th number to access rs.fields(i) = worksheets.range(... next i I am hoping that it might be possible to do all the calculation, then transfer the whole lot in one go to access - I have a feeling that this may speed it up a bit. Any views on this? My main question is, is it possible to transfer a range of cells from excel to access via vba? Many thanks, Chris- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can Excel be linked to MS Acess? | Excel Discussion (Misc queries) | |||
How to transfer text to an sheet if the cells en range contains a | Excel Discussion (Misc queries) | |||
Linking Acess with Excel | Excel Discussion (Misc queries) | |||
How to Transfer an Excel Range to an ADO.Net DataSet or DataTable in VB.Net? | Excel Programming | |||
transfer data into row cells from column cells in Excel | Excel Discussion (Misc queries) |