Import Data from Access to Excel and do a conversion from $ to M$

Question

Here is a question i have for importing data from Access to Excel.

So first I need to write data from Excel to Access (when the data is saved in Access, the data are saved as the access units by doing a unit conversion of the data from Excel).

After that, I may want to recall these data back into excel and the excel needs to convert the recalled data back into the base units of Excel.

That being said, for cells with money values, I assign these cells with a unit type of CURRENCY. When these data is saved in Access, they become $. But when they are recalled back into Excel, the base unit in excel is M$. I need to figure out a coding that let these cells value divided by 1000 so they become M$ in Excel. After that, I will have a droplist that let me convert these values to either $ or MM$ when i want to. The conversions for bbl and mcf are already done by original user.

Not sure if anyone can give me a easy solution for this...I have the code below:

    Set rs2 = db.OpenRecordset("SELECT * FROM Project_Data WHERE LoadID = " & _
                                record_ID & " ORDER BY LoadID Asc", dbReadOnly)

    For v = 1 To 1244
        v_name = vars(v, 1)

        If vars(v, 2) = "Y" Then
            rs2.MoveFirst
            Do
                If rs2![VariableName] = v_name Then ' And rs2![LoadID] = record_ID Then
                    If Mid(v_name, 1, 2) = "T1" Then
                        Sheets("T1").Range(v_name) = Val(rs2![VariableValue])
                    End If

                    If Mid(v_name, 1, 2) = "T2" Then
                        Sheets("T2").Range(v_name) = Val(rs2![VariableValue])
                    End If

                    If Mid(v_name, 1, 2) = "T3" Then
                        Sheets("T3").Range(v_name) = Val(rs2![VariableValue])
                    End If

                    If Mid(v_name, 1, 2) = "T4" Then
                        Sheets("T4").Range(v_name) = Val(rs2![VariableValue])
                    End If

                    If Mid(v_name, 1, 2) = "T5" Then
                        Sheets("T5").Range(v_name) = Val(rs2![VariableValue])
                    End If

                    If Mid(v_name, 1, 2) = "T6" Then
                        Sheets("T6").Range(v_name) = Val(rs2![VariableValue])
                    End If

                    If Mid(v_name, 1, 2) = "T7" Then
                        Sheets("T7").Range(v_name) = Val(rs2![VariableValue])
                    End If


                    Exit Do
                End If

                rs2.MoveNext
            Loop Until rs2.EOF
        End If
    Next v

    rs2.Close
    Set rs2 = Nothing


    db.Close

     Set db = Nothing

    Unload Me
    write_log ("Imported existing record.")
    Sheets("Main").Range("GASUNITS") = "MMcf"
    ThisWorkbook.gas_units = "MMcf"
    Sheets("Main").Range("LIQUIDUNITS") = "Mbbl"
    ThisWorkbook.liquid_units = "Mbbl"
    Call Switch_Liquids_Units("Mbbl")
    Call Switch_Gas_Units("MMcf")

    MsgBox ("Data has been loaded.")
End Sub

Coding for switch_Liquids_Units:

Sub Switch_Liquids_Units(units)

    If Range("LIQUIDUNITS") = "Mbbl" Then
        pass_unit = "M"
        pass_fluid = "Oil"
    End If

    If Range("LIQUIDUNITS") = "MMbbl" Then
        pass_unit = "MM"
        pass_fluid = "Oil"
    End If

    If Range("LIQUIDUNITS") = "bbl" Then
        pass_unit = ""
        pass_fluid = "Oil"
    End If

    Call Replace_Units(pass_unit, pass_fluid)

      Sheets("Main").Select

     If do_old = True Then
        Sheets("T1").Select
        'Rows("3:3").Select
        Range("B3:AE3").Select

        If units = "bbl" Then
            Selection.Replace What:="(Mbbl)", Replacement:="(bbl)", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False

            Selection.Replace What:="(MMbbl)", Replacement:="(bbl)", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False

            Selection.Replace What:="(Mboe)", Replacement:="(boe)", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False

            Selection.Replace What:="(MMboe)", Replacement:="(boe)", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
        End If


         If units = "Mbbl" Then
            Range("B3:AE3").Replace What:="(bbl)", Replacement:="(Mbbl)", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False

            Selection.Replace What:="(MMbbl)", Replacement:="(Mbbl)", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False

            Selection.Replace What:="(boe)", Replacement:="(Mboe)", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False

            Selection.Replace What:="(MMboe)", Replacement:="(Mboe)", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
        End If


         If units = "MMbbl" Then
            Selection.Replace What:="(bbl)", Replacement:="(MMbbl)", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False

            Selection.Replace What:="(Mbbl)", Replacement:="(MMbbl)", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False

            Selection.Replace What:="(boe)", Replacement:="(MMboe)", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False

            Selection.Replace What:="(Mboe)", Replacement:="(MMboe)", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
        End If


        Sheets("T2").Select
        'Rows("4:4").Select
        Range("B4:AN4").Select

        If units = "bbl" Then
            Selection.Replace What:="(Mbbl)", Replacement:="(bbl)", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False

            Selection.Replace What:="(MMbbl)", Replacement:="(bbl)", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
        End If


         If units = "Mbbl" Then
            Selection.Replace What:="(bbl)", Replacement:="(Mbbl)", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False

            Selection.Replace What:="(MMbbl)", Replacement:="(Mbbl)", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
        End If


         If units = "MMbbl" Then
            Selection.Replace What:="(bbl)", Replacement:="(MMbbl)", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False

            Selection.Replace What:="(Mbbl)", Replacement:="(MMbbl)", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
        End If
    End If



