SQL statement result exported to Excel

[Nick]

I'm trying to write some vb code in access that takes a 20+ checkboxes form
and returns any checked columns from a table into an excel spreadsheet. I
got this far with at least the correct SQL statement.

'MY CODE SO FAR:
Private Sub Command87_Click()

Dim a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x
Dim selection As String
Dim SQL As String

If Partnercbox = True Then a = "Partner,"
If Locationcbox = True Then b = "Location,"
If Trackingcbox = True Then c = "Track,"
If ProjectSummarycbox = True Then d = "ProjectSummary,"
If SatisfyActcbox = True Then e = "SatisfyAct,"
If Voicecbox = True Then f = "Voice,"
If Datacbox = True Then g = "Data,"
If ECTemployeecbox = True Then h = "ECTEmployee,"
If Assistancecbox = True Then i = "Assistance,"
If Companycbox = True Then j = "Company,"
If Contactcbox = True Then k = "Contact,"
If Emailcbox = True Then l = "Email,"
If EndDatecbox = True Then m = "EndDate,"
If StartDatecbox = True Then n = "StartDate,"
If LastUpdateDatecbox = True Then o = "LastUpdateDate,"
If Phonecbox = True Then p = "Phone,"
If Requestcbox = True Then q = "Request,"
If Statecbox = True Then r = "State,"
If Statuscbox = True Then s = "Status,"
If StatusDesccbox = True Then t = "StatusDesc,"
If Techcbox = True Then u = "Tech,"
If TechDesccbox = True Then v = "TechDesc,"
If Productcbox = True Then w = "Product,"
x = "end"

selection = a & b & c & d & e & f & g & h & i & j & k & l & m & n & o & p &
q & r & s & t & u & v & w & x

If a = "" And b = "" And c = "" And d = "" And e = "" And f = "" And g = ""
And h = "" And j = "" And k = "" And l = "" And m = "" And n = "" And o = ""
And p = "" And q = "" And r = "" And s = "" And t = "" And u = "" And v = ""
And w = "" Then
MsgBox ("You must make at least one selection to Export")

Else:

'DoCmd.OutputTo acQuery, "qryExport", "MicrosoftExcel(*.xls)", "", True, "", 0

SQL = "SELECT " & selection & " FROM tbl-ECTprojects"
MsgBox (SQL)
'Returns the value "SELECT partner,location,end FROM tbl-ECTprojects" when
first two are checked which is good

DoCmd.RunSQL (SQL)

'DoCmd.RunSQL SQL, acCmdQueryAddToOutput, "qryExport", "MicrosoftExcel(*.xls)"

End If

End Sub 'End all code


Where I have my issue now is how to take that SQL statement and send the
result to a spreadsheet. I have no idea what to do and perhaps there is a
much better way to do this. I appreicate any help someone can give me.
Thanks in advance!

 

[Allen Browne]

Okay, the end of the story is that you use TransferSpreadsheet to export
data in Excel format.

TransferSpreadsheet exports a table or query, so you need a saved query to
export. You could mock one up, and save it as (say) "Query1". Once your code
has created the SQL statement, you can assign it to the SQL property of the
saved query, and then export the query:
dbEngine(0)(0).QueryDefs("Query1").SQL = SQL
DoCmd.TransferSpreadsheet acExport,,"Query1", "C:\MyFile.xls"

You might find it easier to use a multi-select list box so the user can
click the fields they want for the export. The list box would have
properties:
Row Source Type Value List
Row Source "Partner", "Location", "Track", "ProjectSummary",
....
Multi Select Simple

You can then loop through the selected items in the list box to build the
field list for the SELECT clause like this:
Dim varItem As Variant 'Selected items
Dim strList As String 'String to use as WhereCondition
Dim lngLen As Long 'Length of string

'Loop through the ItemsSelected in the list box.
With Me.[NameOfYourListBoxHere]
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strList = strList & .ItemData(varItem) & ", "
End If
Next
End With

'Chop off the trailing separator.
lngLen = Len(strList) - 2
If lngLen <= 0 Then
MsgBox "No fields selected"
Else
dbEngine(0)(0).QueryDefs("Query1").SQL = _
"SELECT " & Left$(strList, lngLen) & " FROM tbl-ECTprojects;"
DoCmd.TransferSpreadsheet acExport,,"Query1", "C:\MyFile.xls"
End If

You can also offer buttons for "Select All" and "Deselect All" if that
helps. The code is here:
http://allenbrowne.com/func-12.html

Hope that helps.

 

[Nick]

I agree with your option but unfoutunately for me the client likes the
checkboxes and I still have to use them. However, I still need some more
parameters and code to export my results. Here's what I have so far from the
ELSE statement:

Else:

SQL = "SELECT " & selection & " FROM tbl-ECTprojects"
'MsgBox (SQL)
DBEngine(0)(0).QueryDefs("qryExport").SQL = SQL
DoCmd.TransferSpreadsheet acExport, , "qryExport", "C:\ECTprojects.xls"

DoCmd.RunMacro "mcr-Exportformclose"

End If


