By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,656 Members | 1,027 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,656 IT Pros & Developers. It's quick & easy.

Exporting to Excel

P: n/a
Hi All,
In Access97 I have a table that's greater than 65k records and I'm
looking for a VBA way to export the records to Excel.

Anyone have vba code to export from access to excel and have the code
use multiple excel tabs within a workbook????

Anyone have vba code that would create a temp table write 65,000
records to it, export those to excel, clean the temp table, append the
next 65,000 records, export it to excel with a different name, clear
the temp table, etc............ til it gets all the records from the
source table exported ???

thanks
bobh.

Mar 20 '06 #1
Share this Question
Share on Google+
21 Replies


P: n/a
PCD
Bob,

Start by looking at the Transferspreadsheet method in the Help file.
DoCmd.TransferSpreadsheet........ can be used to export data from Access to
Excel.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1150 users have come to me from the newsgroups requesting help
re******@pcdatasheet.com

"bobh" <vu******@isp.com> wrote in message
news:11**********************@t31g2000cwb.googlegr oups.com...
Hi All,
In Access97 I have a table that's greater than 65k records and I'm
looking for a VBA way to export the records to Excel.

Anyone have vba code to export from access to excel and have the code
use multiple excel tabs within a workbook????

Anyone have vba code that would create a temp table write 65,000
records to it, export those to excel, clean the temp table, append the
next 65,000 records, export it to excel with a different name, clear
the temp table, etc............ til it gets all the records from the
source table exported ???

thanks
bobh.

Mar 20 '06 #2

P: n/a
PCD wrote:
Bob,

Start by looking at the Transferspreadsheet method in the Help file.
DoCmd.TransferSpreadsheet........ can be used to export data from Access to
Excel.


Steve, just a suggestion here - Why don't you occasionally try reading
the OP before posting your advertising.

--
To the original poster:

It is commonly accepted that these newsgroups are for free
exchange of information. Please be aware that PC Datasheet
is a notorious job hunter. If you are considering doing
business with him then I suggest that you take a look at
the link below first.

http://home.tiscali.nl/arracom/whoissteve.html

Randy Harris
Mar 20 '06 #3

P: n/a
bobh wrote:
Hi All,
In Access97 I have a table that's greater than 65k records and I'm
looking for a VBA way to export the records to Excel.

Anyone have vba code to export from access to excel and have the code
use multiple excel tabs within a workbook????

Anyone have vba code that would create a temp table write 65,000
records to it, export those to excel, clean the temp table, append the
next 65,000 records, export it to excel with a different name, clear
the temp table, etc............ til it gets all the records from the
source table exported ???

thanks
bobh.


Bob, I think you've really got two seperate issues here. Perhaps the
easier of the two is to send the data to multiple sheets in Excel. I
can supply some sample code to do that. I think, however, you first
need to figure out how to the break up the data into "chunks". You
could use the GetRows method to grab the first, say 50K rows, but I'm
not entirely sure how you would then get the next sets. The only
"obvious" method that occurs to me, would be to set up a giant recordset
and go through all of the records incrementally in a "brute force"
approach. I suspect that someone else will have a much more elegant
solution.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
Mar 20 '06 #4

P: n/a
PCD
Just a suggestion here -------

STOP making yourself look like an obsessed A#$&@# in front of all the
newsgroup readers!!!!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1150 users have come to me from the newsgroups requesting help
re******@pcdatasheet.com

"Please Stop Advertising" <pl****@no.spam> wrote in message
news:Jr****************@newssvr27.news.prodigy.net ...
PCD wrote:
Bob,

Start by looking at the Transferspreadsheet method in the Help file.
DoCmd.TransferSpreadsheet........ can be used to export data from Access
to Excel.


Steve, just a suggestion here - Why don't you occasionally try reading
the OP before posting your advertising.

--
To the original poster:

