VBA: Modify Excel Module for Access

[Flashbond]

Hi Guys! I am very new here and this is my first post. I am good with excel but new to access thus be patient, please.

I have a code using in Excel already but now I need the same for Access so is there anyone can modify it for me? First let me explain how does it work and its function.

The code is:

Sub example()

Const nlow  As Long = 5
Const nHigh As Long = 13
Dim oRng    As Range
Dim vArr()  As Variant
Dim v       As Variant
Dim Dict    As Object
Dim n       As Long
Dim i       As Long
Dim j       As Long

Set oRng = Range("A1", Cells(Rows.Count, "A").End(xlUp))
Set Dict = CreateObject("scripting.dictionary")

With Application
    
    vArr = .Transpose(oRng.Value2)
    For i = 1 To UBound(vArr)
        Dict.Add Key:=vArr(i), Item:=vArr(i)
    Next i
    i = Dict.Count
    For n = nlow To nHigh
        j = 0
        For Each v In Dict
            If (n Mod v) > 0 Then
                j = j + 1
            Else
                Exit For
            End If
        Next v
        If j = i Then
            Dict.Add Key:=n, Item:=n
            i = i + 1
        End If
    Next n
    
    oRng.Resize(i, 1) = .Transpose(Dict.Items)

End With

End Sub

Its function is simply when sheet1 looks like this:
Sheet1
A
1 2
2 4

it checks from low value to high value if value 'n' is divisible with both (2 and 4) or not. If it is divisible with one of them, it does nothing. If it is not divisible with none (I mean both), it pastes the value to the next empty cell (in this case A3). And continues to check next values by this method.

And finally it looks like this:
Sheet1
A
1 2
2 4
3 5
4 7
5 9
6 11
7 13

Now, what I expect from Access is just to do the same. First, I'll have two Record lines in the table. In a field name like, let's say "Division" field, there'll be 2 and 4. What I want is, make Access to do the same divisibility check and if it is not divisible with none of the above values, to make Access to insert a new Record line and put the value into the related field (Division).

That's all! Thanks for your help from very this moment!!

 

[Rx_]

You are possibly sending a code fragment. Just in case you need a good example(s) of creating an Access Recordset to move data over to Excel - look at this site:
http://www.btabdevelopment.com/ts/default.aspx?PageId=48

http://msdn.microsoft.com/en-us/library/se0w9esz(v=vs.71).aspx
Good useage of the Mod operator. If a number is divisable by the 2 / 4 the mod will return a 0 (zero). You are using a Case statement.


It will help if you break down your task a little more.
Are you able to open a recordset object from data in Access?
Then Filter on the records from that recordset?
Since your data appears to be numeric, it is usually better to determine what data qualifes in Access first, use SQL to determine the order, then move the resulting data to Excel.
Once in Excel, use Excel object automation to format the data.

My Excel outputs are actually Reports. They are Modules in Access and are Functions that return True if there are no errors. example:

Public Function Battery_Report( _
                  Optional ID_Area As String = " not In(1000) ", _
                  Optional ID_Wells As Integer = 0, _
                  Optional LandscapeView As Boolean = False) As Boolean
      Dim objXL                   As Excel.Application
      Dim XLWB                    As Excel.Workbook
      Dim XLWS                    As Excel.Worksheet
      Dim strSQL                  As String
      Dim rsData                  As DAO.Recordset
      Dim intMaxRecordCount       As Integer ' keep recordset count
      Dim intMaxColCount          As Integer ' keep number of columns

Create an Access Query then copy over the view as SQL or just call an existing query stored in Access. Try to filter, order and arrange here.

strSQL = "SELECT  Wells_TankBattery.ID_TankBattery AS Battery_ID, Wells_Status1.Status1 AS [Well Status], ... (example)"
strSQL = strSQLBattery & " FROM Wells_TankBattery AS Wells_TankBattery_1 ... (example)" 
strSQL = strSQLBattery & " WHERE ... (example) 
strSQL = strSQLBattery & " ORDER BY ... (example) ";

Move the recordset over to Excel. Use variables for sheet number and row start.

Set rsData = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
objXL.Worksheets(intWorksheetNum).Cells(intRowPos, 1).CopyFromRecordset rsDataSundries ' puts results into Excel.

If you need to modify each row's column information, use Excel automation to move row by row, test conditions, and modify properties or values as needed. In this case a cell in column 3 is copied to column 33 if a condition is true.

            With objXL.ActiveWorkbook.ActiveSheet
                For i = intRowPos To intMaxRecordCount + intRowPos
                 If .Cells(i, "B").value <> .Cells(i - 1, "B").value Then
                       .Range(.Cells(i, "B"), .Cells(i, "H")).Font.FontStyle = "Bold"
                       .Cells(i, 33).Value = .Cells(i, 3).Value
                 Else
                       '.Range(.Cells(i, "B"), .Cells(i, "C")).Font.ColorIndex = 16 'metalic gray
                 End If
               Next i
            End With

That is just a very general overview of the process.

 

[Flashbond]

No, I didn't sent you a code fragment. This is code individually does what it is supposed to do. I think you got it wrong,

I am trying to say that I have already a code in Excel but I want to move my project to an access database because of some technical issues.

Now, the new code should work standalone for Table1 within Access without needing any other excel workbook or else.
I described it before;

When I paste the code in to a class or module (even I don't know that difference. If somebody tells, I'll be appreciate) it must pick the numbers by an order within a given range (in our case it is 5 to 13),
Check with the each value (in each record line) within the related field with Mod operator.
If it is divisible with even one of the previous values, do nothing.
If it is not divisible with none of them, then insert a new Record line and paste the attempted number to the related field(in our case the name of the field is "Division").

No excel, no workbook. -an access file can work totally independent and automated.

Thanks a lot!

PS: There is a thread in another forum about the same topic: ozgrid .com/ forum/ showthread.php?t=172183&p=637940

 

[Rx_]

Thanks for the update. I am old and slow, but sometimes can figure it out.
Lets start by putting the code in a standard Module (not a class module) since you are beginning with MS Access.

Let me take a fresh look at this.
Excel offers a nice transpose feature to put the values into an array. I will need to look this part over lunch hour today.

I put your code into Excel 2010 with a module
Put a 1 and 2 in 1st, 2nd row of Column A
Set oRng = Range("A1", Cells(Rows.Count, "A").End(xlUp))
The A1 seems to be a hard codded value. I run that
Then change it to A2 - the 3,5,7,11, 13 appears below on column A, nothing in column B
These are created by the N Low to N High constants.

Are there constants that will become variables?

 

[Flashbond]

Ok Rx_, you are great! Thanks for your concern No, they'll remain as constants. Maybe it is just for my expression. Excuse me for my English...

 

[Flashbond]

Any modüle suggestion?