Need to export multiple files

[Bonnie A]

Hi everyone, it's been awhile since I've been able to work with Access.
Using A02 on XP.

I have an extract file that I've imported to Access. The first field is
[GRPID] (the field is a number - I used Integer since it will never be more
than a whole number from 1 - 9999). I need to export Excel files, one for
each [GRPID] and name it [GRPID].xls.

I have a query, qtVtgAssetAcctBals, that pulls all the records I need. It
is sorted by [GRPID]. I have 26 records for '2', 13 records for '1137', 1666
records for '2543', etc. Lots of records. I would like to export the 26
records for '2' to a file named 2.xls, the 13 records to 1137.xls, etc.
There are over 200,000 records that I need to filter each quarter for this
project. Normally, we open the file and cut and paste each [GRPID] section.
There are over 500 [GRPID]'s. You can see why I want to make this better!
It would save HOURS every quarter.

I have a macro built using the 'Output To' Action. Object type is Query,
Object name is qtVtgAssetAcctBals, Output Format is Microsoft Excel (*.xls),
Output file now reads
S:\rps\PTS\VtgAssetAcctBalExtract\ExtractedFiles\[GRPID].xls

I am not sure how to write [GRPID] so the file will be named. I think I'm
close but no where near the cigar yet. Can you assist?

Thank you for your time and, hopefully, assistance.

Bonnie

 

[Douglas J. Steele]

Take a look at what Ken Snell has at
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm, specifically at
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#FilterExportSepFiles

 

[Bonnie A]

Hi Mr. Steele,

Thank you very much for the pointer. It appears to be exactly what I need.
The problem is that I don't understand parts of it.

I pasted and tried to adapt to my names but got confused with the strMgr and
rstMgr items. I don't know which parts to edit or not. It also didn't work
until I put a function name in. (Please let that tell you that I am not a
programmer so don't assume I know anything.) I also did not understand the
'create a temp query...'. Name it what? Have what in it? I have a query
that is pulling a list of just the 'unique values' - qListGPs. My table is
tVtgAssetAcctBals and my field is [GRPID]. I want to download to Excel files
named [GRPID] (a number field) plus the date. Here is what I have so far:

Function MakeFiles()

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstGRPID As DAO.Recordset
Dim strSQL As String, strTemp As String, strGRPID As String

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and field
names
' with the real names of the EmployeesTable table and the ManagerID field
strSQL = "SELECT DISTINCT GRPID FROM tVtgAssetAcctBals;"
Set rstGRPID = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of ManagerID values and create a query for each
ManagerID
' so that the data can be exported -- the code assumes that the actual names
' of the managers are in a lookup table -- again, replace generic names with
' real names of tables and fields
If rstGRPID.EOF = False And rstGRPID.BOF = False Then
rstGRPID.MoveFirst
Do While rstGRPID.EOF = False

' *** code to set strGRPID needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strGRPID = DLookup("GRPID", "tVtgAssetAcctBals", _
"ManagerID = " & rstGRPID!GRPID.Value)

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM tVtgAssetAcctBals WHERE " & _
"GRPID = " & rstGRPID!GRPID.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strGRPID
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "S:\RPS\" & strGRPID & Format(Now(), _
"ddMMMyyy_hhnn") & ".xls"
rstGRPID.MoveNext
Loop
End If

rstGRPID.Close
Set rstGRPID = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing


End Function

The rstGRPID and strGRPID confused me completely. I'm sure I've messed it
up royally. I made the replacements where I could but need a bit more
guidance.

Thank you very much for your time and assistance!
--
Bonnie W. Anderson
Cincinnati, OH

Take a look at what Ken Snell has at
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm, specifically at
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#FilterExportSepFiles

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi everyone, it's been awhile since I've been able to work with Access.
Using A02 on XP.

I have an extract file that I've imported to Access. The first field is
[GRPID] (the field is a number - I used Integer since it will never be
more
than a whole number from 1 - 9999). I need to export Excel files, one for
each [GRPID] and name it [GRPID].xls.

I have a query, qtVtgAssetAcctBals, that pulls all the records I need. It
is sorted by [GRPID]. I have 26 records for '2', 13 records for '1137',
1666
records for '2543', etc. Lots of records. I would like to export the 26
records for '2' to a file named 2.xls, the 13 records to 1137.xls, etc.
There are over 200,000 records that I need to filter each quarter for this
project. Normally, we open the file and cut and paste each [GRPID]
section.
There are over 500 [GRPID]'s. You can see why I want to make this better!
It would save HOURS every quarter.

I have a macro built using the 'Output To' Action. Object type is Query,
Object name is qtVtgAssetAcctBals, Output Format is Microsoft Excel
(*.xls),
Output file now reads
S:\rps\PTS\VtgAssetAcctBalExtract\ExtractedFiles\[GRPID].xls

