exporting from access

[dolano]

What I would like to do is export certain data from a report made in access to specific cols in an excel sheet (Within a certain range, eg A3:A78, A86:A119). Firstly is there a simple way to do this or will it require a certain amount of code.

 

[mhartman]

Hello

In Access, just use the Office Links selection on the menu and pick the Analyze with Microsoft Excel option.
Regards
Mark

 

[dolano]

Thanks mark I knew of this and it will generate a new excel sheet for me, however this wouldn be sufficient as I need to update an existing excel worksheet and only specific cols and cells. But thanks anyway

 

[Jibbadiah]

Dolano,

Instead of trying to output a report, you could create a recordset of the query that you use for the report and output that instead. Below is a sample of the code that you could use, as well as some additional format helpers if you want to format the spreadsheet. You would put this code as the on_click event of a command button from a form.

Private Sub cmdExportToExcel_Click()
Dim oExcel As Object
Dim iRow As Integer
Dim rst As Recordset

'This is where you put your own query to select the fields you want to output
Set rst = CurrentDb.OpenRecordset("Select Field1, Field2, Field3 from Tablename;")

If rst.RecordCount > 0 Then
    
    Set oExcel = CreateObject("Excel.Application")
    
    With oExcel
        .Visible = True
        .Workbooks.Add

        With .Workbooks(.Workbooks.Count).Worksheets(1)

        .Cells(1, 4) = "<Stick a title here>"

        'populate recordset
        rst.MoveLast
        rst.MoveFirst
                
        'Create Headings
        .Range("A3") = "Heading 1 "
        .Range("B3") = "Heading 2"
        .Range("C3") = "Heading 3"

        'fill in the records starting at row 4
        For iRow = 4 To rst.RecordCount + 3
            .Cells(iRow, 1) = rst!field1
            .Cells(iRow, 2) = rst!field2
            .Cells(iRow, 3) = rst!field3
            
            If Not rst.EOF Then rst.MoveNext
            Next iRow
            iRow = iRow + 1

            '---------------------------------------------------------------
            'You can change or remove the following to suit your requirements
            .Columns("A").ColumnWidth = 10.5
            .Range("A3:C" & iRow).Font.Name = "Tahoma"
            .Range("A3:C" & iRow).Font.size = "10"
            .Range("A3:C" & iRow).Font.Bold = True
            .Range("A3:C8").BorderAround Weight:=xlThin
            .Range("A3:C8").Borders(xlInsideHorizontal).Weight = xlThin
            .Range("A3:C8").Borders(xlInsideVertical).Weight = xlThin
            .PageSetup.LeftMargin = 18
            .PageSetup.RightMargin = 18
            .PageSetup.TopMargin = 18
            .PageSetup.BottomMargin = 36
            .PageSetup.FitToPagesWide = 1
            '-----------------------------------------------------------------
        
        End With
    End With

    Set oExcel = Nothing
    Set rst = Nothing
    
End If

End Sub

HTH James

 

[Jibbadiah]

Dolano,

Instead of trying to output a report, you could create a recordset of the query that you use for the report and output that instead. Below is a sample of the code that you could use, as well as some additional format helpers if you want to format the spreadsheet. You would put this code as the on_click event of a command button from a form.

Private Sub cmdExportToExcel_Click()
Dim oExcel As Object
Dim iRow As Integer
Dim rst As Recordset

'This is where you put your own query to select the fields you want to output
Set rst = CurrentDb.OpenRecordset("Select Field1, Field2, Field3 from Tablename;")

