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 Of Query, Access 2016    
 
   
mike60smart
post Jun 7 2019, 09:37 AM
Post#1


UtterAccess VIP
Posts: 13,225
Joined: 6-June 05
From: Dunbar,Scotland


Hi Everyone

I have the following in the On Click of a Command Button

[Code]
Private Sub cmdCaseWorkerErrorsExcel_Click()
On Error GoTo cmdCaseWorkerErrorsExcel_Click_Err

DoCmd.OpenQuery "qryCaseWorkerErrors", acViewNormal, acEdit


cmdCaseWorkerErrorsExcel_Click_Exit:
Exit Sub

cmdCaseWorkerErrorsExcel_Click_Err:
MsgBox Error$
Resume cmdCaseWorkerErrorsExcel_Click_Exit

End Sub
[\Code]

How could I modify the code to Export to Excel?

Any help appreciated

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
Phil_cattivocara...
post Jun 7 2019, 09:53 AM
Post#2



Posts: 303
Joined: 2-April 18



DoCmd.TransferSpreadsheet method (Access)

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
mike60smart
post Jun 7 2019, 10:15 AM
Post#3


UtterAccess VIP
Posts: 13,225
Joined: 6-June 05
From: Dunbar,Scotland


Hi Phil

That Exports the data OK but how do I then display the File / Query to the user?


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
MadPiet
post Jun 7 2019, 11:26 AM
Post#4



Posts: 3,163
Joined: 27-February 09



you would have to open it first somewhere.
Go to the top of the page
 
mike60smart
post Jun 7 2019, 11:43 AM
Post#5


UtterAccess VIP
Posts: 13,225
Joined: 6-June 05
From: Dunbar,Scotland


Hi Piet

I am trying to use this:-

[Code]
Private Sub cmdCaseWorkerErrorsExcel_Click()
On Error GoTo cmdCaseWorkerErrorsExcel_Click_Err

DoCmd.OpenQuery "qryCaseWorkerErrors"
DoCmd.TransferSpreadsheet acExport, 9, "qryCaseWorkerErrors", "C:\Exports\qryCaseWorkerErrors.xlsx"

cmdCaseWorkerErrorsExcel_Click_Exit:
Exit Sub

cmdCaseWorkerErrorsExcel_Click_Err:
MsgBox Error$
Resume cmdCaseWorkerErrorsExcel_Click_Exit

End Sub
[\Code]

Which Opens the query and Exports the data

Is there any way to display the Exported Data and Close the query?


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
Phil_cattivocara...
post Jun 7 2019, 12:02 PM
Post#6



Posts: 303
Joined: 2-April 18



It is not necessary to open the query before exporting with TransfreSpreadsheet

Application.FollowHyperlink to open the file, then add a new line with

DoCmd.Close method (Access)

If you need to control xls(x) file too you cannot use FollowHyperlink but Excel with Automation

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
DanielPineault
post Jun 7 2019, 03:00 PM
Post#7


UtterAccess VIP
Posts: 6,713
Joined: 30-June 11



You may like to look at http://www.devhut.net/2017/03/15/ms-access...rdset-to-excel/

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
mike60smart
post Jun 7 2019, 03:34 PM
Post#8


UtterAccess VIP
Posts: 13,225
Joined: 6-June 05
From: Dunbar,Scotland


Hi Daniel

I Imported the Module

Then on my Command Button's On Click Event I have this:-

[Code]
Private Sub cmdPreview_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ssql As String

Set db = CurrentDb
ssql = "SELECT * FROM qryCaseWorkerErrors"
Set rs = db.OpenRecordset(ssql, dbOpenSnapshot)
Call ExportRecordset2XLS(rs)
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

[\Code]

When I run it I get the following error:-
Attached File  error.jpg ( 16.48K )Number of downloads: 0


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
mike60smart
post Jun 8 2019, 07:25 AM
Post#9


UtterAccess VIP
Posts: 13,225
Joined: 6-June 05
From: Dunbar,Scotland


Hi Everyone

I have made a step forward in my process

On the Form where I have the Command Button I have also added Unbound textbox's for entering a Start Date and End Date

Now when I run the following Code it Displays the query result and also saves a copy into the Exports Folder

However, when I try to open the file I get the following error:-

Attached File  error.JPG ( 24.98K )Number of downloads: 2


Code is:-

[Code]
Private Sub cmdCaseWorkerErrorsExcel_Click()
On Error GoTo cmdCaseWorkerErrorsExcel_Click_Err
Dim strCrit As String


If IsDate(Me.txtStart) Then
strCrit = strCrit & "([Date_Completed] >= #" & Format(Me.txtStart, "mm/dd/yyyy") & "#) AND "
End If
If IsDate(Me.txtEnd) Then
strCrit = strCrit & "([Date_Completed] <= #" & Format(Me.txtEnd, "mm/dd/yyyy") & "#) AND "
End If

If Me.txtNr > "" Then
strCrit = strCrit & "([WORKER_DCV_DCU_Nr] = " & Chr(34) & Me.txtNr & Chr(34) & ") AND "
End If


If strCrit > "" Then
strCrit = Left(strCrit, Len(strCrit) - 5)

DoCmd.OpenQuery "qryCaseWorkerErrors"
DoCmd.TransferSpreadsheet acExport, 9, "qryCaseWorkerErrors", "C:\Exports\qryCaseWorkerErrors.xlsx"

End If
cmdCaseWorkerErrorsExcel_Click_Exit:
Exit Sub

cmdCaseWorkerErrorsExcel_Click_Err:
MsgBox Error$
Resume cmdCaseWorkerErrorsExcel_Click_Exit

End Sub

Any help appreciated
[\Code]

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
Phil_cattivocara...
post Jun 8 2019, 07:36 AM
Post#10



Posts: 303
Joined: 2-April 18



QUOTE (mike60smart)
CODE
DoCmd.TransferSpreadsheet acExport, 9, "qryCaseWorkerErrors", "C:\Exports\qryCaseWorkerErrors.xlsx"

Come back to the link I gave you, you will find this
QUOTE
acSpreadsheetTypeExcel9 8 Microsoft Excel 2000 format
acSpreadsheetTypeExcel12 9 Microsoft Excel 2010 format
acSpreadsheetTypeExcel12Xml 10 Microsoft Excel 2010/2013/2016 XML format (.xlsx, .xlsm, .xlsb)

What are you using? what should you use?

PS: have you ever noticed you write code inside tag using the "close tag" with backslash [\] instead of slash [/]?

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
mike60smart
post Jun 8 2019, 07:44 AM
Post#11


UtterAccess VIP
Posts: 13,225
Joined: 6-June 05
From: Dunbar,Scotland


Hi Phil

Many thanks for the pointer regarding the correct acTransfer

Also thanks for pointing out my error with the [/Code]

Many thanks all now working
cheers.gif

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th July 2019 - 03:57 AM