Solved Export data to Excel in format (1 Viewer)

Leo_Polla_Psemata

Registered User.
Local time
Today, 04:28
Joined
Mar 24, 2014
Messages
364
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 ?
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:28
Joined
Sep 21, 2011
Messages
14,038
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.
 

June7

AWF VIP
Local time
Today, 03:28
Joined
Mar 9, 2014
Messages
5,423
What does "not good at all" mean? What's wrong with result?
 

Leo_Polla_Psemata

Registered User.
Local time
Today, 04:28
Joined
Mar 24, 2014
Messages
364
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

Registered User.
Local time
Today, 04:28
Joined
Mar 24, 2014
Messages
364
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"
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:28
Joined
Sep 21, 2011
Messages
14,038
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.
 

Leo_Polla_Psemata

Registered User.
Local time
Today, 04:28
Joined
Mar 24, 2014
Messages
364
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”.
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:28
Joined
Sep 21, 2011
Messages
14,038
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.:banghead:

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/forums/showthread.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
 [COLOR="Red"]       qdf.Parameters(0) = Me.busStartDate
        qdf.Parameters(1) = Me.busEndDate[/COLOR]
        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
 
Last edited:

Users who are viewing this thread

Top Bottom