Solved Export data to Excel in format

[Leo_Polla_Psemata]

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]

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]

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

 

[Leo_Polla_Psemata]

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]

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]

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]

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

 

[Gasman]

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.

 

[Leo_Polla_Psemata]

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]

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.

    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