Converting an Excel VBA macro into an Access module?

[Jallie]

I have a macro in Excel which sorts some data for me. I then import the spreadsheet into a table in Access. What I would like to do is to be able to run the macro on the tables inside Access without having to open Excel at all.

What the Excel macro currently does is it takes a sequence of numbers, it chooses how many characters to retain on either side, and inserts a counter in the middle of them, and chooses which records to include.

For example, if I have the numbers 1122334455, 2122334455, 3122334455, 4122334455 and I want to retain 2 characters on the right, and 3 characters on the left, and insert 500 in the middle and start from records which begin with 2, then the new sequence would be:

21500455, 31501455, 41502455. Here is the code:

Option Explicit
Sub EventIDCode()
Dim intSRC As Integer
Dim intDES As Integer


Dim strSRC As String
Dim strDES As String


Dim xx As Integer
Dim yy As Integer
Dim xt As String
Dim yt As String


Dim strInit As String
Dim intInit As Integer


strSRC = InputBox("Enter the source column", "Source", "A")
If strSRC = "" Then Exit Sub
strDES = InputBox("Enter the destination column", "Destination", "B")
If strDES = "" Then Exit Sub
xt = InputBox("Enter # characters from the left most column to retain", "", "4")
If xt = "" Then Exit Sub
yt = InputBox("Enter # characters from the right most column to retain", "", "5")
If yt = "" Then Exit Sub
strInit = InputBox("Enter first number of the series", "", "1")
If strInit = "" Then Exit Sub


xx = Val(xt)
yy = Val(yt)
intSRC = sv(strSRC)
intDES = sv(strDES)
intInit = Val(strInit)


Dim temp As String
Dim counter As Integer
counter = 2
temp = ActiveSheet.Cells(counter, intSRC)
Do Until temp = ""
    ActiveSheet.Cells(counter, intDES).Value = Left(temp, xx) & four_digit_conv(intInit + counter - 2) & Right(temp, yy)
    counter = counter + 1
    temp = ActiveSheet.Cells(counter, intSRC)
Loop






End Sub
Function four_digit_conv(i As Integer) As String
Dim temp As String
temp = Trim(Str(i))
Select Case Len(Trim(Str(i)))
    Case 1
        four_digit_conv = "000" & temp
    Case 2
        four_digit_conv = "00" & temp
    Case 3
        four_digit_conv = "0" & temp
    Case 4
        four_digit_conv = temp
End Select
End Function


Function sv(ByVal s As String) As Integer
Dim temp As Integer
Dim i As Integer


temp = 0
For i = 1 To Len(s)
    temp = temp * 26 + (Asc(Mid(s, i, 1)) - 64)
Next i
sv = temp
End Function

Basically, I don't want to open Excel at all, I would like to have this macro working on a data table inside Access, but when I pasted it into the module, it would not run. How can I convert the VBA so it is accepted by Access?

Any help is appreciated. Thank you so much for your time.

[June7]

I think you got your left and right backwards.

The strings can easily be extracted by expression in query, like:

SELECT *, Left(fieldname,2) AS IDLeft, Right(fieldname,3) AS IDRight FROM tablename WHERE Left(fieldname,1)>1;

Incrementing is not easily done in query. Review http://www.lebans.com/rownumber.htm

It is easy to show a sequential number in a report. Textbox in report has a RunningSum property that allows this.

[alansidman]

Cross Posted: http://www.access-programmers.co.uk/...d.php?t=243656

Please read this link: http://www.excelguru.ca/content.php?184

[Jallie]

June7, thank you very much for your reply, I've tried the query and it works great so far. I will try to follow the instructions to add the increment as well. Thanks again!

alansidman, I'm really sorry. I have read the article now, and I should have read some more general guidelines before posting to the forum. Thank you for the link, from now on I will follow the instructions in the article.

[June7]

A VBA procedure could generate that new ID value and save to another field in table. This requires opening the table as a recordset in VBA, cycle through the recordset, manipulate the original value, save new value to record, commit the recordset edits to table.