Export Queries to Excel

  • Thread starter Thread starter Denver
  • Start date Start date
D

Denver

I have an unbound form with a list box
that displays all my Queries....
SELECT [Name] FROM MSysObjects WHERE [Type]=5 AND [Name] NOT LIKE "~sq_*"
ORDER BY [Name];

I have a cmdButton(Export Excel)

My problem is I don't know how to write or start my codes. so that when I
click on it i can export it to excel.i try from the example but no luck..

any help would be appreciated

thanks

Denver
 
Private Sub cmdExportExcel_Click()
On Error GoTo Err_cmdExportExcel_Click

For i = 0 To MyListBox.ListCount - 1
DoCmd.TransferSpreadsheet acExport, 8, MyListBox.Column(0, i), _
"C:\" & MyListBox.Column(0, i)& ".xls", True, ""
Next i
Exit_cmdExportExcel_Click:
Exit Sub
Err_cmdExportExcel_Click:
MsgBox Err.Description
Resume Exit_cmdExportExcel_Click
End Sub

Couple of notes: it will export it to the C drive. You can change that of
course. It will also name the excel file the same as the query name.

If you wanted to actually select which queries to be printed (a better idea
in my opinion), it would be something like this:

Private Sub cmdExportExcel_Click()
On Error GoTo Err_cmdExportExcel_Click

For i = 0 To MyListBox.ListCount - 1
If MyListBox.Selected(i) Then
DoCmd.TransferSpreadsheet acExport, 8, MyListBox.Column(0, i), _
"C:\" & MyListBox.Column(0, i)& ".xls", True, ""
End If
Next i
Exit_cmdExportExcel_Click:
Exit Sub
Err_cmdExportExcel_Click:
MsgBox Err.Description
Resume Exit_cmdExportExcel_Click
End Sub

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Roger,

Is there are reason you would use the entire list rather than this?

Dim varItm As Variant

For Each varItm In Me.MyListBox.ItemsSelected
Docmd.TransferSpreadsheet........
Next varItm

Roger Carlson said:
Private Sub cmdExportExcel_Click()
On Error GoTo Err_cmdExportExcel_Click

For i = 0 To MyListBox.ListCount - 1
DoCmd.TransferSpreadsheet acExport, 8, MyListBox.Column(0, i), _
"C:\" & MyListBox.Column(0, i)& ".xls", True, ""
Next i
Exit_cmdExportExcel_Click:
Exit Sub
Err_cmdExportExcel_Click:
MsgBox Err.Description
Resume Exit_cmdExportExcel_Click
End Sub

Couple of notes: it will export it to the C drive. You can change that of
course. It will also name the excel file the same as the query name.

If you wanted to actually select which queries to be printed (a better
idea in my opinion), it would be something like this:

Private Sub cmdExportExcel_Click()
On Error GoTo Err_cmdExportExcel_Click

For i = 0 To MyListBox.ListCount - 1
If MyListBox.Selected(i) Then
DoCmd.TransferSpreadsheet acExport, 8, MyListBox.Column(0, i), _
"C:\" & MyListBox.Column(0, i)& ".xls", True, ""
End If
Next i
Exit_cmdExportExcel_Click:
Exit Sub
Err_cmdExportExcel_Click:
MsgBox Err.Description
Resume Exit_cmdExportExcel_Click
End Sub

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Denver said:
I have an unbound form with a list box
that displays all my Queries....
SELECT [Name] FROM MSysObjects WHERE [Type]=5 AND [Name] NOT LIKE
"~sq_*"
ORDER BY [Name];

I have a cmdButton(Export Excel)

My problem is I don't know how to write or start my codes. so that when I
click on it i can export it to excel.i try from the example but no luck..

any help would be appreciated

thanks

Denver
Click to expand...
Click to expand...
 
Hello Roger,

On Error GoTo Err_Command213_Click

For i = 0 To MyListBox.ListCount - 1
DoCmd.TransferSpreadsheet acExport, 8, MyListBox.Column(0, i), _
"D:\" & MyListBox.Column(0, i) & ".xls", True, ""

Next i
Exit_Command213_Click:
Exit Sub
Err_Command213_Click:
MsgBox Err.Description
Resume Exit_Command213_Click

I have this code now which i copy from you but when i click on the
Cmdbutton(Command213) a msgbox
prompts and says "Object Required"
do i mis something here? I try to make DIM statement but have Type Mismatch
error...... like... Dim i as Object i even try Dim i as Variant....but no
luck..

is there anyone is kind to help me with my codes...

thanks again

denver


Klatuu said:
Roger,

Is there are reason you would use the entire list rather than this?

Dim varItm As Variant

For Each varItm In Me.MyListBox.ItemsSelected
Docmd.TransferSpreadsheet........
Next varItm

Roger Carlson said:
Private Sub cmdExportExcel_Click()
On Error GoTo Err_cmdExportExcel_Click

For i = 0 To MyListBox.ListCount - 1
DoCmd.TransferSpreadsheet acExport, 8, MyListBox.Column(0, i), _
"C:\" & MyListBox.Column(0, i)& ".xls", True, ""
Next i
Exit_cmdExportExcel_Click:
Exit Sub
Err_cmdExportExcel_Click:
MsgBox Err.Description
Resume Exit_cmdExportExcel_Click
End Sub

Couple of notes: it will export it to the C drive. You can change that of
course. It will also name the excel file the same as the query name.

If you wanted to actually select which queries to be printed (a better
idea in my opinion), it would be something like this:

Private Sub cmdExportExcel_Click()
On Error GoTo Err_cmdExportExcel_Click

For i = 0 To MyListBox.ListCount - 1
If MyListBox.Selected(i) Then
DoCmd.TransferSpreadsheet acExport, 8, MyListBox.Column(0, i), _
"C:\" & MyListBox.Column(0, i)& ".xls", True, ""
End If
Next i
Exit_cmdExportExcel_Click:
Exit Sub
Err_cmdExportExcel_Click:
MsgBox Err.Description
Resume Exit_cmdExportExcel_Click
End Sub

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Denver said:
I have an unbound form with a list box
that displays all my Queries....
SELECT [Name] FROM MSysObjects WHERE [Type]=5 AND [Name] NOT LIKE
"~sq_*"
ORDER BY [Name];

I have a cmdButton(Export Excel)

My problem is I don't know how to write or start my codes. so that when I
click on it i can export it to excel.i try from the example but no luck..

any help would be appreciated

thanks

Denver
Click to expand...
Click to expand...
Click to expand...
 
I forgot to initialize the counter variable (i), so add this after the On
Error line:

Dim i as Integer

Also, because you didn't include the name of your listbox, I chose to name
it MyListBox. So you should substitute your acutal listbox name anywhere
where MyListBox occurs.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Denver said:
Hello Roger,

On Error GoTo Err_Command213_Click

For i = 0 To MyListBox.ListCount - 1
DoCmd.TransferSpreadsheet acExport, 8, MyListBox.Column(0, i), _
"D:\" & MyListBox.Column(0, i) & ".xls", True, ""

Next i
Exit_Command213_Click:
Exit Sub
Err_Command213_Click:
MsgBox Err.Description
Resume Exit_Command213_Click

I have this code now which i copy from you but when i click on the
Cmdbutton(Command213) a msgbox
prompts and says "Object Required"
do i mis something here? I try to make DIM statement but have Type
Mismatch
error...... like... Dim i as Object i even try Dim i as Variant....but no
luck..

is there anyone is kind to help me with my codes...

thanks again

denver


Klatuu said:
Roger,

Is there are reason you would use the entire list rather than this?

Dim varItm As Variant

For Each varItm In Me.MyListBox.ItemsSelected
Docmd.TransferSpreadsheet........
Next varItm

Roger Carlson said:
Private Sub cmdExportExcel_Click()
On Error GoTo Err_cmdExportExcel_Click

For i = 0 To MyListBox.ListCount - 1
DoCmd.TransferSpreadsheet acExport, 8, MyListBox.Column(0, i), _
"C:\" & MyListBox.Column(0, i)& ".xls", True, ""
Next i
Exit_cmdExportExcel_Click:
Exit Sub
Err_cmdExportExcel_Click:
MsgBox Err.Description
Resume Exit_cmdExportExcel_Click
End Sub

Couple of notes: it will export it to the C drive. You can change that
of
course. It will also name the excel file the same as the query name.

If you wanted to actually select which queries to be printed (a better
idea in my opinion), it would be something like this:

Private Sub cmdExportExcel_Click()
On Error GoTo Err_cmdExportExcel_Click

For i = 0 To MyListBox.ListCount - 1
If MyListBox.Selected(i) Then
DoCmd.TransferSpreadsheet acExport, 8, MyListBox.Column(0, i), _
"C:\" & MyListBox.Column(0, i)& ".xls", True, ""
End If
Next i
Exit_cmdExportExcel_Click:
Exit Sub
Err_cmdExportExcel_Click:
MsgBox Err.Description
Resume Exit_cmdExportExcel_Click
End Sub

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


I have an unbound form with a list box
that displays all my Queries....
SELECT [Name] FROM MSysObjects WHERE [Type]=5 AND [Name] NOT LIKE
"~sq_*"
ORDER BY [Name];

I have a cmdButton(Export Excel)

My problem is I don't know how to write or start my codes. so that
when I
click on it i can export it to excel.i try from the example but no
luck..

any help would be appreciated

thanks

Denver
Click to expand...
Click to expand...
Click to expand...
 
In Access 97, there was a bug in the ItemsSelected collection. The
..Selected method was more reliable. I don't know if this bug has been
corrected in subsequent versions, but I got used to using the .Selected
method.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Klatuu said:
Roger,

Is there are reason you would use the entire list rather than this?

Dim varItm As Variant

For Each varItm In Me.MyListBox.ItemsSelected
Docmd.TransferSpreadsheet........
Next varItm

Roger Carlson said:
Private Sub cmdExportExcel_Click()
On Error GoTo Err_cmdExportExcel_Click

