Exporting Tables/Queries

[Denver]

I have a list box on my form that has this RowSource
SELECT [Name] FROM MSysObjects WHERE [Type]=5 AND [Name] NOT LIKE
"~sq_*" ORDER BY [Name];

i have a cmdbutton (cmdRun Process) which has an event
Call exportspreadsheet

I have this module
Sub exportspreadsheet()
On Error GoTo HandleError

Dim objXLApp As Object
Set objXLApp = CreateObject("Excel.Application")
Dim objXLBook As Excel.Workbook


Dim db As DAO.Database

Set db = CurrentDb

conPath = "D:\Database\Export Folders\EXCEL\DCL All Site2.xlt"

'delete the spreadsheet
Kill conPath & "MySpreadsheet.xls"

' create a workbook from the template
Set objXLApp = New Excel.Application
Set objXLBook = objXLApp.Workbooks.Open(conPath & "DCL All Site2.xlt")
'objXLApp.Visible = True

objXLBook.SaveAs (conPath & "MySpreadsheet.xls")
objXLBook.Close

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "DCN01",
conPath & "MySpreadsheet.xls", True


MsgBox "Done!" & vbCrLf & vbCrLf & "Look in the directory" & vbCrLf & vbCrLf
& "where the application sits for ""MySpreadsheet.xls"""

ProcDone:
On Error Resume Next

' Let's clean up our act
Set qdf = Nothing
Set db = Nothing
Set rs = Nothing
Set objResultsSheet = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing



ExitHere:
Exit Sub
HandleError:
Select Case Err.Number
Case 3265
Resume Next
Case 1004
Set objXLBook = objXLApp.Workbooks.Open("D:\Database\Export
Folders\EXCEL\DCL All Site2.xlt")
Resume Next
Case 53
Resume Next
Case 75
Resume Next
Case Else
MsgBox Err.Description, vbExclamation, _
"Error " & Err.Number
End Select
Resume ProcDone
End Sub

THE PROBLEM IS HOW CAN I MODIFY THE CODES THAT WHATEVER SELECTION I MADE
FROM THE LIST BOX WILL BE EXPORTED TO EXCEL? AND EXPORT IT TO THIS PATH
Database\Export Folders\Excel, is it possible? pls. need your help and pls
modify the codes.

thanks

denver

 

[Tom Wickerath]

Hi Denver,

I have a list box on my form that has this RowSource
SELECT [Name] FROM MSysObjects WHERE [Type]=5 AND [Name] NOT LIKE
"~sq_*" ORDER BY [Name];

I suggest that you change the row source to the SQL statement that I
provided in my last reply. The reason is that your SQL statement is picking
up action queries (delete, update, append, make table) as well as the more
common SELECT queries. Action queries do not result in recordsets; they
change data in tables.

i have a cmdbutton (cmdRun Process)...

Why the space in the name of this control? My advice (and that of Microsoft,
as well) is to avoid the use of special characters:

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

I have this module
Sub exportspreadsheet()
On Error GoTo HandleError

Dim objXLApp As Object
Set objXLApp = CreateObject("Excel.Application")
Dim objXLBook As Excel.Workbook

You are using a mixture of late binding, ie. Dim objXLApp As Object, and
early binding, ie. Dim objXLBook As Excel.Workbook. Use one or the other. For
writing and debugging VBA code, I recommend using early binding. After
throughly tested, you can convert your code to late binding, so that it is
not dependent on a particular version of Excel. Early binding requires a
checked reference to the Excel object library; late binding does not require
a checked reference to this library.

conPath = "D:\Database\Export Folders\EXCEL\DCL All Site2.xlt"

I don't see a declaration for conPath included in your code. So, you either
have it declared as a global constant (or variable) or you do not have those
two very important words as the second line of code in the module: Option
Explicit. I suggest changing the above line of code to this:

Const conPath As String = "D:\Database\Export Folders\EXCEL\DCL All Site2.xlt"

Also, the variables "qdf", "rs" and "objResultsSheet" do not appear to have
been declared. I suggest you add the following three declarations:

Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim objResultsSheet As Excel.Worksheet '<---Uses early binding for now.

Also, this is just my personal choice, but I think code is easier to read if
all Dim statements are grouped together, followed by Set statements (although
sometimes one must initialize a string before issueing a Set statement).

Please see this "Gem Tip" for more information on this topic, including how
to configure your Visual Basic Editor (VBE) to always require variable
declaration:

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

Set objXLBook = objXLApp.Workbooks.Open(conPath & "DCL All Site2.xlt")

If you add a Debug.Print statement, like this:

Debug.Print conPath & "DCL All Site2.xlt"

does the path look reasonable? It appears to include the template file
twice. Here is what I get:

D:\Database\Export Folders\EXCEL\DCL All Site2.xltDCL All Site2.xlt

I think you wanted this, instead:

D:\Database\Export Folders\EXCEL\DCL All Site2.xlt

so you should likely change that line of code to:

Set objXLBook = objXLApp.Workbooks.Open(conPath)

objXLBook.SaveAs (conPath & "MySpreadsheet.xls")

A Debug.Print statement reveals the following, which does not look correct
to me for objXLBook.SaveAs :

D:\Database\Export Folders\EXCEL\DCL All Site2.xltMySpreadsheet.xls

So, perhaps you should really re-define conPath as follows (including the
trailing backslash):

Const conPath As String = "D:\Database\Export Folders\EXCEL\"

and maintain your earlier line of code that concatenates DCL All Site2.xlt,
ie:

Set objXLBook = objXLApp.Workbooks.Open(conPath & "DCL All Site2.xlt")


I'll try to respond further tomorrow afternoon. It's getting late for me
now, so I need to cut it off.

Still needed:

1.) Convert all declarations to early binding for testing purposes
2.) Include a reference to the list box's name to pick up the selected query
(if the multi-select property is set to None--in other words, if you only
want to allow exporting one query at a time) OR

