Solved Extract excel from access

[Leo_Polla_Psemata]

@arnelgp
This is a new thread, continued from this one

Solved - Format multiple currencies based on a value

Hi In one excel report that I extract from access database, I have two columns which outline mixed currencies, usd or eur. Before, in the old report, the format was just currency “none” and I had used the below line, it worked just fine. .Range("M4", "N" & i).NumberFormat =...

www.access-programmers.co.uk

but since subject has changed, i open this new here.

With the below code, we can retrieve from access tables, one report in excel.
One report in one workbook that is consisted of one tab.

Private Sub Btn1_Click()
On Error GoTo SubError

    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim SQL As String
    Dim rsBS As DAO.Recordset
    Dim i As Integer

    'Show user work is being performed
    DoCmd.Hourglass (True)

    '*********************************************
    '              RETRIEVE DATA
    '*********************************************
    'SQL statement to retrieve data from database

     SQL = "SELECT Forma1.IDM, Forma1.bk, Forma1.freight, Forma1.curre " & _
        "FROM Forma1;"

   'Execute query and populate recordset
    Set rsBS = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)


    'If no data, don't bother opening Excel, just quit
    If rsBS.RecordCount = 0 Then
        MsgBox "No data selected for export", vbInformation + vbOKOnly, "No data exported"
        GoTo SubExit
    End If
    '*********************************************
    '             BUILD SPREADSHEET
    '*********************************************
    'Create an instance of Excel and start building a spreadsheet

    'Early Binding
    Set xlApp = Excel.Application

    xlApp.Visible = False
    Set xlBook = xlApp.Workbooks.Add
    Set xlSheet = xlBook.Worksheets(1)
    Range("A4").Select
    ActiveWindow.FreezePanes = True
' Here I try to remove grid
    xlSheet.Activate
    ActiveWindow.DisplayGridlines = False

With xlSheet
        .Name = "IMPORT BLss"
        .Cells.Font.Name = "Calibri"
        .Cells.Font.Size = 10

        'Format Labels
        .Range("A1").Value = "ID"
        .Range("B1").Value = "BK"
        .Range("C1").Value = "FREIGHT"
        .Range("D1").Value = "CURRENCY"

        'provide initial value to row counter
        i = 2
        'Loop through recordset and copy data from recordset to sheet
        Do While Not rsBS.EOF

    .Range("A" & i).Value = Nz(rsBS!IDM, "")
    .Range("B" & i).Value = Nz(rsBS!bk, "")
    .Range("C" & i).Value = Nz(rsBS!freight, "")
    .Range("D" & i).Value = Nz(rsBS!curre, "")

            i = i + 1
            rsBS.MoveNext

            Loop

End With

SubExit:
On Error Resume Next
    DoCmd.Hourglass False
    xlApp.Visible = True
    rsBS.Close
    Set rsBS = Nothing
    Exit Sub

SubError:
    MsgBox "Error Number: " & Err.Number & "= " & Err.Description, vbCritical + vbOKOnly, _
        "An error occurred"
    GoTo SubExit

End Sub

The question is , how can we retrieve two reports in one workbook that is consisted of two tabs (or more) ,
one report in first tab, second report in second tab

After i add these lines, thanks to arnelgp, yes, i retrieve two tabs however
i don't know where should i add the SQL select statement for the second report , there is no room

xlApp.Visible = False
    Set xlbook = xlApp.Workbooks.Add
   
    'add 1 more worksheet
    With xlbook
        .Worksheets.Add After:=.Worksheets(.Worksheets.Count)
   
        Set xlSheet = .Worksheets(1)
    End With

 

[arnelgp]

its basically the same code as you have in the original code.
you just need to use different column headings.
if you can upload a sample of your db and a sample of the "second" sheet report, then
i think it would be easy to create the code.

 

[Leo_Polla_Psemata]

its basically the same code as you have in the original code.
you just need to use different column headings.
if you can upload a sample of your db and a sample of the "second" sheet report, then
i think it would be easy to create the code.

Here you are our sample database
In the same form, there are two buttons
The first button returns the desired results and it is there just for back up

On the second tab, i have added the piece of code you wrote
and indeed i get the same report with a second tab.
The question is how to bring in the second tab another report,
say we want only the USD, or another report.

To my humble opinion, i was thinking that i must add a second sql statement
after the first one in which i will fetch all required info for the second report,
under the

With xlSheet
end with

I should enter something like this, okay i made several attempts and failed.

With xlSheet

        With Worksheets1
                End With  

        With Worksheets2
                End With  
End with

So, in the second tab, we want to have the exact same format as first one, only the USD currency.

 

[arnelgp]

i remove the other button and just use 1 button to create two sheets.
the code is self explanatory.

 

[Leo_Polla_Psemata]

i remove the other button and just use 1 button to create two sheets.
the code is self explanatory.

Many thanks Arnelgp
I got the point, i will test it and i am sure i can adjust it to my real environment