Hello!

Im using Excel 2007 file to store very large database: 600k rows & 40
columns in one spreadsheet. Its more than 250MB on hard drive.

Im using it to draw many charts. Usually I copy charts to PowerPoint 2007 to
get small file sieze.

I wonder if its possigle to transfer data from xlsx to another file, and use
it as external data source. My target is to get small xlxs files, without any
need to copy charts to PowerPoint 2007.

I was trying to put data into Access 2007 db, but.... System was crashing
during importing.

What is most efficient method of storing such a big database, to use it in
Excel 2007 ?

I hope that someone answer my question. Its first time I write on this
newsgroup... :)

regards, Przemek

inserting 786k rowsx 32 cols into Access 2007 from Excel 2007 took ca.
45 mins on my AMD Athlon 64 X2 Dual Core Processor 3800+ 2.01 GHZ, 1GB
RAM. and it never crashed

copying the same amount of data from Access 2007 to Excel 2007 (via
CopyFromRecordset) method took less than 1 minute

you may copy the data from Access to Excel every time you need to
update yr charts

never worked with such vast amounts of data in Excel but maybe this
method is worth trying?

1. in both macros change
path "C:\Documents and Settings\Jarek\Pulpit\" to your path
file name "Excel_Access.mdb" to your file name
table name "TranStany" to your table name
as well as 31 to 40 in Sub sciagnij_dane_z_ACCESSA()
(and anything else I might have forgot of)
accordingly for the macros to work

used this macro to insert data into Access:

Sub dopisz_do_ACCESSa()
Dim i As Integer
Dim licz As Integer
Dim rekord As String
Dim wartosci_pol_rekordu As String
Dim rsData As ADODB.Recordset
Dim cnAccess As ADODB.Connection
Dim sConnect As String
Dim sPath As String
Dim sSQL As String

sPath = "C:\Documents and Settings\Jarek\Pulpit\"


If Right$(sPath, 1) < "\" Then sPath = sPath & "\"

Set rsData = New ADODB.Recordset
Set cnAccess = New ADODB.Connection
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sPath & "Excel_Access.mdb;"
cnAccess.ConnectionString = sConnect
cnAccess.Open


sSQL = "DELETE * FROM TranStany"

'Use the Connection object to execute the SQL statement.
cnAccess.Execute sSQL, , adCmdText + adExecuteNoRecords

ostatnia_data = 1
ostatni_rekord = 1000000

Range(Cells(ostatnia_data, 1), Cells(ostatni_rekord, 1)).Select

For Each cell In Selection

rekord = vbNullString
licz = 0

If Len(cell.Value) 0 Then

For m = 0 To 31
'cell.Offset(0, m).Activate
If m 0 Then
If cell.Offset(0, m) = 0 Then
rekord = rekord & "','" & 0
Else
rekord = rekord & "','" & cell.Offset(0, m)
End If
Else
rekord = cell.Value
End If

Next m

wartosci_pol_rekordu = " VALUES (" & "'" & rekord & "'" & ")"

sSQL = "INSERT INTO TranStany " &
wartosci_pol_rekordu

'Use the Connection object to execute the SQL
statement.
cnAccess.Execute sSQL, , adCmdText +
adExecuteNoRecords

Else
Exit For
End If

Next cell

cnAccess.Close
Set rsData = Nothing
Set cnAccess = Nothing

End Sub



I used this one to cope data from Access to Excel:



Sub sciagnij_dane_z_ACCESSA()
Dim rsData As ADODB.Recordset
Dim cnAccess As ADODB.Connection
Dim sConnect As String
Dim sPath As String
Dim sSQL As String

sPath = "C:\Documents and Settings\Jarek\Pulpit\"

If Right$(sPath, 1) < "\" Then sPath = sPath & "\"

Set rsData = New ADODB.Recordset
Set cnAccess = New ADODB.Connection
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sPath & "Excel_Access.mdb;"
cnAccess.ConnectionString = sConnect
cnAccess.Open


sSQL = "SELECT * FROM TranStany"

Set rsData = New ADODB.Recordset
rsData.Open sSQL, sConnect, _
adOpenForwardOnly, adLockReadOnly, adCmdText
If Not rsData.EOF Then
ActiveSheet.Range("A1:M100000").Rows.EntireRow.Del ete
ActiveSheet.Range("A1").CopyFromRecordset rsData
Else
MsgBox "No data located.", vbCritical, "Error!"
End If