For i = 0 To MyListBox.ListCount - 1
DoCmd.TransferSpreadsheet acExport, 8, MyListBox.Column(0, i), _
"C:\" & MyListBox.Column(0, i)& ".xls", True, ""
Next i
Exit_cmdExportExcel_Click:
Exit Sub
Err_cmdExportExcel_Click:
MsgBox Err.Description
Resume Exit_cmdExportExcel_Click
End Sub

Couple of notes: it will export it to the C drive. You can change that
of course. It will also name the excel file the same as the query name.

If you wanted to actually select which queries to be printed (a better
idea in my opinion), it would be something like this:

Private Sub cmdExportExcel_Click()
On Error GoTo Err_cmdExportExcel_Click

For i = 0 To MyListBox.ListCount - 1
If MyListBox.Selected(i) Then
DoCmd.TransferSpreadsheet acExport, 8, MyListBox.Column(0, i), _
"C:\" & MyListBox.Column(0, i)& ".xls", True, ""
End If
Next i
Exit_cmdExportExcel_Click:
Exit Sub
Err_cmdExportExcel_Click:
MsgBox Err.Description
Resume Exit_cmdExportExcel_Click
End Sub

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Denver said:
I have an unbound form with a list box
that displays all my Queries....
SELECT [Name] FROM MSysObjects WHERE [Type]=5 AND [Name] NOT LIKE
"~sq_*"
ORDER BY [Name];

I have a cmdButton(Export Excel)

My problem is I don't know how to write or start my codes. so that when
I
click on it i can export it to excel.i try from the example but no
luck..

any help would be appreciated

thanks

Denver
Click to expand...
Click to expand...
Click to expand...
 
Ahh, I was not aware of that bug. I only began using Acces in the last
days of 97. In fact, we started our first project in 97 and switched to
2000 before we completed it. I have been using the ItemsSelected collection
for the last 3 or so years that I can think of without an issue.

Roger Carlson said:
In Access 97, there was a bug in the ItemsSelected collection. The
.Selected method was more reliable. I don't know if this bug has been
corrected in subsequent versions, but I got used to using the .Selected
method.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Klatuu said:
Roger,

Is there are reason you would use the entire list rather than this?

Dim varItm As Variant

For Each varItm In Me.MyListBox.ItemsSelected
Docmd.TransferSpreadsheet........
Next varItm

Roger Carlson said:
Private Sub cmdExportExcel_Click()
On Error GoTo Err_cmdExportExcel_Click

For i = 0 To MyListBox.ListCount - 1
DoCmd.TransferSpreadsheet acExport, 8, MyListBox.Column(0, i), _
"C:\" & MyListBox.Column(0, i)& ".xls", True, ""
Next i
Exit_cmdExportExcel_Click:
Exit Sub
Err_cmdExportExcel_Click:
MsgBox Err.Description
Resume Exit_cmdExportExcel_Click
End Sub

Couple of notes: it will export it to the C drive. You can change that
of course. It will also name the excel file the same as the query name.

If you wanted to actually select which queries to be printed (a better
idea in my opinion), it would be something like this:

Private Sub cmdExportExcel_Click()
On Error GoTo Err_cmdExportExcel_Click

For i = 0 To MyListBox.ListCount - 1
If MyListBox.Selected(i) Then
DoCmd.TransferSpreadsheet acExport, 8, MyListBox.Column(0, i), _
"C:\" & MyListBox.Column(0, i)& ".xls", True, ""
End If
Next i
Exit_cmdExportExcel_Click:
Exit Sub
Err_cmdExportExcel_Click:
MsgBox Err.Description
Resume Exit_cmdExportExcel_Click
End Sub

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


I have an unbound form with a list box
that displays all my Queries....
SELECT [Name] FROM MSysObjects WHERE [Type]=5 AND [Name] NOT LIKE
"~sq_*"
ORDER BY [Name];

I have a cmdButton(Export Excel)

My problem is I don't know how to write or start my codes. so that when
I
click on it i can export it to excel.i try from the example but no
luck..

any help would be appreciated

thanks

Denver
Click to expand...
Click to expand...
Click to expand...
 
Roger,
it is working now with this code....

Dim i As Integer

For i = 0 To List209.ListCount - 1
If List209.Selected(i) Then
DoCmd.TransferSpreadsheet acExport, 8, List209.Column(0, i), _
"D:\Database\Export Folders" & List209.Column(0, i) & ".xls", True, ""
End If
...........
NOW I WANT TO EXPORT THE SELECTED QUERIES TO THIS PATHNAME
D:\Database\Export Folders\EXCEL
I TRY ALREADY TO WRITE THIS ONE IN THE CODE BUT IT EXPORT ONLY TO
D:\Databse\Export Folders

SECOND THERE ARE SCENARIO THAT I WANT TO PRINT THE SELECTED QUERY AND TRY
THIS CODE.....

For i = 0 To List209.ListCount - 1
If List209.Selected(i) Then
DoCmd.PrintOut

End If


BUT IT WILL PRINT THE DISPLAY WINDOWS OF MY DATABASE
HE WILL NOT PRINT THE SELECTED QUERY INSTEAD....

PLEASE HELP ME WHAT CODE DO I NEED TO PRINT THE SELECTED QUERIES FROM
LISTBOX...

THANKS

DENVER