If rst.RecordCount > 0 Then
    
    Set oExcel = CreateObject("Excel.Application")
    
    With oExcel
        .Visible = True
        .Workbooks.Add

        With .Workbooks(.Workbooks.Count).Worksheets(1)

        .Cells(1, 4) = "<Stick a title here>"

        'populate recordset
        rst.MoveLast
        rst.MoveFirst
                
        'Create Headings
        .Range("A3") = "Heading 1 "
        .Range("B3") = "Heading 2"
        .Range("C3") = "Heading 3"

        'fill in the records starting at row 4
        For iRow = 4 To rst.RecordCount + 3
            .Cells(iRow, 1) = rst!field1
            .Cells(iRow, 2) = rst!field2
            .Cells(iRow, 3) = rst!field3
            
            If Not rst.EOF Then rst.MoveNext
            Next iRow
            iRow = iRow + 1

            '---------------------------------------------------------------
            'You can change or remove the following to suit your requirements
            .Columns("A").ColumnWidth = 10.5
            .Range("A3:C" & iRow).Font.Name = "Tahoma"
            .Range("A3:C" & iRow).Font.size = "10"
            .Range("A3:C" & iRow).Font.Bold = True
            .Range("A3:C8").BorderAround Weight:=xlThin
            .Range("A3:C8").Borders(xlInsideHorizontal).Weight = xlThin
            .Range("A3:C8").Borders(xlInsideVertical).Weight = xlThin
            .PageSetup.LeftMargin = 18
            .PageSetup.RightMargin = 18
            .PageSetup.TopMargin = 18
            .PageSetup.BottomMargin = 36
            .PageSetup.FitToPagesWide = 1
            '-----------------------------------------------------------------
        
        End With
    End With

    Set oExcel = Nothing
    Set rst = Nothing
    
End If

End Sub

HTH James

 

[Jibbadiah]

Sorry about the double-post... my browser said that it had timed-out?!

 

[dolano]

will try

Appriciate that james, I knew this was going to be a stinger but didn't realise how much plus kinda teaching myself vba as I go along.

 

[Jibbadiah]

That code should work as is, it will allow you to see it isn't as hard as it looks.

Create a table called Tablename with 3 fields (field1, field2, field3) and populate some of the fields with any garbage...

i.e.
Field1Row1 Field2Row1 Field3Row1
Field1Row2 Field2Row2 Field3Row2
Field1Row3 Field2Row3 Field3Row3
Field1Row4 Field2Row4 Field3Row4
Field1Row5 Field2Row5 Field3Row5

Paste the above code in an onclick event of a command button (you can do this with the wizard which pops up when you try to add a command button to a form).

It should open an excel document and output the contents of the table.

Try it.

Once you see how it works you can tailor it to your own requirements.

J.

 

[dolano]

cheers

That little prototype worked so I just have to expand on it. Thanks for the help

 

[fluidmind]

Date between

Hi there!

Just read this thread and directly pasted your code into my db too... Your code is simply awesome and so easy to just paste

As my source however, I have selected a query, that includes a condition (Where date Between [forms]![salgsrapportperiode]![tekst1] And [forms]![salgsrapportperiode]![tekst5]). The form 'salgsrapportperiode' is the same form in which the button to run the command below is placed, so it's not that it isn't open...

But still I get an error "Too few parameters.... two expected". And when I remove the condition it works perfectly, but then I get way too many rows exported

Is there any way to make the code below accept the condition in my query???

// JR

 

[Jibbadiah]

Hey Fluid... You will need to translate your query into SQL and then place it as the criteria for your recordset. The code steps through a recordset in order to output it to Excel, so you need to keep the same format.

Put the query in SQL view then copy that code into the bolded section below:

Set rst = CurrentDb.OpenRecordset("[B]Select Field1, Field2, Field3 from QueryName[/B];")

You may need to tailor it a little in order for it to validate correctly... the syntax can get a bit fiddly at times, especially when working with dates. Access won't always accept exactly the same syntax that you use in a query. Start with basic queries and step through the code, keep an eye on the values at each step and ensure that they are correct.

Below is an example of using dates in a select query... take a look and see if you can tailor yours in a similar way. (Note - this works for me... I always have problems with dates, and in my opinion there is no easy way... it is as a suggestion only - there may be better techniques depending upon how you have set up your fields, queries and forms).

SELECT format([DateField], ""dd/mm/yyyy""), Field2 FROM qryQuery WHERE [DateField] between cdate(Format('" & Me.txtDate1 & "',""dd/mm/yyyy"")) And cdate(Format('" & Me.txtDate2 & "',""dd/mm/yyyy""));")

J.