Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2013
    Posts
    3

    Unanswered: Running an Excel macro in Access?

    Hello,

    I have a macro in Excel which does the following:
    If there is a number like 123456789 then the user can choose to retain some numbers from the left and the right and insert some numbers in the middle. For example, if the user wanted to retain the first four digits and the last two, and insert '0500' in the middle, then the new number would be:
    1234050089

    Any ideas how to do this in Access? Can I run the macro in Access or should it be built again using an Access macro? Can this even be done in Access? Should I use a query? Can I insert loops into a query?

    I am sorry for all of the questions. I am very new to Access. Any advice is appreciated. Thank you.

    I am using Access 2010. Here is the code for the macro in Excel:
    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
    Last edited by Jallie; 03-13-13 at 07:50.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    that doesn't look like an excel macro to me, it does look like a fragment of VBA, so most of it should work inside Access or any other VBA aware applciation once you have removed excxel specific functions fromt he code

    the ideal approach is to write functions that return values, keepignm that fiunction as generalised as possible, and then call those function from pother subs/functiosn which handle applciationm specific calls such as your active sheet calls.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2013
    Posts
    3
    @healdem, thanks! I will try to remove the excel specific functions from the code then

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sorry. ,my bad
    if you are moving this function from excel; to Access then. given your current state of knowledge I'd suggest replacing the excel stuff with access stuff. the comment about keeping functions generalised as possible is for future reference so you cna re-use functions / code with the minimum of modification.

    natch to make this function worlk in Access you will need to repalce the excel specific stuff with access specific stuff

    Public Function ManglePartNumber(PartNumber As String, NoCharsToLeft As Integer, NoCharsToRight As Integer, CharstoInsert As String) As String
    'function to manipulate a string value
    'PartNumber is a string value to be mangled
    'NoCharstoLeft is the integer number of characters to retain from the LHS of the PartNumber
    'NoCharstoRight is the integer number of characters to retain from the RHS of the PartNumber
    'CharstoInsert is the sting to be inserted into the partnumber
    'usage ManglePartNumber("123456789", 4,2,"0500")
    'that means transform 123456789 into 1234050089
    'if we have invlaid data then return the partnumber as supplied
    ManglePartNumber = PartNumber 'setup our default return value
    'lets do some error checking
    'we must have a NoCharsToLeft and NoCharstoRight must be 0 or higher
    If NoCharsToLeft < 0 Or NoCharsToRight < 0 Then Exit Function
    'we must have something in PartNumber and CharstoInsert)
    If IsNull(PartNumber) Or Len(PartNumber) = 0 Then Exit Function
    If IsNull(CharstoInsert) Or Len(CharstoInsert) = 0 Then Exit Function
    'if the partnumber has less characters than we are supposed to be retaining then do nothing
    If Len(PartNumber) < NoCharsToLeft + NoCharsToRight Then Exit Function
    'ok so we've done the best we can to cater for user data errors
    ManglePartNumber = ""
    If NoCharsToLeft > 0 Then 'if we need to retain some characters frotm ehleft of the partnumber
    ManglePartNumber = Left(PartNumber, NoCharsToLeft)
    End If
    ManglePartNumber = ManglePartNumber & CharstoInsert
    If NoCharsToRight > 0 Then 'if we need to retain some characters from the right of the partnumber
    ManglePartNumber = ManglePartNumber & Right(PartNumber, NoCharsToRight)
    End If
    'return (ManglePartnumber)
    End Function

    as to how you apply it to your access form/query, thats up to you
    you could use the function is a query or a form

    in a query it could be something like
    update mytable set mypartnumber = manglepartnumber(mypartnumber,4,2,'0500') where len(mypartnumber) <=10
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2013
    Posts
    3
    healdem, thank you soooooo much for your detailed reply! It got me on the right track. I was confused before because I thought I could just get the VBA code to work in excel the same way it worked in Access, but now I understand.

    Once again, thank you so much, I had no idea where to start. Your reply is very helpful to me

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •