Export Specific Columns To Excel

C

cutun1217

New member

Local time

Tomorrow, 05:47

Joined

Aug 24, 2006

Messages

8

• Aug 24, 2006

• #1

hi everybody, im have a database with table called "project". there are many column in this table. my user want to export this table to Excel, but only some of column, with particular order ( depend on him) to analyze in Excel.
he asked me to build a form with a list box, drop box,somthing like this, so he can choose what column to export in what order.
i try to make a query like this: " Select Forms!UserInput.combobox1.value , Forms!UserInput.combobox2.value,etc, From Project" but it wont work.
Dou you have any idea.
thanks in advance

 

clerics

Registered User.

Local time

Tomorrow, 06:47

Joined

Nov 28, 2005

Messages

82

• Aug 24, 2006

• #2

your code must be
Dim strSQL as String
StrSQL = " Select " & Forms!UserInput.combobox1 & ", " & Forms!UserInput.combobox2 & " From Project"
But make sure combobox1 's data_type is set as text !

Export to excel
this an example 's code (there re more complex ways but this one is simple)
DoCmd.TransferSpreadsheet acExport,, "query_name", "C:\xyz.xls"

 

R

Rich

Guest

• Aug 24, 2006

• #3

http://www.access-programmers.co.uk/forums/showthread.php?t=113385

 

C

cutun1217

New member

Local time

Tomorrow, 05:47

Joined

Aug 24, 2006

Messages

8

• Aug 24, 2006

• #4

My full code is like this:

Private Sub Command4_Click()
On Error GoTo Err_Command4_Click

Dim strSQL As String

strSQL = " Select " & Forms!UserInput.Combo0 & ", " &Forms!UserInput.Combo2 & " From Project"

DoCmd.TransferSpreadsheet acExport, , "strSQL", "C:\project.xls"

Exit_Command4_Click:
Exit Sub

Err_Command4_Click:
MsgBox Err.Description
Resume Exit_Command4_Click

End Sub
i received error: MS Jet database coundnt find the object 'strSQL' ...

how to set combobox data type as TEXT? is it default? sorry, i just a newbie, just start learning.
Thanks a lot

 

C

cutun1217

New member

Local time

Tomorrow, 05:47

Joined

Aug 24, 2006

Messages

8

• Aug 24, 2006

• #5

Thanks, Rich. but i still havent solved the problem

 

clerics

Registered User.

Local time

Tomorrow, 06:47

Joined

Nov 28, 2005

Messages

82

• Aug 24, 2006

• #6

i try to make a query like this: " Select Forms!UserInput.combobox1.value , Forms!UserInput.combobox2.value,etc, From Project" but it wont work.

Click to expand...

to correct you at this i suggest :
StrSQL = " Select " & Forms!UserInput.combobox1 & ", " & Forms!UserInput.combobox2 & " From Project"

But

DoCmd.TransferSpreadsheet acExport, , "strSQL", "C:\project.xls"

Click to expand...

this one "strSQL is SQL code (you must change it to query by CreateQueryDef() Or create a new query that have the same SQL

What i mean is you should have to create a query then use this one
DoCmd.TransferSpreadsheet acExport,, "query_name", "C:\xyz.xls"

Or you can take a look in this writting of Ghudson
http://www.access-programmers.co.uk/forums/showthread.php?t=84119

 

Last edited: Aug 24, 2006

Mile-O

Back once again...

Local time

Today, 22:47

Joined

Dec 10, 2002

Messages

11,316

• Aug 24, 2006

• #7

What Rich is saying, is please don't post the same query in mutliple areas. Another thing to note is that a more descriptive thread title of your problem is better than telling people you need help urgently. One, because most people here need help in one way or another so it's taken as granted that's what your thread will be about. And two, because the use of urgent makes it sound like you expect people to drop what they are doing and help you. This is a free forum, people don't get paid to advise you, thus you will get help as and when someone decides to.

When it's urgent to you, it's just another post on a busy board to everyone else.

And when posting code, learn to use the CODE tags - it makes reading VBA doodlings much easier.

 

Last edited: Aug 24, 2006

C

cutun1217

New member

Local time

Tomorrow, 05:47

Joined

Aug 24, 2006

Messages

8

• Aug 25, 2006

• #8

To Cleric: Thanks a lot. i will play around with it.
to McAbney: Im sorry for inconvenience. just little bit underpressure to finish the task on time and i dont know much about Access. i will follow all the rule here. thanks a lot.

 

C

cutun1217

New member

Local time

Tomorrow, 05:47

Joined

Aug 24, 2006

Messages

8

• Aug 25, 2006

• #9

I got it. Thanks you so much all of you guys. This forum is really like GOLD for me.

 

C

cutun1217

New member

Local time

Tomorrow, 05:47

Joined

Aug 24, 2006

Messages

8

• Aug 29, 2006

• #10

below is my code
if i use .QueryDef.Delete, it will cause error "Item not found in this collection" ( because nothing to delete )

however, if i remove it, it successfully execute 1st time, from the 2nd time, an error display: "Object newQuery already exists"

i want the function will check whether newQuey exist or not then delete it( using If) , but i have some problem with the syntax, still trail n error. do you guys have any idea?

thanks a lot

Code:

Private Sub Command4_Click()
On Error GoTo Err_Command4_Click
'this function will export part of the table to Excel file based on
'bug1:
'if using .QueryDef.Delete, error at the first time use
Dim db As DAO.Database
Dim qdf As QueryDef
Dim strSQL As String

Set db = CurrentDb()

strSQL = "Select distinct " & Forms!UserInput.Combo0 & ", " & Forms!UserInput.Combo2 & " From Project"

With db
[COLOR="Red"].QueryDefs.Delete ("newQuery")[/COLOR]
Set qdf = db.CreateQueryDef("newQuery", strSQL)
.Close
End With

DoCmd.TransferSpreadsheet acExport, , "newQuery", "C:\xyz.xls"
MsgBox ("Data has been exported successfully")
Exit_Command4_Click:

    Exit Sub

Err_Command4_Click:
    MsgBox Err.Description
    Resume Exit_Command4_Click
    
End Sub