Need to export multiple files

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

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


Douglas J. Steele said:
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)


Bonnie A said:
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
Click to expand...


.
Click to expand...
 
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/


Bonnie A said:
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


Douglas J. Steele said:
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)


Bonnie A said:
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
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top