Converting an Excel VBA macro into an Access module?

[Jallie]

Hello,

I have a macro in Excel which sorts some spreadsheet 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 run, open, or call Excel at all after the initial import of the spreadsheet.

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 would like to import the Excel spreadsheet once, then edit the table inside Access with a module without having to open Excel again. How can I convert the VBA so it is accepted by Access?

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

 

[Alansidman]

Cross Posted: http://www.accessforums.net/programming/converting-excel-vba-macro-into-access-module-33299.html

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

 

[mdlueck]

To drive Excel from Access, that is possible.

To have Access / VBA act like a spreadsheet with a grid memory scheme and formulas contained in those memory grid cells, interacting with one another... "good night" recreating Excel in Access VBA!

So which path were you thinking of?

You say over and over, "without Excel". That makes it sound like you want the latter, which I am doubtful if you realize what you are asking for in that case.

 

[Jallie]

mdlueck, I thought I might be able to get the data tables to work like an excel spreadsheet, so thank you for explaining to me that this is not the case. I will also try to call the Excel macro from Access then. Thanks!

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.

 

[mdlueck]

mdlueck, I thought I might be able to get the data tables to work like an excel spreadsheet, so thank you for explaining to me that this is not the case.

Since now you are thinking sensibly, I will suggest this post:

How to drive Excel with VBA (Access) in order to transfer values into spreadsheet
http://www.access-programmers.co.uk/forums/showthread.php?t=233104#post1190025

I will also try to call the Excel macro from Access then.

You do not need to attempt to call to VBA in the Excel sheet from Access VBA. Access VBA is fully capable of doing the driving.

 

[eblieb]

To drive Excel from Access, that is possible.

To have Access / VBA act like a spreadsheet with a grid memory scheme and formulas contained in those memory grid cells, interacting with one another... "good night" recreating Excel in Access VBA!

So which path were you thinking of?

You say over and over, "without Excel". That makes it sound like you want the latter, which I am doubtful if you realize what you are asking for in that case.

I have access run math all the time and store it like excel... Granted it is a nice headache...

 

[nschroeder]

I haven't looked extensively at your code to determine what you are trying to accomplish, but there seem to be things that could be done more efficiently. For example, eliminate the four_digit_conv function and replace the line that calls it:

ActiveSheet.Cells(counter, intDES).Value = Left(temp, xx) & four_digit_conv(intInit + counter - 2) & Right(temp, yy)

with this:

ActiveSheet.Cells(counter, intDES).Value = Left(temp, xx) & format((intInit + counter - 2),"0000") & Right(temp, yy)