rsData.Close
cnAccess.Close
Set rsData = Nothing
Set cnAccess = Nothing

End Sub


HIH

On 4 Lis, 14:49, Przemyslaw Robak <Przemyslaw
wrote:
Hello!

Im using Excel 2007 file to store very large database: 600k rows & 40
columns in one spreadsheet. Its more than 250MB on hard drive.

Im using it to draw many charts. Usually I copy charts to PowerPoint 2007 to
get small file sieze.

I wonder if its possigle to transfer data from xlsx to another file, and use
it as external data source. My target is to get small xlxs files, without any
need to copy charts to PowerPoint 2007.

I was trying to put data into Access 2007 db, but.... System was crashing
during importing.

What is most efficient method of storing such a big database, to use it in
Excel 2007 ?

I hope that someone answer my question. Its first time I write on this
newsgroup... :)

regards, Przemek

I also need to mention that both macros were based on examples for
"Professional Excel Development" by S. Bullen, R. Bovey and J. Green

ackonwledgements

On 4 Lis, 20:27, Jarek Kujawa wrote:
inserting 786k rowsx 32 cols into Access 2007 from Excel 2007 took ca.
45 mins on my AMD Athlon 64 X2 Dual Core Processor 3800+ 2.01 GHZ, 1GB
RAM. and it never crashed

copying the same amount of data from Access 2007 *to Excel 2007 (via
CopyFromRecordset) method took less than 1 minute

you may copy the data from Access to Excel every time you need to
update yr charts

never worked with such vast amounts of data in Excel but maybe this
method is worth trying?

1. in both macros change
path "C:\Documents and Settings\Jarek\Pulpit\" to your path
file name "Excel_Access.mdb" *to your file name
table name "TranStany" *to your table name
as well as 31 to 40 in Sub sciagnij_dane_z_ACCESSA()
(and anything else I might have forgot of)
accordingly for the macros to work

used this macro to insert data into Access:

Sub dopisz_do_ACCESSa()
Dim i As Integer
Dim licz As Integer
Dim rekord As String
Dim wartosci_pol_rekordu As String
Dim rsData As ADODB.Recordset
Dim cnAccess As ADODB.Connection
Dim sConnect As String
Dim sPath As String
Dim sSQL As String

* * sPath = "C:\Documents and Settings\Jarek\Pulpit\"

* * If Right$(sPath, 1) < "\" Then sPath = sPath & "\"

* * Set rsData = New ADODB.Recordset
* * Set cnAccess = New ADODB.Connection
* * sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
* * * * * * * * "Data Source=" & sPath & "Excel_Access.mdb;"
* * cnAccess.ConnectionString = sConnect
* * cnAccess.Open

* * * * sSQL = "DELETE * FROM TranStany"

* * * * * * 'Use the Connection object to execute the SQL statement.
* * * * cnAccess.Execute sSQL, , adCmdText + adExecuteNoRecords

ostatnia_data = 1
ostatni_rekord = 1000000

Range(Cells(ostatnia_data, 1), Cells(ostatni_rekord, 1)).Select

For Each cell In Selection

rekord = vbNullString
licz = 0

If Len(cell.Value) 0 Then

* * For m = 0 To 31
* * * * 'cell.Offset(0, m).Activate
* * * * If m 0 Then
* * * * * * If cell.Offset(0, m) = 0 Then
* * * * * * * * rekord = rekord & "','" & 0
* * * * * * Else
* * * * * * * * rekord = rekord & "','" & cell.Offset(0, m)
* * * * * * End If
* * * * Else
* * * * * * rekord = cell.Value
* * * * End If

* * Next m

wartosci_pol_rekordu = " VALUES (" & "'" & rekord & "'" & ")"

* * * * * * * * * * sSQL = "INSERT INTO TranStany " &
wartosci_pol_rekordu

* * * * * * * * * * * * 'Use the Connection object to execute the SQL
statement.
* * * * * * * * * * cnAccess.Execute sSQL, , adCmdText +
adExecuteNoRecords

Else
* * Exit For
End If

Next cell