It is commonly accepted that these newsgroups are for free
exchange of information. Please be aware that PC Datasheet
is a notorious job hunter. If you are considering doing
business with him then I suggest that you take a look at
the link below first.

http://home.tiscali.nl/arracom/whoissteve.html

Randy Harris

Mar 20 '06 #5

P: n/a

"PCD" <no***@email.com> schreef in bericht news:KX*******************@newsread1.news.atl.eart hlink.net...
Just a suggestion here -------

STOP making yourself look like an obsessed A#$&@# in front of all the
newsgroup readers!!!!


Feeling lost and alone Steve??
Changed your nickname here again? ==>> Won't help that much....

We don't like you in this newsgroup. In fact *nobody* likes your behaviour here.
So why don't you just get lost (I mean STOP) with your advertising/jobhunting?

Arno R

Mar 21 '06 #6

P: n/a
"PCD" <no***@email.com> wrote in
news:KX*******************@newsread1.news.atl.eart hlink.net:
Just a suggestion here -------

STOP making yourself look like an obsessed A#$&@# in front of
all the newsgroup readers!!!!


Well Steve,YOU made an A$$ of yourself by firing off another of
your incorrect replies, because the original poster was quite clear
in that he eexceeded the 64Ki record limit that your solution
suffers.

As long as you persist in posting erroneous answers that show your
incompetence above your advertising which violates newsgroup rules,
you are gonna get deservedly spanked.
--
Bob Quintal

PA is y I've altered my email address.
Mar 21 '06 #7

P: n/a
"bobh" <vu******@isp.com> wrote in
news:11**********************@t31g2000cwb.googlegr oups.com:
Hi All,
In Access97 I have a table that's greater than 65k records and
I'm looking for a VBA way to export the records to Excel.

Anyone have vba code to export from access to excel and have
the code use multiple excel tabs within a workbook????

Anyone have vba code that would create a temp table write
65,000 records to it, export those to excel, clean the temp
table, append the next 65,000 records, export it to excel with
a different name, clear the temp table, etc............ til
it gets all the records from the source table exported ???

thanks
bobh.

start here: http://www.mvps.org/access/modules/mdl0006.htm

You can set up a loop that creates a new worksheet, writes the
first 64Ki records, creates a second worksheet, writes the next
64Ki rows, until .EOF
--
Bob Quintal

PA is y I've altered my email address.
Mar 21 '06 #8

P: n/a
PCD
YOU think you are SO SMART!!!

Well you have made an A$$ of yourself first by showing that you can not
spell!!

Then you made an A$$ of yourself by claiming my reply was incorrect! All the
OP has to do is set up a query that segregates the data in his table in
blocks of 64K records and use the query as the data source in a series of
DoCmd.TransferSpreadsheet .... statements or in a loop to put his data into
a series of worksheets in his workbook. This certainly will be faster than
your stupid recommendation of writing one field at a time to a cell in the
workbook!!!

You can kiss Arno R's A$$!!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1150 users have come to me from the newsgroups requesting help
re******@pcdatasheet.com
"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...
"PCD" <no***@email.com> wrote in
news:KX*******************@newsread1.news.atl.eart hlink.net:
Just a suggestion here -------

STOP making yourself look like an obsessed A#$&@# in front of
all the newsgroup readers!!!!


Well Steve,YOU made an A$$ of yourself by firing off another of
your incorrect replies, because the original poster was quite clear
in that he eexceeded the 64Ki record limit that your solution
suffers.

As long as you persist in posting erroneous answers that show your
incompetence above your advertising which violates newsgroup rules,
you are gonna get deservedly spanked.
--
Bob Quintal

PA is y I've altered my email address.

Mar 21 '06 #9

P: n/a
PCD
You're an obsessed A#$&@# !!! Why don't you just get lost. You don't
contribute anything to the newsgroup.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1150 users have come to me from the newsgroups requesting help
re******@pcdatasheet.com


"StopThisAdvertising" <StopThisAdvertising@DataShit> wrote in message
news:44**********************@text.nova.planet.nl. ..

"PCD" <no***@email.com> schreef in bericht
news:KX*******************@newsread1.news.atl.eart hlink.net...
Just a suggestion here -------

STOP making yourself look like an obsessed A#$&@# in front of all the
newsgroup readers!!!!


Feeling lost and alone Steve??
Changed your nickname here again? ==>> Won't help that much....

We don't like you in this newsgroup. In fact *nobody* likes your behaviour
here.
So why don't you just get lost (I mean STOP) with your
advertising/jobhunting?

Arno R
Mar 21 '06 #10

P: n/a
PCD wrote:
YOU think you are SO SMART!!!

Well you have made an A$$ of yourself first by showing that you can not
spell!!


I guess Steve figures that incorrect replies are OK, as long as you
spell them correctly.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
Mar 21 '06 #11

P: n/a
On 20 Mar 2006 12:55:59 -0800, "bobh" <vu******@isp.com> wrote:
Hi All,
In Access97 I have a table that's greater than 65k records and I'm
looking for a VBA way to export the records to Excel.

Anyone have vba code to export from access to excel and have the code
use multiple excel tabs within a workbook????

Anyone have vba code that would create a temp table write 65,000
records to it, export those to excel, clean the temp table, append the
next 65,000 records, export it to excel with a different name, clear
the temp table, etc............ til it gets all the records from the
source table exported ???

thanks
bobh.


Something like the following should work.
Paste the following code into a standard module.

Change all instances of "ID" in the following sub to the name of your field in
tblCheck.

Change all instances of tblTestData in the following sub to the name of the
table containing the data to be exported.

Create a single field table called tblCheck. The single field (ID) should be the
same name and datatype as the primary key of your data table.

Create a query called qryExportData and paste the following into it's SQL view.
(change tblTestData and ID to suit your table)

SELECT TOP 65000 tblTestData.*
FROM tblTestData LEFT JOIN tblCheck ON tblTestData.ID = tblCheck.ID
WHERE (((tblCheck.ID) Is Null))
ORDER BY tblTestData.ID;

*The ExportToExcel function has been posted here previously by Chuck Grimsby.

The sub will create an excel spreadsheet with multiple worksheets each
containing 65,000 records.
After each loop, the ID's exported will be appended to tblCheck. qryExportData
will then ensure that these ID's are excluded from subsequent loops.

'================================================= ====
Sub Export()
Dim i As Integer
Dim x As Integer
Dim lngTotalRecords As Long
Dim intLoops As Integer
Dim strFileName As String
Dim strSheetName As String
Dim strSQL As String
Dim rst As DAO.Recordset
Dim db As DAO.Database

'name of spreadsheet to create (change to suit)
strFileName = "C:\Documents and Settings\bestfit\my documents\test.xls"
Set db = CurrentDb()

'how many records do we have
strSQL = "SELECT Count(ID) AS TotalRecords FROM tblTestData;"
Set rst = db.OpenRecordset(strSQL)
With rst
If .RecordCount <> 0 Then
.MoveFirst
lngTotalRecords = !TotalRecords
Else
lngTotalRecords = 0
End If
.Close
End With
Set rst = Nothing

'calc number of spreadsheets required
If lngTotalRecords Mod 65000 = 0 Then
intLoops = lngTotalRecords / 65000
Else
intLoops = (lngTotalRecords \ 65000) + 1
End If

'clear temp table
strSQL = "DELETE * FROM tblCheck;"
db.Execute strSQL, dbFailOnError

For i = 1 To intLoops
x = x + 1
'create spreadsheet
strSheetName = "Export" & x
Call ExportToExcel(strFileName, strSheetName, "qryExportData", False)

'write exported IDs to tblCheck
strSQL = "INSERT INTO tblCheck ( ID ) SELECT ID FROM qryExportData;"
db.Execute strSQL, dbFailOnError
Next i

