Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-07-2019, 01:57 AM   #1
Leo_Polla_Psemata
Newly Registered User
 
Join Date: Mar 2014
Posts: 200
Thanks: 43
Thanked 1 Time in 1 Post
Leo_Polla_Psemata is on a distinguished road
Export data to Excel in format

Hi There
I have created two tables, one to many relationship.
I can retrieve info from reports and looks good.
However, i need to email this good looking report as excel

and look as good as the report.


It must look like this one very simple excel which i have found randomly in web


https://pasteboard.co/IK833i8.jpg


I don't know which is the best method to do this.

From macros, the outcome is not good at all.(exportwithformating)

Do you have any good suggestion ?

Leo_Polla_Psemata is offline   Reply With Quote
Old 12-07-2019, 03:08 AM   #2
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,798
Thanks: 455
Thanked 908 Times in 872 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Export data to Excel in format

I'd create a template and then use Excel automation to populate the summary and copy from recordset for the detail rows.?

Would all be in VBA though, not a macro.
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 12-07-2019, 03:12 AM   #3
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,624
Thanks: 1
Thanked 630 Times in 623 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Export data to Excel in format

What does "not good at all" mean? What's wrong with result?

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
June7 is online now   Reply With Quote
Old 12-07-2019, 04:25 AM   #4
Leo_Polla_Psemata
Newly Registered User
 
Join Date: Mar 2014
Posts: 200
Thanks: 43
Thanked 1 Time in 1 Post
Leo_Polla_Psemata is on a distinguished road
Re: Export data to Excel in format

Quote:
Originally Posted by June7 View Post
What does "not good at all" mean? What's wrong with result?
Hi, the excel doesn't transfer the format and layout of the report.
For example
In the report we have tailored the layout as below:



Table one

Field 1 : A
Field 2 : B
Field 3 : C


Table two
Field 4 Field 5 Field 6
D......... . E ...........F

In excel the layout looks like this

Field 1 Field 2 Field 3 Field 4 Field 5 Field 6
A ............B ..........C
....................................D ....... E ...........F
Leo_Polla_Psemata is offline   Reply With Quote
Old 12-07-2019, 04:34 AM   #5
Leo_Polla_Psemata
Newly Registered User
 
Join Date: Mar 2014
Posts: 200
Thanks: 43
Thanked 1 Time in 1 Post
Leo_Polla_Psemata is on a distinguished road
Re: Export data to Excel in format

Quote:
Originally Posted by Gasman View Post
I'd create a template and then use Excel automation to populate the summary and copy from recordset for the detail rows.?

Would all be in VBA though, not a macro.

Is there any source with more info about this?

So far, i have done this, i export the report as excel on my desktop and then

an excel macro makes the format in another sheet.

But this method doesn't look very "professional"
Leo_Polla_Psemata is offline   Reply With Quote
Old 12-07-2019, 04:39 AM   #6
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,798
Thanks: 455
Thanked 908 Times in 872 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Export data to Excel in format

Quote:
Originally Posted by Leo_Polla_Psemata View Post
Is there any source with more info about this?

So far, i have done this, i export the report as excel on my desktop and then

an excel macro makes the format in another sheet.

But this method doesn't look very "professional"
Well you are almost there. You just do it from Access instead, though the CopyFromRecordset would be new.

I'd just be building it piecemeal.

Lots of examples just on this site.

Just search for 'copyfromrecordset'. The first link has the basics.
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
Leo_Polla_Psemata (12-07-2019)
Old 12-07-2019, 07:53 PM   #7
Leo_Polla_Psemata
Newly Registered User
 
Join Date: Mar 2014
Posts: 200
Thanks: 43
Thanked 1 Time in 1 Post
Leo_Polla_Psemata is on a distinguished road
Re: Export data to Excel in format

Hi
In this youtube, https://www.youtube.com/watch?v=6s5jY09KZT8
i found 110% of what i am looking for