End Sub

Field type in Access is Currency? I've never used it. I just set field as Number (Double) and then format for currency on report. What is M$? — June7, Jun 10 '20 at 19:53
Seems like you already have existing subs for converting other unit type, so what is the specific challenge in converting currency? It's difficult to map your question to the code you posted. If you just need a simple division by 1000 you could either do that in your SQL or as you're populating the data from the query in Excel. — Tim Williams, Jun 10 '20 at 20:00
@TimWilliams You are right. For bbl and mcf, the subs - switch_liquids_Units and Switch_Gas_units exist already. I don't know if I can copy the same. — Golden Sun, Jun 10 '20 at 20:13
@TimWilliams I have post the codes for switch_gas_units. Could you please show me how you do the simple division by 1000 in SQL? Not sure which part of the code i need to send to you guys so you show me.. — Golden Sun, Jun 10 '20 at 20:20
What do you mean by 'variablevalue'? That is not a type option in my table design. I still wonder why Excel is even involved. — June7, Jun 10 '20 at 20:27
This is confusing. M is mega or 1 mio. while $ x 1000 is 1 k$. — Gustav, Jun 10 '20 at 20:28
@june7 sorry, when i meant unit type, I meant I defined a unit type string "Currency" to the cells in the spreadsheet. The coding will use this unit type to assign values or do calculations... My bad as a noob. — Golden Sun, Jun 10 '20 at 20:52
@Gustav sorry for the confusion. M here does mean 1000. We use MM for Mega. — Golden Sun, Jun 10 '20 at 20:53
Sorry I can't help here - please don't take offense, but this code needs a serious clean-up: it is several times larger than it needs to be and full of undeclared variables which without the proper context could be Globals. It's difficult to suggests fixes or even additions in this context. — Tim Williams, Jun 10 '20 at 20:58
@TimWilliams Sorry about that. I am given some work that is beyond my understanding for VBA. Do you have any tutorials or VBA learning course you suggest? I have been learning online. My current problem is that I can roughly understand the syntax of a module but I have problem writing the code. Eepscially when I try to add a section of code, I am not sure where to insert it and how the logic works..Guess that takes time to grab. — Golden Sun, Jun 10 '20 at 21:25
@GoldenSun - it's always difficult taking on someone else's code: everyone has their own style and some are easier to follow than others. As for learning VBA: I learned a long time ago (Excel 95!) from the equivalent of this book: https://www.amazon.com/Excel-Power-Programming-Spreadsheets-Bookshelf/dp/1119067723 — Tim Williams, Jun 10 '20 at 22:19
OK. So M for Latin Mille. That makes sense. But - as noted by @TimWilliams - your code seems overly complicated. Perhaps you should explain the input and the expected output. — Gustav, Jun 11 '20 at 07:28

score 0 · Answer 1 · answered Jun 10 '20 at 22:08

To answer your titled question (unable to help with posted code), simply adjust SQL query for the needed conversion. Below shows how the SQL statement can be conditionally modified by needed units conversion. Integrate the below in appropriate sections of your code base. Also, avoid SELECT *. Instead, explicitly define columns for code readability and maintainability.

If unitVariable = "per thousand"    
    strSQL = "SELECT Col1 / 1E3 As Col1_in_thousands, " & _
             "       Col2 / 1E3 As Col2_in_thousands, " & _
             "       Col3 / 1E3 As Col3_in_thousands, " & _
             "       ... " & _
             " FROM Project_Data " & _
             " WHERE LoadID = " & record_ID & _
             " ORDER BY LoadID ASC"

ElseIf unitVariable = "per million"
    strSQL = "SELECT Col1 / 1E6 As Col1_in_millions, " & _
             "       Col2 / 1E6 As Col2_in_millions, " & _
             "       Col3 / 1E6 As Col3_in_millions, " & _
             "       ... " & _
             " FROM Project_Data " & _
             " WHERE LoadID = " & record_ID & _
             " ORDER BY LoadID ASC"

ElseIf unitVariable = "per billion"
    strSQL = "SELECT Col1 / 1E9 As Col1_in_billions, " & _
             "       Col2 / 1E9 As Col2_in_billions, " & _
             "       Col3 / 1E9 As Col3_in_billions, " & _
             "       ... " & _
             " FROM Project_Data " & _
             " WHERE LoadID = " & record_ID & _
             " ORDER BY LoadID ASC"
End If

' PASS DYNAMIC SQL QUERY
Set rs2 = db.OpenRecordset(strSQL, dbReadOnly)

...

Aside: since MS Access is a database, you should be storing the most detailed precision of data in it and since Excel is an end-use reporting tool (not to be used for persistent data storage), simply convert to whatever units needed at the report side and in this case via the SQL query.

Import Data from Access to Excel and do a conversion from $ to M$

1 Answers1