I am not sure how to write [GRPID] so the file will be named. I think I'm
close but no where near the cigar yet. Can you assist?

Thank you for your time and, hopefully, assistance.

Bonnie

.

 

[Ken Snell]

Try this code:

Function MakeFiles()

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstGRPID As DAO.Recordset
Dim strSQL As String, strTemp As String, strGRPID As String

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and field
names
' with the real names of the EmployeesTable table and the ManagerID field
strSQL = "SELECT DISTINCT GRPID FROM tVtgAssetAcctBals;"
Set rstGRPID = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of ManagerID values and create a query for each
ManagerID
' so that the data can be exported -- the code assumes that the actual names
' of the managers are in a lookup table -- again, replace generic names with
' real names of tables and fields
If rstGRPID.EOF = False And rstGRPID.BOF = False Then
rstGRPID.MoveFirst
Do While rstGRPID.EOF = False

' *** code to set strGRPID needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strGRPID = rstGRPID!GRPID.Value

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM tVtgAssetAcctBals WHERE " & _
"GRPID = " & rstGRPID!GRPID.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strGRPID
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "S:\RPS\" & strGRPID & Format(Now(), _
"\_ddMMMyyy\_hhnn") & ".xls"
rstGRPID.MoveNext
Loop
End If

rstGRPID.Close
Set rstGRPID = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing


End Function


--

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

Hi Mr. Steele,

Thank you very much for the pointer. It appears to be exactly what I
need.
The problem is that I don't understand parts of it.

I pasted and tried to adapt to my names but got confused with the strMgr
and
rstMgr items. I don't know which parts to edit or not. It also didn't
work
until I put a function name in. (Please let that tell you that I am not a
programmer so don't assume I know anything.) I also did not understand
the
'create a temp query...'. Name it what? Have what in it? I have a query
that is pulling a list of just the 'unique values' - qListGPs. My table
is
tVtgAssetAcctBals and my field is [GRPID]. I want to download to Excel
files
named [GRPID] (a number field) plus the date. Here is what I have so far:

Function MakeFiles()

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstGRPID As DAO.Recordset
Dim strSQL As String, strTemp As String, strGRPID As String

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and field
names
' with the real names of the EmployeesTable table and the ManagerID field
strSQL = "SELECT DISTINCT GRPID FROM tVtgAssetAcctBals;"
Set rstGRPID = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of ManagerID values and create a query for each
ManagerID
' so that the data can be exported -- the code assumes that the actual
names
' of the managers are in a lookup table -- again, replace generic names
with
' real names of tables and fields
If rstGRPID.EOF = False And rstGRPID.BOF = False Then
rstGRPID.MoveFirst
Do While rstGRPID.EOF = False

' *** code to set strGRPID needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strGRPID = DLookup("GRPID", "tVtgAssetAcctBals", _
"ManagerID = " & rstGRPID!GRPID.Value)

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM tVtgAssetAcctBals WHERE " & _
"GRPID = " & rstGRPID!GRPID.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strGRPID
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "S:\RPS\" & strGRPID & Format(Now(), _
"ddMMMyyy_hhnn") & ".xls"
rstGRPID.MoveNext
Loop
End If

rstGRPID.Close
Set rstGRPID = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing


End Function

The rstGRPID and strGRPID confused me completely. I'm sure I've messed it
up royally. I made the replacements where I could but need a bit more
guidance.

Thank you very much for your time and assistance!
--
Bonnie W. Anderson
Cincinnati, OH

Take a look at what Ken Snell has at
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm, specifically at
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#FilterExportSepFiles

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi everyone, it's been awhile since I've been able to work with Access.
Using A02 on XP.

I have an extract file that I've imported to Access. The first field
is
[GRPID] (the field is a number - I used Integer since it will never be
more
than a whole number from 1 - 9999). I need to export Excel files, one
for
each [GRPID] and name it [GRPID].xls.

I have a query, qtVtgAssetAcctBals, that pulls all the records I need.
It
is sorted by [GRPID]. I have 26 records for '2', 13 records for
'1137',
1666
records for '2543', etc. Lots of records. I would like to export the
26
records for '2' to a file named 2.xls, the 13 records to 1137.xls,
etc.
There are over 200,000 records that I need to filter each quarter for
this
project. Normally, we open the file and cut and paste each [GRPID]
section.
There are over 500 [GRPID]'s. You can see why I want to make this
better!
It would save HOURS every quarter.

I have a macro built using the 'Output To' Action. Object type is
Query,
Object name is qtVtgAssetAcctBals, Output Format is Microsoft Excel
(*.xls),
Output file now reads
S:\rps\PTS\VtgAssetAcctBalExtract\ExtractedFiles\[GRPID].xls

I am not sure how to write [GRPID] so the file will be named. I think
I'm
close but no where near the cigar yet. Can you assist?

Thank you for your time and, hopefully, assistance.

Bonnie

.