Set rst = Nothing
Set db = Nothing

End Sub
'================================================= ====

Function ExportToExcel(strFileName As String, _
strSheetName As String, _
strSourceName As String, _
Optional bolMsgBoxWhenDone _
As Boolean = False) _
As Long

' strFileName is the Excel File to Create (or use)
' strSheetName is the sheet within the Excel file to create
' strSourceName is the table, query, or SQL string
' to use as the source
' bolMsgBoxWhenDone: Want a msgbox saying "Done"?

Dim myXLDB As DAO.Database
Dim myXLTDF As DAO.TableDef
Dim myXLRst As DAO.Recordset
Dim myDB As DAO.Database
Dim myRst As DAO.Recordset
Dim i As Long
Dim lngRC As Long
Dim lngStatus As Long
Dim varStatus As Variant

'Excel 2000
Set myXLDB = DBEngine.OpenDatabase(strFileName, _
dbDriverNoPrompt, _
False, _
"Excel 8.0")

'Excel 97
'Set myXLDB = DBEngine.OpenDatabase(strFileName, _
dbDriverNoPrompt, _
False, _
"Excel 7.0")

Set myDB = CurrentDb
Set myRst = myDB.OpenRecordset(strSourceName)

Set myXLTDF = myXLDB.CreateTableDef(strSheetName)
For i = 0 To myRst.Fields.Count - 1
With myXLTDF
Select Case myRst.Fields(i).Properties("Type")
Case 1
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbBoolean)
Case 2
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbByte)
Case 3
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbInteger)
Case 4
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbLong)
Case 5
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbCurrency)
Case 6
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbSingle)
Case 7
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbDouble)
Case 8
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbDate)
Case 9
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbBinary)
Case 10
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbText)
Case 11
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbLongBinary)
Case 12
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbMemo)
Case 13, 14
' unknown field types.
' No idea what these are!
Case 15
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbGUID)
Case 16
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbBigInt)
Case 17
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbVarBinary)
Case 18
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbChar)
Case 19
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbNumeric)
Case 20
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbDecimal)
Case 21
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbFloat)
Case 22
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbTime)
Case 23
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbTimeStamp)
End Select
End With
Next i
myXLDB.TableDefs.Append myXLTDF
Set myXLTDF = Nothing
myXLDB.TableDefs.Refresh

Set myXLRst = myXLDB.OpenRecordset(strSheetName)
myRst.MoveLast
lngRC = myRst.RecordCount

varStatus = SysCmd(acSysCmdInitMeter, "Exporting Records", lngRC)
lngStatus = 1
varStatus = SysCmd(acSysCmdUpdateMeter, lngStatus)

myRst.MoveFirst
While Not myRst.EOF
lngStatus = lngStatus + 1
varStatus = SysCmd(acSysCmdUpdateMeter, lngStatus)
myXLRst.AddNew
For i = 0 To myRst.Fields.Count - 1
myXLRst.Fields(i) = Nz(myRst.Fields(i))
Next i
myXLRst.Update
myRst.MoveNext
Wend
varStatus = SysCmd(acSysCmdRemoveMeter)
myXLRst.Close
Set myXLRst = Nothing
ExportToExcel = myRst.RecordCount
myRst.Close
Set myRst = Nothing
myDB.Close
Set myDB = Nothing
myXLDB.Close
Set myXLDB = Nothing
If bolMsgBoxWhenDone = True Then
MsgBox "Done!", _
vbInformation + vbOKOnly, _
"Export To Excel"
End If
End Function

'================================================= ====

Wayne Gillespie
Gosford NSW Australia
Mar 21 '06 #12

P: n/a

"Wayne Gillespie" <be*****@NOhotmailSPAM.com.au> wrote in message
news:5j********************************@4ax.com...
On 20 Mar 2006 12:55:59 -0800, "bobh" <vu******@isp.com> wrote:
Hi All,
In Access97 I have a table that's greater than 65k records and I'm
looking for a VBA way to export the records to Excel.

Anyone have vba code to export from access to excel and have the code
use multiple excel tabs within a workbook????

Anyone have vba code that would create a temp table write 65,000
records to it, export those to excel, clean the temp table, append the
next 65,000 records, export it to excel with a different name, clear
the temp table, etc............ til it gets all the records from the
source table exported ???

thanks
bobh.


Something like the following should work.
Paste the following code into a standard module.

Change all instances of "ID" in the following sub to the name of your
field in
tblCheck.

Change all instances of tblTestData in the following sub to the name of
the
table containing the data to be exported.

Create a single field table called tblCheck. The single field (ID) should
be the
same name and datatype as the primary key of your data table.

Create a query called qryExportData and paste the following into it's SQL
view.
(change tblTestData and ID to suit your table)

SELECT TOP 65000 tblTestData.*
FROM tblTestData LEFT JOIN tblCheck ON tblTestData.ID = tblCheck.ID
WHERE (((tblCheck.ID) Is Null))
ORDER BY tblTestData.ID;

*The ExportToExcel function has been posted here previously by Chuck
Grimsby.

The sub will create an excel spreadsheet with multiple worksheets each
containing 65,000 records.
After each loop, the ID's exported will be appended to tblCheck.
qryExportData
will then ensure that these ID's are excluded from subsequent loops.

'================================================= ====
Sub Export()
Dim i As Integer
Dim x As Integer
Dim lngTotalRecords As Long
Dim intLoops As Integer
Dim strFileName As String
Dim strSheetName As String
Dim strSQL As String
Dim rst As DAO.Recordset
Dim db As DAO.Database

'name of spreadsheet to create (change to suit)
strFileName = "C:\Documents and Settings\bestfit\my documents\test.xls"
Set db = CurrentDb()

'how many records do we have
strSQL = "SELECT Count(ID) AS TotalRecords FROM tblTestData;"
Set rst = db.OpenRecordset(strSQL)
With rst
If .RecordCount <> 0 Then
.MoveFirst
lngTotalRecords = !TotalRecords
Else
lngTotalRecords = 0
End If
.Close
End With
Set rst = Nothing

'calc number of spreadsheets required
If lngTotalRecords Mod 65000 = 0 Then
intLoops = lngTotalRecords / 65000
Else
intLoops = (lngTotalRecords \ 65000) + 1
End If

'clear temp table
strSQL = "DELETE * FROM tblCheck;"
db.Execute strSQL, dbFailOnError

For i = 1 To intLoops
x = x + 1
'create spreadsheet
strSheetName = "Export" & x
Call ExportToExcel(strFileName, strSheetName, "qryExportData", False)

'write exported IDs to tblCheck
strSQL = "INSERT INTO tblCheck ( ID ) SELECT ID FROM qryExportData;"
db.Execute strSQL, dbFailOnError
Next i

Set rst = Nothing
Set db = Nothing

End Sub
'================================================= ====

Function ExportToExcel(strFileName As String, _
strSheetName As String, _
strSourceName As String, _
Optional bolMsgBoxWhenDone _
As Boolean = False) _
As Long

' strFileName is the Excel File to Create (or use)
' strSheetName is the sheet within the Excel file to create
' strSourceName is the table, query, or SQL string
' to use as the source
' bolMsgBoxWhenDone: Want a msgbox saying "Done"?

Dim myXLDB As DAO.Database
Dim myXLTDF As DAO.TableDef
Dim myXLRst As DAO.Recordset
Dim myDB As DAO.Database
Dim myRst As DAO.Recordset
Dim i As Long
Dim lngRC As Long
Dim lngStatus As Long
Dim varStatus As Variant

'Excel 2000
Set myXLDB = DBEngine.OpenDatabase(strFileName, _
dbDriverNoPrompt, _
False, _
"Excel 8.0")

