UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Export To Excel With Specific Field, Access 2003    
 
   
mr.siro
post Jun 2 2018, 09:38 PM
Post#1



Posts: 56
Joined: 27-January 18



hello guys,
I have function copy from recordset export to excell:
Dim rs As DAO.Recordset, fld As DAO.Field
Dim Exl As Object, Wbook As Object, Wsheet As Object
Dim i As Integer
Set rs = frm.RecordsetClone
Set Exl = CreateObject("excel.application")
Set Wbook = Exl.workbooks.Add
Set Wsheet = Wbook.sheets(1)
For i = 0 To rs.Fields.Count - 1
Exl.Cells(1, i + 1).value = rs.Fields(i).Name
Next i
Wsheet.Range("a2").CopyFromRecordset rs
Exl.Visible = True
Set Wbook = Nothing
Set Wsheet = Nothing
Set Exl = Nothing
rs.Close
Set rs = Nothing
It's work ok, but it's export all fied in recordset.
Now, i'm want to export fieds which i want.
EX: recorset have 10 field, i want to export field 1-9.
Can you help me.
Go to the top of the page
 
Phil_cattivocara...
post Jun 3 2018, 01:39 AM
Post#2



Posts: 189
Joined: 2-April 18



QUOTE
Now, i'm want to export fieds which i want.
As projecttoday wrote here Show Column Header When Export Recordset To Excel, Access 2003 #10 you can use MaxColumns argument.
Now you know you want fields from 1 to 9 (in a recordset.fields collection these are fields with index from 0 to 8, but here you can ignore this), but you could evalute the flexibility you want to give to your export procedure. If you need to export fields not starting from the first or "jumping" (1st to 3rd and 5th to 10th)? With this you loose the "speed" (in writing code and not only) that RecorsetClone and CopyFromRecordset MaxColumns argument give.

If you want to give a look to this: [Access] Definire i Campi da esportare in EXCEL - [Access] Defining fields to export to Excel

Yes, the usual italian page! It's not my fault if @Alex made all you can do (or quite all) with Access. Forms GUI is italian but it is very intuitive: you can choose fields and their order, Excel file name and path, even in main form - subform situation.
This post has been edited by Phil_cattivocarattere: Jun 3 2018, 01:41 AM

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
mr.siro
post Jun 3 2018, 03:44 AM
Post#3



Posts: 56
Joined: 27-January 18



Hi phil, i just want: recordset have 10 field, i want export 1-8, or 1-9, or 3-10, or 4-10. Not want 1-3 and 6-9.
The fields i dont want to export, i will give it end of recordset or begin of recordset.
If the fields which i dont want export are 1,2,3. So, i export 4-10
If the fields which i dont want export are 6-10. So, i export 1-5.
This is what i want.
This post has been edited by mr.siro: Jun 3 2018, 03:51 AM
Go to the top of the page
 
Phil_cattivocara...
post Jun 3 2018, 04:01 AM
Post#4



Posts: 189
Joined: 2-April 18



If the fields you do not want to export are the last in the recordset.fields collection (last n fields) follow what projecttoday wrote, this is the easiest situation.
If the fields are at the beginning you have to act as I wrote in my #2 (using @Alex example if you want). I do not use to write full code, forgive me.

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
WildBird
post Jun 3 2018, 07:31 PM
Post#5


UtterAccess VIP
Posts: 3,360
Joined: 19-August 03
From: Perth, Australia


Why dont you just have a query, that way you can simply select the fields you want.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    24th June 2018 - 12:33 AM