Transfer Spreadsheet -Export Multiple Files

  • Thread starter Thread starter Bonnie A
  • Start date Start date
B

Bonnie A

Hi everyone! Using A02 on XP. I am not a programmer but can do some
expressions and limited VB. I found some code that will parse out records
and export to multiple files based on a query that lists the [GRPID]s. My
original table may have thousands of records and there are about 800 contract
numbers [GRPID]. I am trying to export all the records split into separate
files and named the [GRPID] plus the date. I did get it to work except that
I am getting all the records in the table in each file even though I am
getting a file for each [GRPID]. My button says Call ExportFileForEachGP and
it works. I just have to tell it to export only the records where [GRPID] is
the same into each file.

Here is my code:

Function ExportFileForEachGP()

Dim strSQL As String 'this variable will hold the SQL
statement that gets the data we want to show in our Excel Workbook
Dim GRPID As Integer 'this variable stores the 'current'
GRPID that we are exporting data for
Dim rs As DAO.Recordset 'declare a recordset
Dim db As DAO.Database 'declare a database
Set db = CurrentDb 'set the database object to the
current database
'Set rs = db.QueryDefs("qListGPs") 'set the recordset object to your
query within the current database
Set rs = db.OpenRecordset("qListGPs")
rs.MoveFirst 'move to the first record in the
recordset
Do Until rs.EOF 'begin loop, do the items within the
loop until the End Of File is reached
GRPID = rs("GRPID") ' set local variable GRPID to the
current record's GRPID within the recordset
strSQL = "Select * from ""tVtgAssetAcctBals"" WHERE GRPID = " & GRPID
'Create an SQL statement that will get all of the data from our table
which we want to parse to Excel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"tVtgAssetAcctBals", "S:\rps\PTS\VtgAssetAcctBalExtract\ExtractedFiles\" &
[GRPID] & "_" & Format(Date, "MM-DD-YY") & ".XLS"
rs.MoveNext 'move to the next record in the
recordset
Loop 'loop back to 'Do Until rs.EOF'
rs.Close 'cleanup
db.Close
Set rs = Nothing
Set db = Nothing
End Function

My guess is I need a query. I found this but I don't know what to do with it.

Function CreateQuery(strDatabase As String, strName As String, strSQL As
String) As Boolean
' Comments : Saves a SQL string as a query in the database
' Parameters: strDatabase - path and name of database to look in or ""
(blank string) for the current database
' strName - name of the new query
' strSQL - SQL string of the new query
' Returns : True - the query was created, False - the query was not created
'
Dim db As DAO.Database
Dim qdfTemp As QueryDef

On Error GoTo err_CreateQuery

Set db = CurrentDb()

Set qdfTemp = db.CreateQueryDef()

qdfTemp.Name = strName
qdfTemp.SQL = strSQL

db.QueryDefs.Append qdfTemp

CreateQuery = True

exit_CreateQuery:
db.Close
Set db = Nothing
Exit Function

err_CreateQuery:
CreateQuery = False
Resume exit_CreateQuery

End Function

It seems the query name is supposed to be strName but I really do not know
what or where to do it. I tried to call this function on the button by
changing it to Call CreateQuery but got a compile error - Argument not
optional. I have no clue if I copied the right piece.

I would really appreciate any help or advice you can provide. This project
will save almost 5 hours of work each quarter manually parsing out each batch
of records by contract number.

Thank you in advance for your time and any assistance!

Bonnie
 
See this article for the basic code to create separate EXCEL files:

Create a Query and Export multiple "filtered" versions of a Query (based on
data in another table) to separate EXCEL files via TransferSpreadsheet (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#FilterExportSepFiles

--

Ken Snell
http://www.accessmvp.com/KDSnell/



Bonnie A said:
Hi everyone! Using A02 on XP. I am not a programmer but can do some
expressions and limited VB. I found some code that will parse out records
and export to multiple files based on a query that lists the [GRPID]s. My
original table may have thousands of records and there are about 800
contract
numbers [GRPID]. I am trying to export all the records split into
separate
files and named the [GRPID] plus the date. I did get it to work except
that
I am getting all the records in the table in each file even though I am
getting a file for each [GRPID]. My button says Call ExportFileForEachGP
and
it works. I just have to tell it to export only the records where [GRPID]
is
the same into each file.

Here is my code:

Function ExportFileForEachGP()

Dim strSQL As String 'this variable will hold the SQL
statement that gets the data we want to show in our Excel Workbook
Dim GRPID As Integer 'this variable stores the
'current'
GRPID that we are exporting data for
Dim rs As DAO.Recordset 'declare a recordset
Dim db As DAO.Database 'declare a database
Set db = CurrentDb 'set the database object to the
current database
'Set rs = db.QueryDefs("qListGPs") 'set the recordset object to your
query within the current database
Set rs = db.OpenRecordset("qListGPs")
rs.MoveFirst 'move to the first record in the
recordset
Do Until rs.EOF 'begin loop, do the items within
the
loop until the End Of File is reached
GRPID = rs("GRPID") ' set local variable GRPID to the
current record's GRPID within the recordset
strSQL = "Select * from ""tVtgAssetAcctBals"" WHERE GRPID = " & GRPID
'Create an SQL statement that will get all of the data from our table
which we want to parse to Excel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"tVtgAssetAcctBals", "S:\rps\PTS\VtgAssetAcctBalExtract\ExtractedFiles\" &
[GRPID] & "_" & Format(Date, "MM-DD-YY") & ".XLS"
rs.MoveNext 'move to the next record in the
recordset
Loop 'loop back to 'Do Until rs.EOF'
rs.Close 'cleanup
db.Close
Set rs = Nothing
Set db = Nothing
End Function

My guess is I need a query. I found this but I don't know what to do with
it.

Function CreateQuery(strDatabase As String, strName As String, strSQL As
String) As Boolean
' Comments : Saves a SQL string as a query in the database
' Parameters: strDatabase - path and name of database to look in or ""
(blank string) for the current database
' strName - name of the new query
' strSQL - SQL string of the new query
' Returns : True - the query was created, False - the query was not
created
'
Dim db As DAO.Database
Dim qdfTemp As QueryDef

On Error GoTo err_CreateQuery

Set db = CurrentDb()

Set qdfTemp = db.CreateQueryDef()

qdfTemp.Name = strName
qdfTemp.SQL = strSQL

db.QueryDefs.Append qdfTemp

CreateQuery = True

exit_CreateQuery:
db.Close
Set db = Nothing
Exit Function

err_CreateQuery:
CreateQuery = False
Resume exit_CreateQuery

End Function

It seems the query name is supposed to be strName but I really do not know
what or where to do it. I tried to call this function on the button by
changing it to Call CreateQuery but got a compile error - Argument not
optional. I have no clue if I copied the right piece.

I would really appreciate any help or advice you can provide. This
project
will save almost 5 hours of work each quarter manually parsing out each
batch
of records by contract number.

Thank you in advance for your time and any assistance!

Bonnie
Click to expand...
 
From just a cursory look the following jumped out at me:

YOURS:
GRPID = rs("GRPID")

MINE WHEN I'VE DONE SIMILAR THINGS:
GRPID = rs!GRPID

I don't know what yours actually stores in the GRPID variable, but
apparently it's not the individual GRPID values from your dataset.




Bonnie A said:
Hi everyone! Using A02 on XP. I am not a programmer but can do some
expressions and limited VB. I found some code that will parse out records
and export to multiple files based on a query that lists the [GRPID]s. My
original table may have thousands of records and there are about 800 contract
numbers [GRPID]. I am trying to export all the records split into separate
files and named the [GRPID] plus the date. I did get it to work except that
I am getting all the records in the table in each file even though I am
getting a file for each [GRPID]. My button says Call ExportFileForEachGP and
it works. I just have to tell it to export only the records where [GRPID] is
the same into each file.

Here is my code:

Function ExportFileForEachGP()

Dim strSQL As String 'this variable will hold the SQL
statement that gets the data we want to show in our Excel Workbook
Dim GRPID As Integer 'this variable stores the 'current'
GRPID that we are exporting data for
Dim rs As DAO.Recordset 'declare a recordset
Dim db As DAO.Database 'declare a database
Set db = CurrentDb 'set the database object to the
current database
'Set rs = db.QueryDefs("qListGPs") 'set the recordset object to your
query within the current database
Set rs = db.OpenRecordset("qListGPs")
rs.MoveFirst 'move to the first record in the
recordset
Do Until rs.EOF 'begin loop, do the items within the
loop until the End Of File is reached
GRPID = rs("GRPID") ' set local variable GRPID to the
current record's GRPID within the recordset
strSQL = "Select * from ""tVtgAssetAcctBals"" WHERE GRPID = " & GRPID
'Create an SQL statement that will get all of the data from our table
which we want to parse to Excel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"tVtgAssetAcctBals", "S:\rps\PTS\VtgAssetAcctBalExtract\ExtractedFiles\" &
[GRPID] & "_" & Format(Date, "MM-DD-YY") & ".XLS"
rs.MoveNext 'move to the next record in the
recordset
Loop 'loop back to 'Do Until rs.EOF'
rs.Close 'cleanup
db.Close
Set rs = Nothing
Set db = Nothing
End Function

My guess is I need a query. I found this but I don't know what to do with it.

Function CreateQuery(strDatabase As String, strName As String, strSQL As
String) As Boolean
' Comments : Saves a SQL string as a query in the database
' Parameters: strDatabase - path and name of database to look in or ""
(blank string) for the current database
' strName - name of the new query
' strSQL - SQL string of the new query
' Returns : True - the query was created, False - the query was not created
'
Dim db As DAO.Database
Dim qdfTemp As QueryDef

On Error GoTo err_CreateQuery

Set db = CurrentDb()

Set qdfTemp = db.CreateQueryDef()

qdfTemp.Name = strName
qdfTemp.SQL = strSQL

db.QueryDefs.Append qdfTemp

CreateQuery = True

exit_CreateQuery:
db.Close
Set db = Nothing
Exit Function

err_CreateQuery:
CreateQuery = False
Resume exit_CreateQuery

End Function

It seems the query name is supposed to be strName but I really do not know
what or where to do it. I tried to call this function on the button by
changing it to Call CreateQuery but got a compile error - Argument not
optional. I have no clue if I copied the right piece.

I would really appreciate any help or advice you can provide. This project
will save almost 5 hours of work each quarter manually parsing out each batch
of records by contract number.

Thank you in advance for your time and any assistance!

Bonnie
Click to expand...
 
Hello, hello and thank you, thank you, thank you!!!

I had to play with the line breaks and wasn't sure where to put it but ended
up pasting into the module I was creating unsuccessfully and then calling it
from a button.

OMG! I cannot tell you how much I appreciate your post!

Thank you tons!!!!!
--
Bonnie W. Anderson
Cincinnati, OH


orange via AccessMonster.com said:
Bonnie said:
Hi everyone! Using A02 on XP. I am not a programmer but can do some
expressions and limited VB. I found some code that will parse out records
and export to multiple files based on a query that lists the [GRPID]s. My
original table may have thousands of records and there are about 800 contract
numbers [GRPID]. I am trying to export all the records split into separate
files and named the [GRPID] plus the date. I did get it to work except that
I am getting all the records in the table in each file even though I am
getting a file for each [GRPID]. My button says Call ExportFileForEachGP and
it works. I just have to tell it to export only the records where [GRPID] is
the same into each file.

Here is my code:

Function ExportFileForEachGP()

Dim strSQL As String 'this variable will hold the SQL
statement that gets the data we want to show in our Excel Workbook
Dim GRPID As Integer 'this variable stores the 'current'
GRPID that we are exporting data for
Dim rs As DAO.Recordset 'declare a recordset
Dim db As DAO.Database 'declare a database
Set db = CurrentDb 'set the database object to the
current database
'Set rs = db.QueryDefs("qListGPs") 'set the recordset object to your
query within the current database
Set rs = db.OpenRecordset("qListGPs")
rs.MoveFirst 'move to the first record in the
recordset
Do Until rs.EOF 'begin loop, do the items within the
loop until the End Of File is reached
GRPID = rs("GRPID") ' set local variable GRPID to the
current record's GRPID within the recordset
strSQL = "Select * from ""tVtgAssetAcctBals"" WHERE GRPID = " & GRPID
'Create an SQL statement that will get all of the data from our table
which we want to parse to Excel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"tVtgAssetAcctBals", "S:\rps\PTS\VtgAssetAcctBalExtract\ExtractedFiles\" &
[GRPID] & "_" & Format(Date, "MM-DD-YY") & ".XLS"
rs.MoveNext 'move to the next record in the
recordset
Loop 'loop back to 'Do Until rs.EOF'
rs.Close 'cleanup
db.Close
Set rs = Nothing
Set db = Nothing
End Function

My guess is I need a query. I found this but I don't know what to do with it.

Function CreateQuery(strDatabase As String, strName As String, strSQL As
String) As Boolean
' Comments : Saves a SQL string as a query in the database
' Parameters: strDatabase - path and name of database to look in or ""
(blank string) for the current database
' strName - name of the new query
' strSQL - SQL string of the new query
' Returns : True - the query was created, False - the query was not created
'
Dim db As DAO.Database
Dim qdfTemp As QueryDef

On Error GoTo err_CreateQuery

Set db = CurrentDb()

Set qdfTemp = db.CreateQueryDef()

qdfTemp.Name = strName
qdfTemp.SQL = strSQL

db.QueryDefs.Append qdfTemp

CreateQuery = True

exit_CreateQuery:
db.Close
Set db = Nothing
Exit Function

err_CreateQuery:
CreateQuery = False
Resume exit_CreateQuery

End Function

It seems the query name is supposed to be strName but I really do not know
what or where to do it. I tried to call this function on the button by
changing it to Call CreateQuery but got a compile error - Argument not
optional. I have no clue if I copied the right piece.

I would really appreciate any help or advice you can provide. This project
will save almost 5 hours of work each quarter manually parsing out each batch
of records by contract number.

Thank you in advance for your time and any assistance!

Bonnie
Click to expand...
Here's routine that should do what you want. I tried to simplify the code to
use
1 recordset GRPS and a utility query named Tempqry.
Basically, for each GRPID, create a query to gather all records with that
GRPID;
then transfer the data produced by the query to your folder as an xls file
with your specified name.
Repeat the process until all GRPIDs have been processed.

Code: 
'-----------------------------------------------------------------------
----------------
' Procedure : ExportDataForEachGRPID
' Author    :
' Created   : 2/5/2010
' Purpose   : To identify the unique GRPIDs and
' to export all records for each GRPID to a separate
' xls file named as "GRPID"_DD_MM_YY.xls in
' a specified folder.
'-----------------------------------------------------------------------------
----------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'-----------------------------------------------------------------------------
-
'
Sub ExportDataForEachGRPID()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim GRPs As DAO.Recordset
Dim Recs As DAO.Recordset
On Error GoTo ExportDataForEachGRPID_Error

Set db = CurrentDb
'**
'Create a temporary query to be used
Set qdf = db.CreateQueryDef("tempqry", "Select Count(*) from
tVtgAssetAcctBals")
Set GRPs = db.OpenRecordset("Select Distinct [GRPID] from tVtgAssetAcctBals")
'**

Do While Not GRPs.EOF
'**Delete the temporary query, we'll recreate it as necessary for our use.
DoCmd.DeleteObject acQuery, "tempqry"
'** Get the unique GRPIDs into GRPs by altering the sql
strSQL = "Select * from tVtgAssetAcctBals  WHERE [GRPID] = " & GRPs!GRPID

'**Recreate tempqry to get required records with GRPID = Grps!GRPID
Set qdf = db.CreateQueryDef("tempqry", strSQL)
'***
' Transfer the query output to an xls file
'***
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"tempqry", "S:\rps\PTS\VtgAssetAcctBalExtract\ExtractedFiles\" _
& GRPs!GRPID & "_" & Format(Date, "MM-DD-YY") & ".XLS"
GRPs.MoveNext             'move to the next record in the GRPs recordset
Loop                              'loop back and get the next GRPID
GRPs.Close                   'cleanup
db.Close
Set rs = Nothing
Set db = Nothing

On Error GoTo 0
Exit Sub

ExportDataForEachGRPID_Error:

MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure
ExportDataForEachGRPID "
End Sub

--
Message posted via AccessMonster.com


.
Click to expand...
 
Back
Top