'Excel 97
'Set myXLDB = DBEngine.OpenDatabase(strFileName, _
dbDriverNoPrompt, _
False, _
"Excel 7.0")

Set myDB = CurrentDb
Set myRst = myDB.OpenRecordset(strSourceName)

Set myXLTDF = myXLDB.CreateTableDef(strSheetName)
For i = 0 To myRst.Fields.Count - 1
With myXLTDF
Select Case myRst.Fields(i).Properties("Type")
Case 1
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbBoolean)
Case 2
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbByte)
Case 3
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbInteger)
Case 4
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbLong)
Case 5
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbCurrency)
Case 6
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbSingle)
Case 7
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbDouble)
Case 8
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbDate)
Case 9
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbBinary)
Case 10
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbText)
Case 11
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbLongBinary)
Case 12
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbMemo)
Case 13, 14
' unknown field types.
' No idea what these are!
Case 15
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbGUID)
Case 16
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbBigInt)
Case 17
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbVarBinary)
Case 18
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbChar)
Case 19
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbNumeric)
Case 20
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbDecimal)
Case 21
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbFloat)
Case 22
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbTime)
Case 23
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbTimeStamp)
End Select
End With
Next i
myXLDB.TableDefs.Append myXLTDF
Set myXLTDF = Nothing
myXLDB.TableDefs.Refresh

Set myXLRst = myXLDB.OpenRecordset(strSheetName)
myRst.MoveLast
lngRC = myRst.RecordCount

varStatus = SysCmd(acSysCmdInitMeter, "Exporting Records", lngRC)
lngStatus = 1
varStatus = SysCmd(acSysCmdUpdateMeter, lngStatus)

myRst.MoveFirst
While Not myRst.EOF
lngStatus = lngStatus + 1
varStatus = SysCmd(acSysCmdUpdateMeter, lngStatus)
myXLRst.AddNew
For i = 0 To myRst.Fields.Count - 1
myXLRst.Fields(i) = Nz(myRst.Fields(i))
Next i
myXLRst.Update
myRst.MoveNext
Wend
varStatus = SysCmd(acSysCmdRemoveMeter)
myXLRst.Close
Set myXLRst = Nothing
ExportToExcel = myRst.RecordCount
myRst.Close
Set myRst = Nothing
myDB.Close
Set myDB = Nothing
myXLDB.Close
Set myXLDB = Nothing
If bolMsgBoxWhenDone = True Then
MsgBox "Done!", _
vbInformation + vbOKOnly, _
"Export To Excel"
End If
End Function

'================================================= ====

Wayne Gillespie
Gosford NSW Australia




I think if I were asked to do this, I would generate a lookup table in code
by having two fields NewID an autonumber for counting purposes and OldID
which is the original ID from the table.

Then run something like:

INSERT INTO tblLookup ( OldID )
SELECT ID FROM MyTable
ORDER BY SomeColumn, AnotherColumn

I can then retrieve the RecordsAffected property to tell me how many records
I have and can split them into batches.

SELECT tblOriginal.*
FROM tblOriginal INNER JOIN tblLookup
ON tblOriginal.ID = tblLookup.OldID
WHERE tblLookup.NewID Between 1 And 1000

What makes this easy is that I can refer to absolute record numbers and
since I don't need to delete large amounts of data, nor do my queries need
any TOP criteria it should be fairly efficient. True, I need to do the
extra work creating the lookup table but with only 2 long integer columns it
should be fast. At least, I have a gut feeling that it should be fast.

Mar 21 '06 #13

P: n/a
Randy Harris <pl****@send.no.spam> wrote in
news:r7****************@newssvr11.news.prodigy.com :
PCD wrote:
YOU think you are SO SMART!!!

Well you have made an A$$ of yourself first by showing that
you can not spell!!


I guess Steve figures that incorrect replies are OK, as long
as you spell them correctly.