Leo_Polla_Psemata is offline   Reply With Quote
The Following User Says Thank You to Leo_Polla_Psemata For This Useful Post:
Gasman (12-08-2019)
Old 12-08-2019, 02:43 AM   #8
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,798
Thanks: 455
Thanked 908 Times in 872 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Export data to Excel in format

Quote:
Originally Posted by Leo_Polla_Psemata View Post
Hi
In this youtube, https://www.youtube.com/watch?v=6s5jY09KZT8
i found 110% of what i am looking for
Thanks for posting that link, it could help others in the future.
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 12-08-2019, 05:14 AM   #9
Leo_Polla_Psemata
Newly Registered User
 
Join Date: Mar 2014
Posts: 200
Thanks: 43
Thanked 1 Time in 1 Post
Leo_Polla_Psemata is on a distinguished road
Re: Export data to Excel in format

Hi, I have managed to make the code working on my file, however,
if the recordset, my query, is with a criteria such as
Forms!CustomerF!CustomerID, it returns the error



“Run time error 3061 Too few parameters, Expected 1”.
Leo_Polla_Psemata is offline   Reply With Quote
Old 12-08-2019, 05:39 AM   #10
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,798
Thanks: 455
Thanked 908 Times in 872 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Export data to Excel in format

Ok, if you have parameters, you need to supply them. For some reason having them in the query criteria does not work for a recordset.

Another user had this problem before.

I'll see if I can find the thread. theDBguy assisted in it as well with a function from another site.

Here it is.

https://www.access-programmers.co.uk...d.php?t=308093

This is the crux of all of it. Make sure the criteria is defined as parameters.

Code:
    StartDate = Me.busStartDate
    EndDate = Me.busEndDate
    
    Do Until rsDriver.EOF

        ThisTable = rsDriver![tablename]
        NameSheet = rsDriver![SheetName]
        
        Set qdf = db.QueryDefs(ThisTable)
'            For Each prm In qdf.Parameters
'                prm.Value = Eval(prm.Name)
'                Debug.Print prm.Value
'            Next
        qdf.Parameters(0) = Me.busStartDate
        qdf.Parameters(1) = Me.busEndDate
        qdf.Close

        Set rsSrc = qdf.OpenRecordset
        If Not rsSrc.EOF Then
            Set wsDest = wbDest.Worksheets.Add
            wsDest.Name = NameSheet
            For i = 1 To rsSrc.Fields.Count   ' this loop puts in the field names.
                wsDest.Cells(1, i) = rsSrc.Fields(i - 1).Name ' field numbers start at zero, excel starts at 1
            Next i
            wsDest.Range("A2").CopyFromRecordset rsSrc
    
    End If
        rsDriver.MoveNext
        Loop
    
    Set Fdia = FileDialog(msoFileDialogSaveAs)
    With Fdia
        .InitialFileName = Me.txtPath & Me.txtFile
    
        If .Show Then
            FilToSave = .SelectedItems(1)
        End If
    End With

    wbDest.saveas FilToSave

    Set wbDest = Nothing
    Set wsDest = Nothing
    xlApp.Quit
    Set xlApp = Nothing
    Set rsDriver = Nothing
    Set rsSrc = Nothing
    Set qdf = Nothing
    Set prm = Nothing
    
    End If
HTH

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by Gasman; 12-08-2019 at 05:49 AM.
Gasman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
button in form to export a table data in pdf and excel format akika Forms 5 04-03-2019 08:54 AM
Export data into Excel in Transpose Format abdulnaseer Modules & VBA 1 01-30-2013 10:54 AM
format of excel export zonexs123 Modules & VBA 3 01-25-2012 05:47 AM
export to excel seems to format data incorrectly qwertyjjj Forms 1 08-30-2006 01:33 AM
Can I export table data to Excel In a set Format? chewy General 53 04-21-2003 07:23 AM




All times are GMT -8. The time now is 10:33 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World