* * cnAccess.Close
* * Set rsData = Nothing
* * Set cnAccess = Nothing

End Sub

I used this one to cope data from Access to Excel:

Sub sciagnij_dane_z_ACCESSA()
Dim rsData As ADODB.Recordset
Dim cnAccess As ADODB.Connection
Dim sConnect As String
Dim sPath As String
Dim sSQL As String

* * sPath = "C:\Documents and Settings\Jarek\Pulpit\"

* * If Right$(sPath, 1) < "\" Then sPath = sPath & "\"

* * Set rsData = New ADODB.Recordset
* * Set cnAccess = New ADODB.Connection
* * sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
* * * * * * * * "Data Source=" & sPath & "Excel_Access.mdb;"
* * cnAccess.ConnectionString = sConnect
* * cnAccess.Open

sSQL = "SELECT * FROM TranStany"

* * Set rsData = New ADODB.Recordset
* * rsData.Open sSQL, sConnect, _
* * * * * * * * adOpenForwardOnly, adLockReadOnly, adCmdText
* * If Not rsData.EOF Then
* * * * * * ActiveSheet.Range("A1:M100000").Rows.EntireRow.Del ete
* * * * * * ActiveSheet.Range("A1").CopyFromRecordset rsData
* * Else
* * * * MsgBox "No data located.", vbCritical, "Error!"
* * End If

* * rsData.Close
* * cnAccess.Close
* * Set rsData = Nothing
* * Set cnAccess = Nothing

End Sub

HIH

On 4 Lis, 14:49, Przemyslaw Robak <Przemyslaw



wrote:
Hello!

Im using Excel 2007 file to store very large database: 600k rows & 40
columns in one spreadsheet. Its more than 250MB on hard drive.

Im using it to draw many charts. Usually I copy charts to PowerPoint 2007 to
get small file sieze.

I wonder if its possigle to transfer data from xlsx to another file, and use
it as external data source. My target is to get small xlxs files, without any
need to copy charts to PowerPoint 2007.

I was trying to put data into Access 2007 db, but.... System was crashing
during importing.

What is most efficient method of storing such a big database, to use it in
Excel 2007 ?

I hope that someone answer my question. Its first time I write on this
newsgroup... :)

regards, Przemek- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -

also you must have Microsoft ActiveX Data Objects x.x Library in place
(Tools-References...)

On 4 Lis, 20:27, Jarek Kujawa wrote:
inserting 786k rowsx 32 cols into Access 2007 from Excel 2007 took ca.
45 mins on my AMD Athlon 64 X2 Dual Core Processor 3800+ 2.01 GHZ, 1GB
RAM. and it never crashed

copying the same amount of data from Access 2007 *to Excel 2007 (via
CopyFromRecordset) method took less than 1 minute

you may copy the data from Access to Excel every time you need to
update yr charts

never worked with such vast amounts of data in Excel but maybe this
method is worth trying?

1. in both macros change
path "C:\Documents and Settings\Jarek\Pulpit\" to your path
file name "Excel_Access.mdb" *to your file name
table name "TranStany" *to your table name
as well as 31 to 40 in Sub sciagnij_dane_z_ACCESSA()
(and anything else I might have forgot of)
accordingly for the macros to work

used this macro to insert data into Access:

Sub dopisz_do_ACCESSa()
Dim i As Integer
Dim licz As Integer
Dim rekord As String
Dim wartosci_pol_rekordu As String
Dim rsData As ADODB.Recordset
Dim cnAccess As ADODB.Connection
Dim sConnect As String
Dim sPath As String
Dim sSQL As String

* * sPath = "C:\Documents and Settings\Jarek\Pulpit\"

* * If Right$(sPath, 1) < "\" Then sPath = sPath & "\"

* * Set rsData = New ADODB.Recordset
* * Set cnAccess = New ADODB.Connection
* * sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
* * * * * * * * "Data Source=" & sPath & "Excel_Access.mdb;"
* * cnAccess.ConnectionString = sConnect
* * cnAccess.Open

* * * * sSQL = "DELETE * FROM TranStany"

* * * * * * 'Use the Connection object to execute the SQL statement.
* * * * cnAccess.Execute sSQL, , adCmdText + adExecuteNoRecords

ostatnia_data = 1
ostatni_rekord = 1000000

Range(Cells(ostatnia_data, 1), Cells(ostatni_rekord, 1)).Select

For Each cell In Selection

rekord = vbNullString
licz = 0

If Len(cell.Value) 0 Then

* * For m = 0 To 31
* * * * 'cell.Offset(0, m).Activate
* * * * If m 0 Then
* * * * * * If cell.Offset(0, m) = 0 Then
* * * * * * * * rekord = rekord & "','" & 0
* * * * * * Else
* * * * * * * * rekord = rekord & "','" & cell.Offset(0, m)
* * * * * * End If
* * * * Else
* * * * * * rekord = cell.Value
* * * * End If

* * Next m

wartosci_pol_rekordu = " VALUES (" & "'" & rekord & "'" & ")"

* * * * * * * * * * sSQL = "INSERT INTO TranStany " &
wartosci_pol_rekordu

* * * * * * * * * * * * 'Use the Connection object to execute the SQL
statement.
* * * * * * * * * * cnAccess.Execute sSQL, , adCmdText +
adExecuteNoRecords

Else
* * Exit For
End If

Next cell

* * cnAccess.Close
* * Set rsData = Nothing
* * Set cnAccess = Nothing

End Sub

I used this one to cope data from Access to Excel:

Sub sciagnij_dane_z_ACCESSA()
Dim rsData As ADODB.Recordset
Dim cnAccess As ADODB.Connection
Dim sConnect As String
Dim sPath As String
Dim sSQL As String

* * sPath = "C:\Documents and Settings\Jarek\Pulpit\"

* * If Right$(sPath, 1) < "\" Then sPath = sPath & "\"

* * Set rsData = New ADODB.Recordset
* * Set cnAccess = New ADODB.Connection
* * sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
* * * * * * * * "Data Source=" & sPath & "Excel_Access.mdb;"
* * cnAccess.ConnectionString = sConnect
* * cnAccess.Open

sSQL = "SELECT * FROM TranStany"

* * Set rsData = New ADODB.Recordset
* * rsData.Open sSQL, sConnect, _
* * * * * * * * adOpenForwardOnly, adLockReadOnly, adCmdText
* * If Not rsData.EOF Then
* * * * * * ActiveSheet.Range("A1:M100000").Rows.EntireRow.Del ete
* * * * * * ActiveSheet.Range("A1").CopyFromRecordset rsData
* * Else
* * * * MsgBox "No data located.", vbCritical, "Error!"
* * End If

* * rsData.Close
* * cnAccess.Close
* * Set rsData = Nothing
* * Set cnAccess = Nothing

End Sub

HIH

On 4 Lis, 14:49, Przemyslaw Robak <Przemyslaw



wrote:
Hello!

Im using Excel 2007 file to store very large database: 600k rows & 40
columns in one spreadsheet. Its more than 250MB on hard drive.

Im using it to draw many charts. Usually I copy charts to PowerPoint 2007 to
get small file sieze.

I wonder if its possigle to transfer data from xlsx to another file, and use
it as external data source. My target is to get small xlxs files, without any
need to copy charts to PowerPoint 2007.

I was trying to put data into Access 2007 db, but.... System was crashing
during importing.

What is most efficient method of storing such a big database, to use it in
Excel 2007 ?

I hope that someone answer my question. Its first time I write on this
newsgroup... :)

regards, Przemek- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -

I suggest that Access is the right way to go - it was built to cope with
large numbers of records. Why not post your import problem on the Access
side of this forum? I guess, though, that you are hitting the maximum number
of record locks and that you will need to go into the registry to make a
change.

Helpful? Click, Yes

"Przemyslaw Robak" wrote:

Hello!

Im using Excel 2007 file to store very large database: 600k rows & 40
columns in one spreadsheet. Its more than 250MB on hard drive.

Im using it to draw many charts. Usually I copy charts to PowerPoint 2007 to
get small file sieze.

I wonder if its possigle to transfer data from xlsx to another file, and use
it as external data source. My target is to get small xlxs files, without any
need to copy charts to PowerPoint 2007.

I was trying to put data into Access 2007 db, but.... System was crashing
during importing.

What is most efficient method of storing such a big database, to use it in
Excel 2007 ?

I hope that someone answer my question. Its first time I write on this
newsgroup... :)

regards, Przemek