Besides, I can spell correctly, I just cannot type accurately.
--
Bob Quintal

PA is y I've altered my email address.
Mar 21 '06 #14

P: n/a
PCD
You also made a stupid recommendation of writing one field at a time to a
cell in the workbook!!!

PCD

"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...
Randy Harris <pl****@send.no.spam> wrote in
news:r7****************@newssvr11.news.prodigy.com :
PCD wrote:
YOU think you are SO SMART!!!

Well you have made an A$$ of yourself first by showing that
you can not spell!!


I guess Steve figures that incorrect replies are OK, as long
as you spell them correctly.

Besides, I can spell correctly, I just cannot type accurately.
--
Bob Quintal

PA is y I've altered my email address.

Mar 21 '06 #15

P: n/a
"PCD" <no***@email.com> wrote in
news:Qm*****************@newsread2.news.atl.earthl ink.net:
You also made a stupid recommendation of writing one field at
a time to a cell in the workbook!!!

PCD
Why do you say it's stupid? It is a perfectly valid programming
construct. It provides an easy method of overcoming the original
poster's problem.

How would you handle adding the required variable number of
worksheets and addressing them correctly in your multiple query
solution?

Put your money where your mouth is.

Q
"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...
Randy Harris <pl****@send.no.spam> wrote in
news:r7****************@newssvr11.news.prodigy.com :
PCD wrote:
YOU think you are SO SMART!!!

Well you have made an A$$ of yourself first by showing that
you can not spell!!
I guess Steve figures that incorrect replies are OK, as long
as you spell them correctly.

Besides, I can spell correctly, I just cannot type
accurately.
--
Bob Quintal

PA is y I've altered my email address.



--
Bob Quintal

PA is y I've altered my email address.
Mar 21 '06 #16

P: n/a
Something wrong with the exclamaton key again Steve??

-- newsgroup readers!!!!
-- A#$&@# !!!
-- SO SMART!!!
-- the workbook!!!
-- Arno R's A$$!!
-- the workbook!!!

These are *only* quotes from THIS thread ...
Shouldn't this be three or four exclamatons each time??

Note: I am using the question mark twice, since I really don't understand what you are moaning and groaning about ...

Arno R

PS: exclamaton was written false on purpose!!!
(I DID mean exclamation... really) ;-)

Arno R

"PCD" <no***@email.com> schreef in bericht news:Qm*****************@newsread2.news.atl.earthl ink.net...
You also made a stupid recommendation of writing one field at a time to a
cell in the workbook!!!

PCD

"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...
Randy Harris <pl****@send.no.spam> wrote in
news:r7****************@newssvr11.news.prodigy.com :
PCD wrote:
YOU think you are SO SMART!!!

Well you have made an A$$ of yourself first by showing that
you can not spell!!
I guess Steve figures that incorrect replies are OK, as long
as you spell them correctly.

Besides, I can spell correctly, I just cannot type accurately.
--
Bob Quintal

PA is y I've altered my email address.


Mar 21 '06 #17

P: n/a
PCD
OBSESSED A#$&@# s don't understand anything but their own obsessed small
mind!!! Why don't you just get lost in your own obsession!!!

PCD
"StopThisAdvertising" <StopThisAdvertising@DataShit> wrote in message
news:44**********************@text.nova.planet.nl. ..
Something wrong with the exclamaton key again Steve??

-- newsgroup readers!!!!
-- A#$&@# !!!
-- SO SMART!!!
-- the workbook!!!
-- Arno R's A$$!!
-- the workbook!!!

These are *only* quotes from THIS thread ...
Shouldn't this be three or four exclamatons each time??

Note: I am using the question mark twice, since I really don't understand
what you are moaning and groaning about ...

Arno R

PS: exclamaton was written false on purpose!!!
(I DID mean exclamation... really) ;-)

Arno R

"PCD" <no***@email.com> schreef in bericht
news:Qm*****************@newsread2.news.atl.earthl ink.net...
You also made a stupid recommendation of writing one field at a time to a
cell in the workbook!!!