Unfoutunately as it stands now, this code errors out. Does the query have
to be designed a certain way? What parameters do I need forDBEngine,
QueryDefs, and TransferSpreadsheet? Also why did you pick to store the
result of the QueryDefs back into the variable SQL? I apologize for being an
inexperienced VB programmer.

Okay, the end of the story is that you use TransferSpreadsheet to export
data in Excel format.

TransferSpreadsheet exports a table or query, so you need a saved query to
export. You could mock one up, and save it as (say) "Query1". Once your code
has created the SQL statement, you can assign it to the SQL property of the
saved query, and then export the query:
dbEngine(0)(0).QueryDefs("Query1").SQL = SQL
DoCmd.TransferSpreadsheet acExport,,"Query1", "C:\MyFile.xls"

You might find it easier to use a multi-select list box so the user can
click the fields they want for the export. The list box would have
properties:
Row Source Type Value List
Row Source "Partner", "Location", "Track", "ProjectSummary",
....
Multi Select Simple

You can then loop through the selected items in the list box to build the
field list for the SELECT clause like this:
Dim varItem As Variant 'Selected items
Dim strList As String 'String to use as WhereCondition
Dim lngLen As Long 'Length of string

'Loop through the ItemsSelected in the list box.
With Me.[NameOfYourListBoxHere]
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strList = strList & .ItemData(varItem) & ", "
End If
Next
End With

'Chop off the trailing separator.
lngLen = Len(strList) - 2
If lngLen <= 0 Then
MsgBox "No fields selected"
Else
dbEngine(0)(0).QueryDefs("Query1").SQL = _
"SELECT " & Left$(strList, lngLen) & " FROM tbl-ECTprojects;"
DoCmd.TransferSpreadsheet acExport,,"Query1", "C:\MyFile.xls"
End If

You can also offer buttons for "Select All" and "Deselect All" if that
helps. The code is here:
http://allenbrowne.com/func-12.html

Hope that helps.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I'm trying to write some vb code in access that takes a 20+ checkboxes
form
and returns any checked columns from a table into an excel spreadsheet. I
got this far with at least the correct SQL statement.

'MY CODE SO FAR:
Private Sub Command87_Click()

Dim a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x
Dim selection As String
Dim SQL As String

If Partnercbox = True Then a = "Partner,"
If Locationcbox = True Then b = "Location,"
If Trackingcbox = True Then c = "Track,"
If ProjectSummarycbox = True Then d = "ProjectSummary,"
If SatisfyActcbox = True Then e = "SatisfyAct,"
If Voicecbox = True Then f = "Voice,"
If Datacbox = True Then g = "Data,"
If ECTemployeecbox = True Then h = "ECTEmployee,"
If Assistancecbox = True Then i = "Assistance,"
If Companycbox = True Then j = "Company,"
If Contactcbox = True Then k = "Contact,"
If Emailcbox = True Then l = "Email,"
If EndDatecbox = True Then m = "EndDate,"
If StartDatecbox = True Then n = "StartDate,"
If LastUpdateDatecbox = True Then o = "LastUpdateDate,"
If Phonecbox = True Then p = "Phone,"
If Requestcbox = True Then q = "Request,"
If Statecbox = True Then r = "State,"
If Statuscbox = True Then s = "Status,"
If StatusDesccbox = True Then t = "StatusDesc,"
If Techcbox = True Then u = "Tech,"
If TechDesccbox = True Then v = "TechDesc,"
If Productcbox = True Then w = "Product,"
x = "end"

selection = a & b & c & d & e & f & g & h & i & j & k & l & m & n & o & p
&
q & r & s & t & u & v & w & x

If a = "" And b = "" And c = "" And d = "" And e = "" And f = "" And g =
""
And h = "" And j = "" And k = "" And l = "" And m = "" And n = "" And o =
""
And p = "" And q = "" And r = "" And s = "" And t = "" And u = "" And v =
""
And w = "" Then
MsgBox ("You must make at least one selection to Export")

Else:

'DoCmd.OutputTo acQuery, "qryExport", "MicrosoftExcel(*.xls)", "", True,
"", 0

SQL = "SELECT " & selection & " FROM tbl-ECTprojects"
MsgBox (SQL)
'Returns the value "SELECT partner,location,end FROM tbl-ECTprojects" when
first two are checked which is good

DoCmd.RunSQL (SQL)

'DoCmd.RunSQL SQL, acCmdQueryAddToOutput, "qryExport",
"MicrosoftExcel(*.xls)"

End If

End Sub 'End all code


Where I have my issue now is how to take that SQL statement and send the
result to a spreadsheet. I have no idea what to do and perhaps there is a
much better way to do this. I appreicate any help someone can give me.
Thanks in advance!

 

[Allen Browne]

You have a variable named SQL.
I would have used:
Dim strSql As String
strSql = "SELECT ...

The query has to be a valid SELECT query.

It might help to indicate what error you get, and which line generates the
error.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I agree with your option but unfoutunately for me the client likes the
checkboxes and I still have to use them. However, I still need some more
parameters and code to export my results. Here's what I have so far from
the
ELSE statement:

Else:

