Hi,
I have a table in Access that contains data that I need to export to Excel:
1. A query that selects only the data pertaining to Store 1 and exports it to excel as Store_1.xls.
2. The loop then uses the same query as in 1 to then export the data pertaining to Store 2 into excel as Store_2.xls
3. the loop continues until all of the stores in the table in access have had an excel report exported.
Thank you! Sorry I do not have that much experience with VBA and the project is due on Friday :(
Maria
The code worked as the following: - Sub TEST()
-
Dim db As DAO.Database
-
Dim rs1 As DAO.Recordset
-
Dim v As String
-
-
Set db = CurrentDb()
-
Set rs1 = db.OpenRecordset("Select Distinct Territory From TEST")
-
-
Dim strQry As String
-
Dim qdfTemp As DAO.QueryDef
-
Dim strQDF As String
-
strQDF = "_TempQuery_"
-
-
Do While Not rs1.EOF
-
v = rs1.Fields(0).Value
-
-
strQry = "SELECT * FROM TEST WHERE Territory = '" & v & "'"
-
-
Set qdfTemp = CurrentDb.CreateQueryDef(strQDF, strQry)
-
qdfTemp.Close
-
Set qdfTemp = Nothing
-
-
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
-
strQDF, "C:\Users\me\Desktop\VBA_TEST\" & v & ".xls", True
-
-
CurrentDb.QueryDefs.Delete strQDF
-
rs1.MoveNext
-
Loop
-
-
rs1.Close
-
-
End Sub
Thank you for your help Jim!
7 1422
Look at the TransferSpreadsheet method of DoCmd. That will be the key to this project.
Note that we're not a homework service here. We'll give you tips and point helpfully in the right direction.
Jim
Thank you Jim!
I have looked at the TransferSpreadsheet method and have that set. The problem that I am having is the looping.
Would you advice to create a query that lists the Stores with an ID that is numerical and then have the loop add 1 to the previous ID so that it keeps exporting that way?
I have found the loop but now I am stuck on the query to link the string v to: - Sub TEST()
-
-
Dim db As DAO.Database Dim rs1 As DAO.Recordset Dim v As String
-
-
Set db = CurrentDb() Set rs1 = db.OpenRecordset("Select Distinct Territory From TEST")
-
-
Do While Not rs1.EOF v = rs1.Fields(0).Value
-
-
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _ "WHAT SHOULD MY QUERY BE TO USE STRING v?", "C:\Users\me\Desktop\VBA_TEST\" & v & ".xls", True
-
-
rs1.MoveNext Loop
-
-
rs1.Close
-
-
End Sub
thank you!
Maria,
It could be something like this - dim strSQL as string
-
strSQL = "Select Distinct Territory From TEST"
or - strSQL = "Select Distinct Territory From TEST where [some fieldname]=" & numericvalue
or - strSQL = "Select Distinct Territory From TEST where [some alpha fieldname]='" & alphavalue & "'"
-
In your case that would be - strSQL = "Select Distinct Territory from TEST where [some alpha fieldname]='" & v & "'"
-
and then of course use strSQL as the parameter where the query string goes. - DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _ strSQL, C:\Users\me\Desktop\VBA_TEST\" & v & ".xls", True
"Select Distinct Territory from Test" is only a filler for representing your real query. You have mentioned stores 1 and 2 in your first post, as if the stores IDs are numeric in nature. But your v variable is a string; was that a conscious decision based on the actual store ID being defined as strings and not numerics (even if the strings are 1, 2, ...)? In other, words, is that first field in the table TEST an alpha or a numeric and is it the store ID? Assuming the answers are 'numeric' and 'yes' you can then figure out how to make your real query to access the store data; something like - strSQL = "Select * from STORETABLE where StoreID=" & v
or if it's an alpha store ID - strSQL = "Select * from STORETABLE where StoreID='" & v & "'"
Jim
I have opted to create a numeric ID called storekey and have updated the code to: - Sub TEST()
-
-
Dim db As DAO.Database
-
Dim rs1 As DAO.Recordset
-
Dim v As Integer
-
Dim strSQL As String
-
-
strSQL = "Select * from TEST where storekey=" & v
-
-
-
Set db = CurrentDb()
-
Set rs1 = db.OpenRecordset("Select Distinct storekey From TEST")
-
-
Do While Not rs1.EOF
-
v = rs1.Fields(0).Value
-
-
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, strSQL, "C:\Users\me\Desktop\VBA_TEST\" & v & ".xls", True
-
-
rs1.MoveNext
-
Loop
-
-
-
rs1.Close
-
-
End Sub
However, now I get runtime error 3011:
Run-Time error '3011':
The Microsoft Office Access Database engine could not find the object 'Select * from TEST where storekey=0'. Make sure the object exists and that you spell its name and path name correctly.
The code worked as the following: - Sub TEST()
-
Dim db As DAO.Database
-
Dim rs1 As DAO.Recordset
-
Dim v As String
-
-
Set db = CurrentDb()
-
Set rs1 = db.OpenRecordset("Select Distinct Territory From TEST")
-
-
Dim strQry As String
-
Dim qdfTemp As DAO.QueryDef
-
Dim strQDF As String
-
strQDF = "_TempQuery_"
-
-
Do While Not rs1.EOF
-
v = rs1.Fields(0).Value
-
-
strQry = "SELECT * FROM TEST WHERE Territory = '" & v & "'"
-
-
Set qdfTemp = CurrentDb.CreateQueryDef(strQDF, strQry)
-
qdfTemp.Close
-
Set qdfTemp = Nothing
-
-
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
-
strQDF, "C:\Users\me\Desktop\VBA_TEST\" & v & ".xls", True
-
-
CurrentDb.QueryDefs.Delete strQDF
-
rs1.MoveNext
-
Loop
-
-
rs1.Close
-
-
End Sub
Thank you for your help Jim!
Oh, oh, right, you can only use saved queries when you're working with TransferSpreadsheet. My bad! Glad you got it worked out, Maria. Good job!!
Jim
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Jen |
last post by:
Trying to take one table in access and split it into multiple excel
files(using an excel template); and then email based on email
addresses in Table2; Of course, I would like to do all of this...
|
by: hstockbridge5 |
last post by:
Hi,
I am trying to link many Excel files into a database with no luck.
Here is the code:
'==============================================
Sub LinkExcelFiles()
Dim fso 'FileSystemObject
Dim...
|
by: Anne |
last post by:
hie, in my web application, i have a dropdownlist, and a
button. i want to be able to select from the list, and
whatever item that i have selected, i want to use the item
value for manipulation....
|
by: theodorej |
last post by:
......how do I select multiple Excel files to import into Access?
Any insights would be deeply appreciated.
|
by: kkadakia |
last post by:
I get a daily excel file for a entire month which I want to transfer into Access at the end of the month. So, there are around 20-25 excel files I get by the end of the month, and I would like to...
|
by: Stephenoja |
last post by:
Hello Guys,
I have a challenge here and would really appreciate some help. I have customer bills that are all done in excel with a template and stored in directories by month order. At the end...
|
by: ciaran.hudson |
last post by:
Hi
I have multiple excel files of the same format in a directory.
They are called book1.xls, book2.xls, book3.xls and so on.
What is the easiest way to import the tab named sheet1 from each of...
|
by: ivancycheng |
last post by:
I have around 400 excel files with same format (multiple sheets) and wants to import few sheets from excel to MS Access (XP version) for further processing. any one can advise me how to do it? (I'm...
|
by: stephen |
last post by:
Hi,
I have 5 excel files and they have multiple sheets. I have to read (say
sheet 3) of each of the 5 excel files and consolidate them into one. what's
the best way to achieve this. if someone...
|
by: zizi2 |
last post by:
Hi,
how do I output multiple excel files from one source using vbscript?
Regards,
Noluthando
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |