Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi all,

    I've got a 2 part question, my end game is to create a module triggered by a click to prompt for Desktop Path, that will allow an operator to install a complex file processing access project on an unknown off site machine, which may have a different directory structure in XP or Windows 7.

    I can visualize most of the processes, but need to know how to:

    1) Access, read and change code in the modules (? Dim Module1 as object ? which parameter ?). I am trying to avoid programming in constants, as I want the installation to be upgradeable.

    2) Then how do I instruct the Access to Compile all the code <-- not essential just cant work it out.

    Thanks for your help I can clarify details if this is not clear.

  2. #2
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I've been working on this and and using a reference to "Microsoft Visual Basic for Applications Extensibility 5.3" have adapted some Excel Code I found online, but how do I rewrite the line in the module. Any Ideas?

    Public Function FindPath()
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule

    Dim I As Double
    Dim Line1 As String

    Set VBProj = VBE.VBProjects(1)
    Set VBComp = VBProj.VBComponents(23)
    Set CodeMod = VBComp.CodeModule
    With CodeMod
    For I = 1 To .CountOfLines
    DoEvents
    Line1 = .Lines(I, 1)
    If Left(Trim(Line1), 1) = "'" Then
    ElseIf InStr(Line1, "C:\") Then
    MsgBox (Line1)
    End If
    Next I
    End With
    End Function

  3. #3
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts
    OK sorry if anyones wasted anytime on this: sussed it. The code below is crude and I'm going to work it up, but it seems to work. Just need to use the .ReplaceLine


    Public Function ReplacePath(NewPath As String)
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
    Dim Rep As String
    Dim I As Double
    Dim Line1 As String

    Set VBProj = VBE.VBProjects(1)
    Set VBComp = VBProj.VBComponents("ModuleName")
    Set CodeMod = VBComp.CodeModule
    With CodeMod
    For I = 1 To .CountOfLines
    DoEvents
    Line1 = .Lines(I, 1)
    If Left(Trim(Line1), 1) = "'" Then
    ElseIf InStr(Line1, "C:\") Then
    Q = InStr(Line1, "C:\")
    P = InStr(Q, Line1, Chr(34))
    Rep = Mid(Line1, Q, P - Q)
    Line1 = Replace(Line1, Rep, NewPath)
    .ReplaceLine (I), Line1
    End If
    Next I
    End With
    End Function

  4. #4
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,514
    Thanks
    3
    Thanked 143 Times in 136 Posts
    I'm glad you managed to figure it out but I can't understand why you don't want to use a constant declaration and then replace that line. If it sits at the top of your module then you could specify the exact line number to replace and avoid the loop completely.

    I haven't been able to work out the mechanics of what you are doing with the undeclared Q and P constants but it appears that the file has to be saved onto a C:\ drive which may be unfortunate if the user chose to install on a separate drive.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,171
    Thanks
    200
    Thanked 781 Times in 715 Posts
    Quote Originally Posted by graliv1 View Post
    create a module triggered by a click to prompt for Desktop Path, that will allow an operator to install a complex file processing access project on an unknown off site machine, which may have a different directory structure in XP or Windows 7.
    If I understand your problem, a big if! I would suggest that you just write the path (I gather from your post that the operator {installer} would provide the drive\path...) to the Registry or a data file and then just read that location when the programs need the information. It seems like way to much work to write and maintain code to generate code when this much simpler path is available.

    RG

    Some Sample Code:
    Code:
                              +-------------------------+             +----------+
    '-------------------------|     cmdBrowse_Click()   |-------------| 08/11/05 |
    '                         +-------------------------+             +----------+
    'Called by  : Button Click: cbSetInstallPath
    'Notes      : Return only the Drive Letter & Colon
    
    Private Sub cbBrowse_Click()
    
        tbSourceDrive.Value = _
             Left(zGetDirectory("Select the BHSInstall Drive"), 2)
        
    End Sub                   'cmdBrowse_Click()
    
    
    '                         +-------------------------+             +----------+
    '-------------------------|     cmdBrowse_Click()   |-------------| 07/25/05 |
    '                         +-------------------------+             +----------+
    'Called by  : Button Click: cbSetInstallPath
    
    Private Sub cbBrowse_Click()
    
        tbInstallPath.Value = _
             zGetDirectory("Select a Location to Install the BHS System")
        
    End Sub                  'cmdBrowse_Click()
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. #6
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Andrew Lockton View Post
    I'm glad you managed to figure it out but I can't understand why you don't want to use a constant declaration and then replace that line. If it sits at the top of your module then you could specify the exact line number to replace and avoid the loop completely.

    I haven't been able to work out the mechanics of what you are doing with the undeclared Q and P constants but it appears that the file has to be saved onto a C:\ drive which may be unfortunate if the user chose to install on a separate drive.
    Thanks for your response.The reason I'm avoiding the constant is because theres are largish number of modules, however on second thoughts now I can auto recode the paths in modules it would be as easy to run the replace code to set up constants, Really don't want to rewrite and retest the processes GROAN!! <-- although probably will now.

    BTW the Q and P are selecting the path in Quotes (IE: FName = "C:\File.xls" Q = 1 P = 21 Q - P = 11 the mid it)

Posting Permissions

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