Roger Carlson said:
In Access 97, there was a bug in the ItemsSelected collection. The
..Selected method was more reliable. I don't know if this bug has been
corrected in subsequent versions, but I got used to using the .Selected
method.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Klatuu said:
Roger,

Is there are reason you would use the entire list rather than this?

Dim varItm As Variant

For Each varItm In Me.MyListBox.ItemsSelected
Docmd.TransferSpreadsheet........
Next varItm

Roger Carlson said:
Private Sub cmdExportExcel_Click()
On Error GoTo Err_cmdExportExcel_Click

For i = 0 To MyListBox.ListCount - 1
DoCmd.TransferSpreadsheet acExport, 8, MyListBox.Column(0, i), _
"C:\" & MyListBox.Column(0, i)& ".xls", True, ""
Next i
Exit_cmdExportExcel_Click:
Exit Sub
Err_cmdExportExcel_Click:
MsgBox Err.Description
Resume Exit_cmdExportExcel_Click
End Sub

Couple of notes: it will export it to the C drive. You can change that
of course. It will also name the excel file the same as the query name.

If you wanted to actually select which queries to be printed (a better
idea in my opinion), it would be something like this:

Private Sub cmdExportExcel_Click()
On Error GoTo Err_cmdExportExcel_Click

For i = 0 To MyListBox.ListCount - 1
If MyListBox.Selected(i) Then
DoCmd.TransferSpreadsheet acExport, 8, MyListBox.Column(0, i), _
"C:\" & MyListBox.Column(0, i)& ".xls", True, ""
End If
Next i
Exit_cmdExportExcel_Click:
Exit Sub
Err_cmdExportExcel_Click:
MsgBox Err.Description
Resume Exit_cmdExportExcel_Click
End Sub

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


I have an unbound form with a list box
that displays all my Queries....
SELECT [Name] FROM MSysObjects WHERE [Type]=5 AND [Name] NOT LIKE
"~sq_*"
ORDER BY [Name];

I have a cmdButton(Export Excel)

My problem is I don't know how to write or start my codes. so that when
I
click on it i can export it to excel.i try from the example but no
luck..

any help would be appreciated

thanks

Denver
Click to expand...
Click to expand...
Click to expand...
 
Unless I'm missing something, just change the path in your code to the path
that you want:

DoCmd.TransferSpreadsheet acExport, 8, List209.Column(0, i), _
"D:\Database\Export Folders\EXCEL\" & List209.Column(0, i) & ".xls",
True, ""

As for printing it, you need to open each query, print it, then close it:

For i = 0 To List209.ListCount - 1
If List209.Selected(i) Then
DoCmd.OpenQuery List209.Column(0, i)
DoCmd.PrintOut
DoCmd.Close acQuery, List209.Column(0, i)
End If
Next i


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Denver said:
Roger,
it is working now with this code....

Dim i As Integer

For i = 0 To List209.ListCount - 1
If List209.Selected(i) Then
DoCmd.TransferSpreadsheet acExport, 8, List209.Column(0, i), _
"D:\Database\Export Folders" & List209.Column(0, i) & ".xls", True, ""
End If
..........
NOW I WANT TO EXPORT THE SELECTED QUERIES TO THIS PATHNAME
D:\Database\Export Folders\EXCEL
I TRY ALREADY TO WRITE THIS ONE IN THE CODE BUT IT EXPORT ONLY TO
D:\Databse\Export Folders

SECOND THERE ARE SCENARIO THAT I WANT TO PRINT THE SELECTED QUERY AND TRY
THIS CODE.....

For i = 0 To List209.ListCount - 1
If List209.Selected(i) Then
DoCmd.PrintOut

End If


BUT IT WILL PRINT THE DISPLAY WINDOWS OF MY DATABASE
HE WILL NOT PRINT THE SELECTED QUERY INSTEAD....

PLEASE HELP ME WHAT CODE DO I NEED TO PRINT THE SELECTED QUERIES FROM
LISTBOX...

THANKS

DENVER

Roger Carlson said:
In Access 97, there was a bug in the ItemsSelected collection. The
..Selected method was more reliable. I don't know if this bug has been
corrected in subsequent versions, but I got used to using the .Selected
method.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Klatuu said:
Roger,

Is there are reason you would use the entire list rather than this?

Dim varItm As Variant

For Each varItm In Me.MyListBox.ItemsSelected
Docmd.TransferSpreadsheet........
Next varItm

Private Sub cmdExportExcel_Click()
On Error GoTo Err_cmdExportExcel_Click

For i = 0 To MyListBox.ListCount - 1
DoCmd.TransferSpreadsheet acExport, 8, MyListBox.Column(0, i), _
"C:\" & MyListBox.Column(0, i)& ".xls", True, ""
Next i
Exit_cmdExportExcel_Click:
Exit Sub
Err_cmdExportExcel_Click:
MsgBox Err.Description
Resume Exit_cmdExportExcel_Click
End Sub

Couple of notes: it will export it to the C drive. You can change
that
of course. It will also name the excel file the same as the query
name.

If you wanted to actually select which queries to be printed (a better
idea in my opinion), it would be something like this:

Private Sub cmdExportExcel_Click()
On Error GoTo Err_cmdExportExcel_Click

For i = 0 To MyListBox.ListCount - 1
If MyListBox.Selected(i) Then
DoCmd.TransferSpreadsheet acExport, 8, MyListBox.Column(0, i),
_
"C:\" & MyListBox.Column(0, i)& ".xls", True, ""
End If
Next i
Exit_cmdExportExcel_Click:
Exit Sub
Err_cmdExportExcel_Click:
MsgBox Err.Description
Resume Exit_cmdExportExcel_Click
End Sub

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


I have an unbound form with a list box
that displays all my Queries....
SELECT [Name] FROM MSysObjects WHERE [Type]=5 AND [Name] NOT LIKE
"~sq_*"
ORDER BY [Name];

I have a cmdButton(Export Excel)

My problem is I don't know how to write or start my codes. so that
when
I
click on it i can export it to excel.i try from the example but no
luck..

any help would be appreciated

thanks

Denver
Click to expand...
Click to expand...
Click to expand...
 
Hi Roger,

I have this code.....
because i want to give an option to user before they print...
but this code give an error ....."Type Mismatch"
what do u miss here...

Dim i As Integer
Dim rsp As String


For i = 0 To List209.ListCount - 1
If List209.Selected(i) Then
MsgBox "Continue", vbYesNo, "Printing"

If rsp = vbYes Then
DoCmd.OpenQuery List209.Column(0, i)
DoCmd.PrintOut
DoCmd.Close acQuery, List209.Column(0, i)

Else
DoCmd.CancelEvent
End If
End If


thanks again

denver

Roger Carlson said:
Unless I'm missing something, just change the path in your code to the path
that you want:

DoCmd.TransferSpreadsheet acExport, 8, List209.Column(0, i), _
"D:\Database\Export Folders\EXCEL\" & List209.Column(0, i) & ".xls",
True, ""

As for printing it, you need to open each query, print it, then close it:

For i = 0 To List209.ListCount - 1
If List209.Selected(i) Then
DoCmd.OpenQuery List209.Column(0, i)
DoCmd.PrintOut
DoCmd.Close acQuery, List209.Column(0, i)
End If
Next i


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Denver said:
Roger,
it is working now with this code....

Dim i As Integer

For i = 0 To List209.ListCount - 1
If List209.Selected(i) Then
DoCmd.TransferSpreadsheet acExport, 8, List209.Column(0, i), _
"D:\Database\Export Folders" & List209.Column(0, i) & ".xls", True, ""
End If
..........
NOW I WANT TO EXPORT THE SELECTED QUERIES TO THIS PATHNAME
D:\Database\Export Folders\EXCEL
I TRY ALREADY TO WRITE THIS ONE IN THE CODE BUT IT EXPORT ONLY TO
D:\Databse\Export Folders

SECOND THERE ARE SCENARIO THAT I WANT TO PRINT THE SELECTED QUERY AND TRY
THIS CODE.....

For i = 0 To List209.ListCount - 1
If List209.Selected(i) Then
DoCmd.PrintOut

End If


BUT IT WILL PRINT THE DISPLAY WINDOWS OF MY DATABASE
HE WILL NOT PRINT THE SELECTED QUERY INSTEAD....

PLEASE HELP ME WHAT CODE DO I NEED TO PRINT THE SELECTED QUERIES FROM
LISTBOX...

THANKS

DENVER

Roger Carlson said:
In Access 97, there was a bug in the ItemsSelected collection. The
..Selected method was more reliable. I don't know if this bug has been
corrected in subsequent versions, but I got used to using the .Selected
method.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Roger,

Is there are reason you would use the entire list rather than this?

Dim varItm As Variant

For Each varItm In Me.MyListBox.ItemsSelected
Docmd.TransferSpreadsheet........
Next varItm

Private Sub cmdExportExcel_Click()
On Error GoTo Err_cmdExportExcel_Click

For i = 0 To MyListBox.ListCount - 1
DoCmd.TransferSpreadsheet acExport, 8, MyListBox.Column(0, i), _
"C:\" & MyListBox.Column(0, i)& ".xls", True, ""
Next i
Exit_cmdExportExcel_Click:
Exit Sub
Err_cmdExportExcel_Click:
MsgBox Err.Description
Resume Exit_cmdExportExcel_Click
End Sub

Couple of notes: it will export it to the C drive. You can change
that
of course. It will also name the excel file the same as the query
name.

If you wanted to actually select which queries to be printed (a better
idea in my opinion), it would be something like this:

Private Sub cmdExportExcel_Click()
On Error GoTo Err_cmdExportExcel_Click

For i = 0 To MyListBox.ListCount - 1
If MyListBox.Selected(i) Then
DoCmd.TransferSpreadsheet acExport, 8, MyListBox.Column(0, i),
_
"C:\" & MyListBox.Column(0, i)& ".xls", True, ""
End If
Next i
Exit_cmdExportExcel_Click:
Exit Sub
Err_cmdExportExcel_Click:
MsgBox Err.Description
Resume Exit_cmdExportExcel_Click
End Sub

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