PCD

"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...
Randy Harris <pl****@send.no.spam> wrote in
news:r7****************@newssvr11.news.prodigy.com :
PCD wrote:
YOU think you are SO SMART!!!

Well you have made an A$$ of yourself first by showing that
you can not spell!!
I guess Steve figures that incorrect replies are OK, as long
as you spell them correctly.

Besides, I can spell correctly, I just cannot type accurately.
--
Bob Quintal

PA is y I've altered my email address.


Mar 22 '06 #18

P: n/a
PCD
You can set up a loop that creates a new worksheet, writes the
first 64Ki (sic) records, creates a second worksheet, writes the next
64Ki (sic) rows, until .EOF!!!!!

Kiss Arno R's A$$!!!

PCD

"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...
"PCD" <no***@email.com> wrote in
news:Qm*****************@newsread2.news.atl.earthl ink.net:
You also made a stupid recommendation of writing one field at
a time to a cell in the workbook!!!

PCD

Why do you say it's stupid? It is a perfectly valid programming
construct. It provides an easy method of overcoming the original
poster's problem.

How would you handle adding the required variable number of
worksheets and addressing them correctly in your multiple query
solution?

Put your money where your mouth is.

Q
"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...
Randy Harris <pl****@send.no.spam> wrote in
news:r7****************@newssvr11.news.prodigy.com :

PCD wrote:
> YOU think you are SO SMART!!!
>
> Well you have made an A$$ of yourself first by showing that
> you can not spell!!
>

I guess Steve figures that incorrect replies are OK, as long
as you spell them correctly.

Besides, I can spell correctly, I just cannot type
accurately.
--
Bob Quintal

PA is y I've altered my email address.



--
Bob Quintal

PA is y I've altered my email address.

Mar 22 '06 #19

P: n/a
On Wed, 22 Mar 2006 00:36:57 +0100, "StopThisAdvertising"
<StopThisAdvertising@DataShit> wrote:
I thought that you were going to post a SINGLE reply to PCD's posts and then no
further followups.

What is happening now is that people are posting legitimate questions and their
threads are getting hijacked by this ridiculaous by-play between the warring
factions.

The followup posts are now becoming every bit as annoying as PCD's advertising.

Please post a single warning post per THREAD and DON'T BITE BACK.
It's now a fight to see who gets the last word.

If you must engage him please put it in a separate thread so that it can be
ignored by those of us who have heard it all before, and the original thread can
be confined to answering the OP's questions.
Wayne Gillespie
Gosford NSW Australia
Mar 22 '06 #20

P: n/a
Hi Wayne,

You are 100% right about this.
I guess I got 'carried away' a bit because of some other issue here concerning PCD.
So: I am sorry and I will try to stick to the single post per thread.

Arno R
"Wayne Gillespie" <be*****@NOhotmailSPAM.com.au> schreef in bericht news:ti********************************@4ax.com...
On Wed, 22 Mar 2006 00:36:57 +0100, "StopThisAdvertising"
<StopThisAdvertising@DataShit> wrote:


I thought that you were going to post a SINGLE reply to PCD's posts and then no
further followups.

What is happening now is that people are posting legitimate questions and their
threads are getting hijacked by this ridiculaous by-play between the warring
factions.

The followup posts are now becoming every bit as annoying as PCD's advertising.

Please post a single warning post per THREAD and DON'T BITE BACK.
It's now a fight to see who gets the last word.

If you must engage him please put it in a separate thread so that it can be
ignored by those of us who have heard it all before, and the original thread can
be confined to answering the OP's questions.


Wayne Gillespie
Gosford NSW Australia

Mar 22 '06 #21

P: n/a
Wayne,
Thanks for the code, I'll be using it.
bobh.

Mar 23 '06 #22

This discussion thread is closed

Replies have been disabled for this discussion.