Solved Format multiple currencies based on a value

[arnelgp]

can you show the error?
you can prevent the error if you calculate first the "complicated formula"

    With .Range("N" & i)
        Dim dblResult As Variant
        On Error Resume Next
        dblResult = Null
        If rsBS!CURRE & "" = "S" Then
            dblResult = [complicated formula]/1.0443
        ElseIf rsBS!CURRE & "" =  "E" Then
            dblResult = [complicated formula]
        End If
        If (Err = 0) Then
           .Value = dblResult
        End If
        Err.Clear
        On Error Goto 0
    End With

 

[Leo_Polla_Psemata]

Hi,
There is a code from which i export excel report from access tables.
With your help, the task works just fine.

The last detail.

I need to apply "sumiffs" at the end (or at the top) of the report

With the below piece of code, i can do it if all parameters are displayed in the excel report.
What if the "USD" or the "OFT" are not displays in the list but we need to use this value from the access table ?
What syntax should i type if i want to replace the
F10" & ":F" & i - 1 & " ," & """USD""" & "
to
rsBS!curre " & """USD""" & "

This sumiffs example is from another report.

   .Range("E2").Formula = "=SUMIFS(I10" & ":I" & i - 1 & " ,D10" & ":D" & i - 1 & " ," & """OFT""" & ",F10" & ":F" & i - 1 & " ," & """USD""" & ", J10" & ":J" & i - 1 & " ," & """P"")"

 

[arnelgp]

can you upload a sample excel file add and add the Actual sumifs() conditions.

 

[Leo_Polla_Psemata]

can you upload a sample excel file add and add the Actual sumifs() conditions.

Yes but the currency S or E, now is not listed in any column, it is only as format in the cell.
So, I can't tell to the code, sum up this column if the value in that column is S because that column doesn't exist in the excel.
Okay, i could list that column and do it , however i change the overall format, i depart a bit from the companies template.
Another solution is to list the currency in column AAX and then "hide".

 

[arnelgp]

give a sample excel file and i will see if we can use the "Format" of the cell to use
in SumIfs() or we just Sum the values and show the result without putting the formula.

 

[Leo_Polla_Psemata]

give a sample excel file and i will see if we can use the "Format" of the cell to use
in SumIfs() or we just Sum the values and show the result without putting the formul

Hi, in the attached file, there is accdb with one table, one form, one button , behind the button is the code you have helped me to extracts the excel.
We want, at the bottom of the report, at cell C8 in this example, the sum of USD , cell C9 the sum of Eur.

 

[arnelgp]

check your excel report.

 

[Leo_Polla_Psemata]

check your excel report.

Hi arnelgp
With the below code, (the full code can be found in previous post) we can retrieve data from any access table/tables and build
ONE excel with ONE spreadsheet.
What syntax should we use if we want to build
ONE excel with TWO or more spreadsheets ?

Then, with the syntax, "With xlSheet....end with" i can combine, in one command button, one excel workbook with two or more spreadsheets, each spreadsheet will host a different report. Now I have two buttons, i crreate 2 different excel workbooks and then combine manually by transferring one spreadsheet from one workbook to another.

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

 

     SQL = "SELECT something " & _
        "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)
    

With xlSheet

 

[arnelgp]

you change this line to:

    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