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
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