Use VBA code to iterate the .ItemsSelected property of the list box, if you
want to allow selecting multiple queries, and exporting them all in one
operation.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

I have a list box on my form that has this RowSource
SELECT [Name] FROM MSysObjects WHERE [Type]=5 AND [Name] NOT LIKE
"~sq_*" ORDER BY [Name];

i have a cmdbutton (cmdRun Process) which has an event
Call exportspreadsheet

I have this module
Sub exportspreadsheet()
On Error GoTo HandleError

Dim objXLApp As Object
Set objXLApp = CreateObject("Excel.Application")
Dim objXLBook As Excel.Workbook


Dim db As DAO.Database

Set db = CurrentDb

conPath = "D:\Database\Export Folders\EXCEL\DCL All Site2.xlt"

'delete the spreadsheet
Kill conPath & "MySpreadsheet.xls"

' create a workbook from the template
Set objXLApp = New Excel.Application
Set objXLBook = objXLApp.Workbooks.Open(conPath & "DCL All Site2.xlt")
'objXLApp.Visible = True

objXLBook.SaveAs (conPath & "MySpreadsheet.xls")
objXLBook.Close

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "DCN01",
conPath & "MySpreadsheet.xls", True


MsgBox "Done!" & vbCrLf & vbCrLf & "Look in the directory" & vbCrLf & vbCrLf
& "where the application sits for ""MySpreadsheet.xls"""

ProcDone:
On Error Resume Next

' Let's clean up our act
Set qdf = Nothing
Set db = Nothing
Set rs = Nothing
Set objResultsSheet = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing



ExitHere:
Exit Sub
HandleError:
Select Case Err.Number
Case 3265
Resume Next
Case 1004
Set objXLBook = objXLApp.Workbooks.Open("D:\Database\Export
Folders\EXCEL\DCL All Site2.xlt")
Resume Next
Case 53
Resume Next
Case 75
Resume Next
Case Else
MsgBox Err.Description, vbExclamation, _
"Error " & Err.Number
End Select
Resume ProcDone
End Sub

THE PROBLEM IS HOW CAN I MODIFY THE CODES THAT WHATEVER SELECTION I MADE
FROM THE LIST BOX WILL BE EXPORTED TO EXCEL? AND EXPORT IT TO THIS PATH
Database\Export Folders\Excel, is it possible? pls. need your help and pls
modify the codes.

thanks

denver