I have an unbound form with a list box
that displays all my Queries....
SELECT [Name] FROM MSysObjects WHERE [Type]=5 AND [Name] NOT LIKE
"~sq_*"
ORDER BY [Name];

I have a cmdButton(Export Excel)

My problem is I don't know how to write or start my codes. so that
when
I
click on it i can export it to excel.i try from the example but no
luck..

any help would be appreciated

thanks

Denver
Click to expand...
Click to expand...
Click to expand...
 
Hi Roger,

I have this code.....
i want to give an option to user before they print and give them the option
to set up the page, is this possible? what would be the code?...
but this code give an error ....."Type Mismatch"
what do miss here...
Note: once the query is open i don't want them to edit any records, is it
possuble?
i only want them to print and page setup..
Dim i As Integer
Dim rsp As String

For i = 0 To List209.ListCount - 1
If List209.Selected(i) Then
MsgBox "Continue", vbYesNo, "Printing"

If rsp = vbYes Then
DoCmd.OpenQuery List209.Column(0, i)
DoCmd.PrintOut
DoCmd.Close acQuery, List209.Column(0, i)
Else
DoCmd.CancelEvent
End If
End If


thanks again

denver

Roger Carlson said:
Unless I'm missing something, just change the path in your code to the path
that you want:

DoCmd.TransferSpreadsheet acExport, 8, List209.Column(0, i), _
"D:\Database\Export Folders\EXCEL\" & List209.Column(0, i) & ".xls",
True, ""

As for printing it, you need to open each query, print it, then close it:

For i = 0 To List209.ListCount - 1
If List209.Selected(i) Then
DoCmd.OpenQuery List209.Column(0, i)
DoCmd.PrintOut
DoCmd.Close acQuery, List209.Column(0, i)
End If
Next i


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Denver said:
Roger,
it is working now with this code....

Dim i As Integer

For i = 0 To List209.ListCount - 1
If List209.Selected(i) Then
DoCmd.TransferSpreadsheet acExport, 8, List209.Column(0, i), _
"D:\Database\Export Folders" & List209.Column(0, i) & ".xls", True, ""
End If
..........
NOW I WANT TO EXPORT THE SELECTED QUERIES TO THIS PATHNAME
D:\Database\Export Folders\EXCEL
I TRY ALREADY TO WRITE THIS ONE IN THE CODE BUT IT EXPORT ONLY TO
D:\Databse\Export Folders

SECOND THERE ARE SCENARIO THAT I WANT TO PRINT THE SELECTED QUERY AND TRY
THIS CODE.....

For i = 0 To List209.ListCount - 1
If List209.Selected(i) Then
DoCmd.PrintOut

End If


BUT IT WILL PRINT THE DISPLAY WINDOWS OF MY DATABASE
HE WILL NOT PRINT THE SELECTED QUERY INSTEAD....

PLEASE HELP ME WHAT CODE DO I NEED TO PRINT THE SELECTED QUERIES FROM
LISTBOX...

THANKS

DENVER

:

In Access 97, there was a bug in the ItemsSelected collection. The
..Selected method was more reliable. I don't know if this bug has been
corrected in subsequent versions, but I got used to using the .Selected
method.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Roger,

Is there are reason you would use the entire list rather than this?

Dim varItm As Variant

For Each varItm In Me.MyListBox.ItemsSelected
Docmd.TransferSpreadsheet........
Next varItm

Private Sub cmdExportExcel_Click()
On Error GoTo Err_cmdExportExcel_Click

For i = 0 To MyListBox.ListCount - 1
DoCmd.TransferSpreadsheet acExport, 8, MyListBox.Column(0, i), _
"C:\" & MyListBox.Column(0, i)& ".xls", True, ""
Next i
Exit_cmdExportExcel_Click:
Exit Sub
Err_cmdExportExcel_Click:
MsgBox Err.Description
Resume Exit_cmdExportExcel_Click
End Sub

Couple of notes: it will export it to the C drive. You can change
that
of course. It will also name the excel file the same as the query
name.

If you wanted to actually select which queries to be printed (a better
idea in my opinion), it would be something like this:

Private Sub cmdExportExcel_Click()
On Error GoTo Err_cmdExportExcel_Click

For i = 0 To MyListBox.ListCount - 1
If MyListBox.Selected(i) Then
DoCmd.TransferSpreadsheet acExport, 8, MyListBox.Column(0, i),
_
"C:\" & MyListBox.Column(0, i)& ".xls", True, ""
End If
Next i
Exit_cmdExportExcel_Click:
Exit Sub
Err_cmdExportExcel_Click:
MsgBox Err.Description
Resume Exit_cmdExportExcel_Click
End Sub

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


I have an unbound form with a list box
that displays all my Queries....
SELECT [Name] FROM MSysObjects WHERE [Type]=5 AND [Name] NOT LIKE
"~sq_*"
ORDER BY [Name];

I have a cmdButton(Export Excel)

My problem is I don't know how to write or start my codes. so that
when
I
click on it i can export it to excel.i try from the example but no
luck..

any help would be appreciated

thanks

Denver
Click to expand...
Click to expand...
Click to expand...
 
You are using the wrong syntax for the MsgBox *in this case*. If you want
to return a value from the message box, it needs to be like this:
rsp = MsgBox ("Continue Printing?", vbYesNo)

so:

Dim i As Integer
Dim rsp As String

rsp = MsgBox ("Continue Printing?", vbYesNo)
If rsp = vbYes Then
For i = 0 To List209.ListCount - 1
If List209.Selected(i) Then
DoCmd.OpenQuery List209.Column(0, i)
DoCmd.PrintOut
DoCmd.Close acQuery, List209.Column(0, i)
End If
Next i
End If


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Denver said:
Hi Roger,

I have this code.....
because i want to give an option to user before they print...
but this code give an error ....."Type Mismatch"
what do u miss here...

Dim i As Integer
Dim rsp As String


For i = 0 To List209.ListCount - 1
If List209.Selected(i) Then
MsgBox "Continue", vbYesNo, "Printing"

If rsp = vbYes Then
DoCmd.OpenQuery List209.Column(0, i)
DoCmd.PrintOut
DoCmd.Close acQuery, List209.Column(0, i)

Else
DoCmd.CancelEvent
End If
End If


thanks again

denver

Roger Carlson said:
Unless I'm missing something, just change the path in your code to the
path
that you want:

DoCmd.TransferSpreadsheet acExport, 8, List209.Column(0, i), _
"D:\Database\Export Folders\EXCEL\" & List209.Column(0, i) & ".xls",
True, ""

As for printing it, you need to open each query, print it, then close it:

For i = 0 To List209.ListCount - 1
If List209.Selected(i) Then
DoCmd.OpenQuery List209.Column(0, i)
DoCmd.PrintOut
DoCmd.Close acQuery, List209.Column(0, i)
End If
Next i


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Denver said:
Roger,
it is working now with this code....

Dim i As Integer

For i = 0 To List209.ListCount - 1
If List209.Selected(i) Then
DoCmd.TransferSpreadsheet acExport, 8, List209.Column(0, i), _
"D:\Database\Export Folders" & List209.Column(0, i) & ".xls", True,
""
End If
..........
NOW I WANT TO EXPORT THE SELECTED QUERIES TO THIS PATHNAME
D:\Database\Export Folders\EXCEL
I TRY ALREADY TO WRITE THIS ONE IN THE CODE BUT IT EXPORT ONLY TO
D:\Databse\Export Folders

SECOND THERE ARE SCENARIO THAT I WANT TO PRINT THE SELECTED QUERY AND
TRY
THIS CODE.....

For i = 0 To List209.ListCount - 1
If List209.Selected(i) Then
DoCmd.PrintOut

End If


BUT IT WILL PRINT THE DISPLAY WINDOWS OF MY DATABASE
HE WILL NOT PRINT THE SELECTED QUERY INSTEAD....

PLEASE HELP ME WHAT CODE DO I NEED TO PRINT THE SELECTED QUERIES FROM
LISTBOX...

THANKS

DENVER

:

In Access 97, there was a bug in the ItemsSelected collection. The
..Selected method was more reliable. I don't know if this bug has
been
corrected in subsequent versions, but I got used to using the
.Selected
method.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Roger,

Is there are reason you would use the entire list rather than this?

Dim varItm As Variant

For Each varItm In Me.MyListBox.ItemsSelected
Docmd.TransferSpreadsheet........
Next varItm

Private Sub cmdExportExcel_Click()
On Error GoTo Err_cmdExportExcel_Click

For i = 0 To MyListBox.ListCount - 1
DoCmd.TransferSpreadsheet acExport, 8, MyListBox.Column(0, i),
_
"C:\" & MyListBox.Column(0, i)& ".xls", True, ""
Next i
Exit_cmdExportExcel_Click:
Exit Sub
Err_cmdExportExcel_Click:
MsgBox Err.Description
Resume Exit_cmdExportExcel_Click
End Sub

Couple of notes: it will export it to the C drive. You can change
that
of course. It will also name the excel file the same as the query
name.

If you wanted to actually select which queries to be printed (a
better
idea in my opinion), it would be something like this:

Private Sub cmdExportExcel_Click()
On Error GoTo Err_cmdExportExcel_Click

For i = 0 To MyListBox.ListCount - 1
If MyListBox.Selected(i) Then
DoCmd.TransferSpreadsheet acExport, 8, MyListBox.Column(0,
i),
_
"C:\" & MyListBox.Column(0, i)& ".xls", True, ""
End If
Next i
Exit_cmdExportExcel_Click:
Exit Sub
Err_cmdExportExcel_Click:
MsgBox Err.Description
Resume Exit_cmdExportExcel_Click
End Sub

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


I have an unbound form with a list box
that displays all my Queries....
SELECT [Name] FROM MSysObjects WHERE [Type]=5 AND [Name] NOT
LIKE
"~sq_*"
ORDER BY [Name];

I have a cmdButton(Export Excel)

My problem is I don't know how to write or start my codes. so that
when
I
click on it i can export it to excel.i try from the example but no
luck..

any help would be appreciated

thanks

Denver
Click to expand...
Click to expand...
Click to expand...
 
Thank you Mr. Roger
it works now.

Roger Carlson said:
You are using the wrong syntax for the MsgBox *in this case*. If you want
to return a value from the message box, it needs to be like this:
rsp = MsgBox ("Continue Printing?", vbYesNo)

so:

Dim i As Integer
Dim rsp As String

rsp = MsgBox ("Continue Printing?", vbYesNo)
If rsp = vbYes Then
For i = 0 To List209.ListCount - 1
If List209.Selected(i) Then
DoCmd.OpenQuery List209.Column(0, i)
DoCmd.PrintOut
DoCmd.Close acQuery, List209.Column(0, i)
End If
Next i
End If


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Denver said:
Hi Roger,

I have this code.....
because i want to give an option to user before they print...
but this code give an error ....."Type Mismatch"
what do u miss here...

Dim i As Integer
Dim rsp As String


For i = 0 To List209.ListCount - 1
If List209.Selected(i) Then
MsgBox "Continue", vbYesNo, "Printing"

If rsp = vbYes Then
DoCmd.OpenQuery List209.Column(0, i)
DoCmd.PrintOut
DoCmd.Close acQuery, List209.Column(0, i)

Else
DoCmd.CancelEvent
End If
End If


thanks again

denver

Roger Carlson said:
Unless I'm missing something, just change the path in your code to the
path
that you want:

DoCmd.TransferSpreadsheet acExport, 8, List209.Column(0, i), _
"D:\Database\Export Folders\EXCEL\" & List209.Column(0, i) & ".xls",
True, ""

As for printing it, you need to open each query, print it, then close it:

For i = 0 To List209.ListCount - 1
If List209.Selected(i) Then
DoCmd.OpenQuery List209.Column(0, i)
DoCmd.PrintOut
DoCmd.Close acQuery, List209.Column(0, i)
End If
Next i


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Roger,
it is working now with this code....

Dim i As Integer

For i = 0 To List209.ListCount - 1
If List209.Selected(i) Then
DoCmd.TransferSpreadsheet acExport, 8, List209.Column(0, i), _
"D:\Database\Export Folders" & List209.Column(0, i) & ".xls", True,
""
End If
..........
NOW I WANT TO EXPORT THE SELECTED QUERIES TO THIS PATHNAME
D:\Database\Export Folders\EXCEL
I TRY ALREADY TO WRITE THIS ONE IN THE CODE BUT IT EXPORT ONLY TO
D:\Databse\Export Folders

SECOND THERE ARE SCENARIO THAT I WANT TO PRINT THE SELECTED QUERY AND
TRY
THIS CODE.....

For i = 0 To List209.ListCount - 1
If List209.Selected(i) Then
DoCmd.PrintOut

End If


BUT IT WILL PRINT THE DISPLAY WINDOWS OF MY DATABASE
HE WILL NOT PRINT THE SELECTED QUERY INSTEAD....

PLEASE HELP ME WHAT CODE DO I NEED TO PRINT THE SELECTED QUERIES FROM
LISTBOX...

THANKS

DENVER

:

In Access 97, there was a bug in the ItemsSelected collection. The
..Selected method was more reliable. I don't know if this bug has
been
corrected in subsequent versions, but I got used to using the
.Selected
method.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Roger,

Is there are reason you would use the entire list rather than this?

Dim varItm As Variant

For Each varItm In Me.MyListBox.ItemsSelected
Docmd.TransferSpreadsheet........
Next varItm

Private Sub cmdExportExcel_Click()
On Error GoTo Err_cmdExportExcel_Click

For i = 0 To MyListBox.ListCount - 1
DoCmd.TransferSpreadsheet acExport, 8, MyListBox.Column(0, i),
_
"C:\" & MyListBox.Column(0, i)& ".xls", True, ""
Next i
Exit_cmdExportExcel_Click:
Exit Sub
Err_cmdExportExcel_Click:
MsgBox Err.Description
Resume Exit_cmdExportExcel_Click
End Sub

Couple of notes: it will export it to the C drive. You can change
that
of course. It will also name the excel file the same as the query
name.

If you wanted to actually select which queries to be printed (a
better
idea in my opinion), it would be something like this:

Private Sub cmdExportExcel_Click()
On Error GoTo Err_cmdExportExcel_Click

For i = 0 To MyListBox.ListCount - 1
If MyListBox.Selected(i) Then
DoCmd.TransferSpreadsheet acExport, 8, MyListBox.Column(0,
i),
_
"C:\" & MyListBox.Column(0, i)& ".xls", True, ""
End If
Next i
Exit_cmdExportExcel_Click:
Exit Sub
Err_cmdExportExcel_Click:
MsgBox Err.Description
Resume Exit_cmdExportExcel_Click
End Sub

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


I have an unbound form with a list box
that displays all my Queries....
SELECT [Name] FROM MSysObjects WHERE [Type]=5 AND [Name] NOT
LIKE
"~sq_*"
ORDER BY [Name];

I have a cmdButton(Export Excel)

My problem is I don't know how to write or start my codes. so that
when
I
click on it i can export it to excel.i try from the example but no
luck..

any help would be appreciated

thanks

Denver
Click to expand...
Click to expand...
Click to expand...
 
Back
Top