SQL = "SELECT " & selection & " FROM tbl-ECTprojects"
'MsgBox (SQL)
DBEngine(0)(0).QueryDefs("qryExport").SQL = SQL
DoCmd.TransferSpreadsheet acExport, , "qryExport", "C:\ECTprojects.xls"

DoCmd.RunMacro "mcr-Exportformclose"

End If


Unfoutunately as it stands now, this code errors out. Does the query have
to be designed a certain way? What parameters do I need forDBEngine,
QueryDefs, and TransferSpreadsheet? Also why did you pick to store the
result of the QueryDefs back into the variable SQL? I apologize for being
an
inexperienced VB programmer.

Okay, the end of the story is that you use TransferSpreadsheet to export
data in Excel format.

TransferSpreadsheet exports a table or query, so you need a saved query
to
export. You could mock one up, and save it as (say) "Query1". Once your
code
has created the SQL statement, you can assign it to the SQL property of
the
saved query, and then export the query:
dbEngine(0)(0).QueryDefs("Query1").SQL = SQL
DoCmd.TransferSpreadsheet acExport,,"Query1", "C:\MyFile.xls"

You might find it easier to use a multi-select list box so the user can
click the fields they want for the export. The list box would have
properties:
Row Source Type Value List
Row Source "Partner", "Location", "Track",
"ProjectSummary",
....
Multi Select Simple

You can then loop through the selected items in the list box to build the
field list for the SELECT clause like this:
Dim varItem As Variant 'Selected items
Dim strList As String 'String to use as WhereCondition
Dim lngLen As Long 'Length of string

'Loop through the ItemsSelected in the list box.
With Me.[NameOfYourListBoxHere]
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strList = strList & .ItemData(varItem) & ", "
End If
Next
End With

'Chop off the trailing separator.
lngLen = Len(strList) - 2
If lngLen <= 0 Then
MsgBox "No fields selected"
Else
dbEngine(0)(0).QueryDefs("Query1").SQL = _
"SELECT " & Left$(strList, lngLen) & " FROM tbl-ECTprojects;"
DoCmd.TransferSpreadsheet acExport,,"Query1", "C:\MyFile.xls"
End If

You can also offer buttons for "Select All" and "Deselect All" if that
helps. The code is here:
http://allenbrowne.com/func-12.html

Hope that helps.

I'm trying to write some vb code in access that takes a 20+ checkboxes
form
and returns any checked columns from a table into an excel spreadsheet.
I
got this far with at least the correct SQL statement.

'MY CODE SO FAR:
Private Sub Command87_Click()

Dim a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v,
w, x
Dim selection As String
Dim SQL As String

If Partnercbox = True Then a = "Partner,"
If Locationcbox = True Then b = "Location,"
If Trackingcbox = True Then c = "Track,"
If ProjectSummarycbox = True Then d = "ProjectSummary,"
If SatisfyActcbox = True Then e = "SatisfyAct,"
If Voicecbox = True Then f = "Voice,"
If Datacbox = True Then g = "Data,"
If ECTemployeecbox = True Then h = "ECTEmployee,"
If Assistancecbox = True Then i = "Assistance,"
If Companycbox = True Then j = "Company,"
If Contactcbox = True Then k = "Contact,"
If Emailcbox = True Then l = "Email,"
If EndDatecbox = True Then m = "EndDate,"
If StartDatecbox = True Then n = "StartDate,"
If LastUpdateDatecbox = True Then o = "LastUpdateDate,"
If Phonecbox = True Then p = "Phone,"
If Requestcbox = True Then q = "Request,"
If Statecbox = True Then r = "State,"
If Statuscbox = True Then s = "Status,"
If StatusDesccbox = True Then t = "StatusDesc,"
If Techcbox = True Then u = "Tech,"
If TechDesccbox = True Then v = "TechDesc,"
If Productcbox = True Then w = "Product,"
x = "end"

selection = a & b & c & d & e & f & g & h & i & j & k & l & m & n & o &
p
&
q & r & s & t & u & v & w & x

If a = "" And b = "" And c = "" And d = "" And e = "" And f = "" And g
=
""
And h = "" And j = "" And k = "" And l = "" And m = "" And n = "" And o
=
""
And p = "" And q = "" And r = "" And s = "" And t = "" And u = "" And v
=
""
And w = "" Then
MsgBox ("You must make at least one selection to Export")

Else:

'DoCmd.OutputTo acQuery, "qryExport", "MicrosoftExcel(*.xls)", "",
True,
"", 0

SQL = "SELECT " & selection & " FROM tbl-ECTprojects"
MsgBox (SQL)
'Returns the value "SELECT partner,location,end FROM tbl-ECTprojects"
when
first two are checked which is good

DoCmd.RunSQL (SQL)

'DoCmd.RunSQL SQL, acCmdQueryAddToOutput, "qryExport",
"MicrosoftExcel(*.xls)"

End If

End Sub 'End all code


Where I have my issue now is how to take that SQL statement and send
the
result to a spreadsheet. I have no idea what to do and perhaps there
is a
much better way to do this. I appreicate any help someone can give me.
Thanks in advance!