 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Importing / Linking / August 2006Export to MS Excel
HI experts, Appreciate if someone can help me on this. Is there any fastest way to export a table to multiple excel files. I got one field name codes eg 100....250. Can someone show me how to export data with code 100 to 100.xls.....code 250 to 250.xls. I dont know how to this. Please guide or possibly give me a sample to do this. tq. You can use the Transferspreadsheet method. Create a select query that will filter on your code. Then in the Transferspreadsheet, identify the query as the tablename and and use the code you are filtering on as the filename. You may want to consider putting them in a macro so you can do them all at one time. And, with any luck, one of the "experts" may respond to your question :) > HI experts, > [quoted text clipped - 6 lines] > > tq. Klatuu's concept is the correct way to go. This can best and most effectively be done via VBA code. It's a pretty standard thing to do. Post back if you need specific details.  SignatureKen Snell <MS ACCESS MVP> > You can use the Transferspreadsheet method. Create a select query that > will [quoted text clipped - 21 lines] >> >> tq. Ken, I would use code, also, but I expect GW is not a VBA programmer. A good way to move from the evil (IMHO) Macro is to create the macro, then convert it to code. That way you get an understanding of what the code is doing. > Klatuu's concept is the correct way to go. This can best and most > effectively be done via VBA code. It's a pretty standard thing to do. Post [quoted text clipped - 24 lines] > >> > >> tq. Hmm... sometimes yes, sometimes no. Often, the wizard that creates the code from a macro generates very old code, which can be confusing when you try to compare it to the "more modern" code. And that generated code can be a bit klunky. From my own personal experience, I found that the best way to learn ACCESS VBA code was to plunge right in and not convert macros to code. SignatureKen Snell <MS ACCESS MVP> > Ken, > I would use code, also, but I expect GW is not a VBA programmer. A good [quoted text clipped - 36 lines] >> >> >> >> tq. Ken, You have a valid point. Most code generators write klunky code. The only one I rememeber that did a pretty good job was the one that was included with Foxpro 2.?. In the good ole' DOS days, it came with a screen builder that the code generator used to write the code for you. It was not bad. The interesting part was that the code generator was written in Foxpro, so you could modify the code generator to your own liking. The value with the conversion of Macro to VBA (klunky code aside) is that for a complete novice who has no concept of how coding works, it is, at least as starting point. Life is easier now. My first programming job, with only experience as an operator (tape ape) I was given a COBOL reference manual, a large stack of punch cards and instructions on what my first program was required to do. When I finally got it working, and watched it turn the tape drives to do a history update, I was totally hooked. Pardon my waxing nostalgic. Sort of sounds like "In my day we didn't have school buses, we had to walk to school in the snow and it was up hill both ways." > Hmm... sometimes yes, sometimes no. Often, the wizard that creates the code > from a macro generates very old code, which can be confusing when you try to [quoted text clipped - 42 lines] > >> >> > >> >> tq. > Ken, > > Pardon my waxing nostalgic. Sort of sounds like "In my day we didn't have > school buses, we had to walk to school in the snow and it was up hill both > ways." A quick Google search of the newsgroups will find many, 'nostalgic' posts from people's experiences! I will spare everyone mine.... < g > SignatureKen Snell <MS ACCESS MVP> Hi experts, Actually, appreciate if you could show me how write the code for the problem (the looping proces). I can create a query or a macro but what if there are thousand of group of data in that particular fields and the data is not consistent eg 100...120,130...150,160...250. I want to export the data to excel based on all group of data in that particular field at one time eg. for 100 to 100.xls....250 to 250.xls. pls guide me.. tq. > > Ken, > > [quoted text clipped - 4 lines] > A quick Google search of the newsgroups will find many, 'nostalgic' posts > from people's experiences! I will spare everyone mine.... < g > Tell us more about the query and data structures so that we can suggest meaningful examples. Also show us an example of the data records. SignatureKen Snell <MS ACCESS MVP> > Hi experts, > [quoted text clipped - 22 lines] >> A quick Google search of the newsgroups will find many, 'nostalgic' posts >> from people's experiences! I will spare everyone mine.... < g > Hi Ken, I've to export the data one by one based on br_code value to respective .xls. Query : Select * from CP where [br_code]="2803" ...........Select * from CP where [br_code]="3206" one by one group Below are required info.. tq so much. DataType: text|text|text|datetime|NumericDouble|text|NumericLonginteger|NumericDouble FieldName: name|acct_no|br_code|agrt_date|net_os|lawyer_code|app_age|app_gr_inc Data: CLARK|500000192290|2803|18/11/1997 0:00:00|-41.25|PAULC|39|0.00 JONATHAN|500000396191|3206|24/10/1997 0:00:00|0.00|LIMBP|32|0.00 MARTHA|500000709513|3302|2/3/1996 0:00:00|0.00|KADIR|38|0.00 LEX|502000145638|2803|16/5/2000 0:00:00|0.00|GANES|49|4500.00 LIONEL|504000186532|3301|7/3/2001 0:00:00|0.00|YAACO|34|1500.00 LANA|504000283954|3301|20/4/2001 0:00:00|0.00||31|2000.00 CHLOE|504001629429|3208|6/8/2003 0:00:00|0.00||| LOIS|504001675332|3201|2/9/2003 0:00:00|0.00|HARIS|51|0.00 JASON|504001787879|2906|31/10/2003 0:00:00|21.50||56|26000.00 KALEL|502000150669|2806|20/5/2000 0:00:00|32.42|ROSNA|6|0.00 JOREL|504000379688|3206|8/6/2001 0:00:00|62.30|ARMAN|41|2617.50 KENT|504000704187|2905|27/12/2001 0:00:00|132.95|HARCH|15|0.00 GW|504000704217|2905|27/12/2001 0:00:00|132.95||15|0.00 KEN|505000000783|2905|22/12/2001 0:00:00|158.90|SOBRI|15|0.00 MARK|502000413887|3401|1/11/2000 0:00:00|222.00|FERN|46|2970.71 MIKE|504000978643|3201|13/6/2002 0:00:00|241.57||42|2717.00 > Tell us more about the query and data structures so that we can suggest > meaningful examples. Also show us an example of the data records. [quoted text clipped - 24 lines] > >> A quick Google search of the newsgroups will find many, 'nostalgic' posts > >> from people's experiences! I will spare everyone mine.... < g > GW, First, you question about the number of [br_code]s you need to deal with. The easiest way to do that is to create a group by select query that returns only the br_code. This should give you a record set that contains one occurance of each br_code in your table. We will call that qselBrCodes. Then you will need a query based on your table with one parameter which is br_code. It should be layed out the way you want it to be in Excel. We will call that qselBrData. Here is the basic looping logic for that: Set qdf = CurrentDb.QueryDefs("qselBrCodes") Set rstBrCodes = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly) 'Be sure there are records to process If rstBrCodes.Recordcount = 0 Then MsgBox "No Data Found For This Report", vbInformation + vbOKOnly, _ "Data Error" Exit Do Else rstBrCodes.MoveLast rstBrCodes.MoveFirst End If Do While Not rstBrCodes.EOF Set qdf = CurrentDb.QueryDefs("qselBrData") qdf.Parameters(0) = rstBrCodes.[br_code] Set rstBrData = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly) If rstBrData.Recordcount > 0 Then strXLFileName = "MyPathj" & rstBrCodes.[br_code] & ".xls" DoCmd.TransferSpreadsheet acImport, 8, _ "qselBrData", strXLFileName, True End If rstBrData.Close rstBrCodes.MoveNext Loop rstBrCodes.Close set rstBrCodes = Nothing set rstBrData = Nothing set qdf = Nothing Please be aware this is untested Air Code. > Hi Ken, > I've to export the data one by one based on br_code value to respective .xls. [quoted text clipped - 55 lines] > > >> A quick Google search of the newsgroups will find many, 'nostalgic' posts > > >> from people's experiences! I will spare everyone mine.... < g > Hi Klatuu, Why line2 openRecordset return an error method or data member not found. help me pls. > GW, > [quoted text clipped - 98 lines] > > > >> A quick Google search of the newsgroups will find many, 'nostalgic' posts > > > >> from people's experiences! I will spare everyone mine.... < g > GW, I really don't know. The code looks ok, but without your system in front of me to play with it, I can't tell. I know it works for me because what I did was copy it from one of my modules and changed the names. Are there any parameters in your query? Don't dispare, we can work it out, we just have to experiment with it. By the way, what version of Access are you on. I am on 2000. Try removing the arguments from the statement. Set rstBrCodes = qdf.OpenRecordset Did you put Dim statements in your code Dim qdf as Querydef Dim rstBrCodes as Recordset Let me know how it works out. If I see anything in the meantime, I will get back to you. > Hi Klatuu, > [quoted text clipped - 103 lines] > > > > >> A quick Google search of the newsgroups will find many, 'nostalgic' posts > > > > >> from people's experiences! I will spare everyone mine.... < g > Perhaps his database has no reference set to DAO library? SignatureKen Snell <MS ACCESS MVP> > GW, > I really don't know. The code looks ok, but without your system in front > of > me to play with it, I can't tell. Possible, but wouldn't this happen for other DAO objects already? I'm wondering if perhaps he has an naming error he hasn't discovered yet. Do you see any problems with the code I posted for him? > Perhaps his database has no reference set to DAO library? > > > GW, > > I really don't know. The code looks ok, but without your system in front > > of > > me to play with it, I can't tell. I don't see any compiling problem with the first few lines (on which he says he's erroring), but I do note that this step qdf.Parameters(0) = rstBrCodes.[br_code] is better written as qdf.Parameters(0) = rstBrCodes![br_code] with similar changes throughout the code. Also, this step DoCmd.TransferSpreadsheet acImport, 8, _ "qselBrData", strXLFileName, True is missing the "export specification" argument: DoCmd.TransferSpreadsheet acImport, "ExportSpecName", _ 8, "qselBrData", strXLFileName, True SignatureKen Snell <MS ACCESS MVP> > Possible, but wouldn't this happen for other DAO objects already? I'm > wondering if perhaps he has an naming error he hasn't discovered yet. [quoted text clipped - 8 lines] >> > of >> > me to play with it, I can't tell. Ken, You are correct on your first part, but in the TransferSpreadsheet, unless there is an undocumented feature, or you are using something newer that 2000 and I am not familiar with anything newer, TransferSpreadsheet has no specification name. Are you perhaps thinking of TransferText? In either case, this is an import, not an export. > I don't see any compiling problem with the first few lines (on which he says > he's erroring), but I do note that this step [quoted text clipped - 25 lines] > >> > of > >> > me to play with it, I can't tell. > Ken, > You are correct on your first part, but in the TransferSpreadsheet, unless [quoted text clipped - 3 lines] > specification name. Are you perhaps thinking of TransferText? In either > case, this is an import, not an export. My apology... that's what I get for multitasking today....< g > TransferSpreadsheet doesn't have a specification argument. My *big* error! Thanks. SignatureKen Snell <MS ACCESS MVP> Not a *big* error. We all do it. I see your posts out here frequently and you always have good advice. > > Ken, > > You are correct on your first part, but in the TransferSpreadsheet, unless [quoted text clipped - 8 lines] > TransferSpreadsheet doesn't have a specification argument. My *big* error! > Thanks. Thank you :-) SignatureKen Snell <MS ACCESS MVP> > Not a *big* error. We all do it. > I see your posts out here frequently and you always have good advice. Hi Klatuu, Have a look at it, Public Sub exporting() 'FYI, I'm using MS Access 2002 'Before I add DAO library, I get so many errors, but not now. 'Fine after I added DAO 3.6 Lib 'After trying so many changes & I remove all the dim 'Dim db As Database, Dim qdf As QueryDefs, Dim rstbrcodes, 'rstbrdata As Recordset, Set db = CurrentDb 'I managed to compile this module but come to run the module 'I got run time error object not found for this line " 'qdf.Parameters(0) = rstbrdata![app_sys_code] Set qdf = CurrentDb.QueryDefs("qselapp_sys_code") '"qselapp_sys_code" is the query name for "select * from prov group by app_sys_code Set rstbrcodes = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly) 'Be sure there are records to process If rstbrcodes.RecordCount = 0 Then MsgBox "No Data Found For This Report", vbInformation + vbOKOnly, _ "Data Error" 'Exit Do 'Exit Do give me an error too. Else rstbrcodes.MoveLast rstbrcodes.MoveFirst End If Do While Not rstbrcodes.EOF Set qdf = CurrentDb.QueryDefs("qselapp_sys_data") 'qselapp_sys_data is the query name for "Select * from prov where app_sys_code="AMC" '"AMC" is one of the app_sys_code group of data" qdf.Parameters(0) = rstbrdata![app_sys_code] Set rstbrdata = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly) If rstbrdata.RecordCount > 0 Then strXLFileName = "MyPathj" & rstbrcodes.[app_sys_code] & ".xls" DoCmd.TransferSpreadsheet acImport, "ExportSpecName", _ 8, "qselBrData", strXLFileName, True End If rstbrdata.Close rstbrcodes.MoveNext Loop rstbrcodes.Close Set rstbrcodes = Nothing Set rstbrdata = Nothing Set qdf = Nothing End Sub > GW, > I really don't know. The code looks ok, but without your system in front of [quoted text clipped - 119 lines] > > > > > >> A quick Google search of the newsgroups will find many, 'nostalgic' posts > > > > > >> from people's experiences! I will spare everyone mine.... < g > Error for this line is object not found as per my reply earlier qdf.Parameters(0) = rstbrdata![app_sys_code] but actually it was object required > GW, > I really don't know. The code looks ok, but without your system in front of [quoted text clipped - 119 lines] > > > > > >> A quick Google search of the newsgroups will find many, 'nostalgic' posts > > > > > >> from people's experiences! I will spare everyone mine.... < g > Hi, GW, Did you ever get this to work? I need to do same thing; export training class rosters held each day to separate excel spreadsheets. The classes change, so I created a training code query to identify variables (i.e., all classes & dates held in a given month). I also need to export to excel templates containing header info. that refers to data exported to each sheet (i.e., course title, date and trainer name must appear at the top of each roster). I used code below & got following message: Run time error 3265 Item Not Found in this Collection Code stops at line: qdf.Parameters(0) = qryRptTrngClasses![Group] PLEASE HELP -PETRA >Error for this line is object not found as per my reply earlier >qdf.Parameters(0) = rstbrdata![app_sys_code] [quoted text clipped - 5 lines] >> > > > > >> A quick Google search of the newsgroups will find many, 'nostalgic' posts >> > > > > >> from people's experiences! I will spare everyone mine.... < g > |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.