UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Use Access To Edit Vba In Excel File(s), Access 2016    
 
   
aggiemarine07
post Oct 4 2019, 11:22 AM
Post#1



Posts: 75
Joined: 7-January 11



Howdy everyone!

I have a general question for the group. Is it possible to write some code in MS Access to edit VBA stored in an Excel Worksheet? I've read stuff online about being able to clear VBA from workbooks/worksheets but nothing to edit it.

The problem I am trying to solve is to edit the code (below) which is stored on the "PLOG" worksheet stored in 500+ excel workbooks. Specifically, I need to change
CODE
c.Column + 255
to
CODE
c.Column + 256
and have that code loop (with sub-folders) through all 500+ files.

CODE
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim c As Range
  If Not Intersect(Target, Range("A:CR")) Is Nothing Then
    For Each c In Target
      If c.Value <> "" Then Cells(c.Row, c.Column + 255) = Now()
    Next
  End If
End Sub


In hindsight, I think it would have been better for me to put this code in a module vice in the actual worksheet since I have found an number of places (like Ron de Bruin: https://www.rondebruin.nl/win/s9/win002.htm) who have written code that allows for the export/import of modules fairly easily but c'est la vie smile.gif

Would it be better to go that route (clear all VBA in all 500+ workbooks and then import a module)? How do I translate the above code from a private to a public sub? Thanks in advance!
This post has been edited by aggiemarine07: Oct 4 2019, 11:23 AM
Go to the top of the page
 
ADezii
post Oct 4 2019, 02:37 PM
Post#2



Posts: 2,700
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
Is it possible to write some code in MS Access to edit VBA stored in an Excel Worksheet?

Yes, it is possible using the Microsoft Visual Basic For Applications Extensibility 5.3 Object Library. If you like, I can show you how you can change a line of Code in an External Workbook, just let me know.
This post has been edited by ADezii: Oct 4 2019, 02:37 PM
Go to the top of the page
 
aggiemarine07
post Oct 4 2019, 04:15 PM
Post#3



Posts: 75
Joined: 7-January 11



Yes please! Or at least point to a how-to where I can read about it to learn more and see some examples smile.gif
Go to the top of the page
 
isladogs
post Oct 4 2019, 05:29 PM
Post#4


UtterAccess VIP
Posts: 1,879
Joined: 4-June 18
From: Somerset, UK


The late Chip Pearson wrote an excellent guide to using VBE Extensibility. See http://www.cpearson.com/excel/vbe.aspx

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
ADezii
post Oct 4 2019, 05:36 PM
Post#5



Posts: 2,700
Joined: 4-February 07
From: USA, Florida, Delray Beach


I actually had a working Demo in work that will hopefully illustrate how you can modify Code in an External Excel Workbook from Access Automation Code. I thought that I had attached the Demo to an E-Mail to myself, but I attached the wrong Files. When I get to work tomorrow, I'll see what I can come up with and let you know. The Code relies heavily on the Microsoft Visual Basic For Applications Extensibility 5.3 Object Library which is what you should be looking into. See you soon.
Go to the top of the page
 
ADezii
post Oct 4 2019, 06:00 PM
Post#6



Posts: 2,700
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. Thanks to isladogs and the late Chip Pearson I was able to reproduce a 'crude' Demo in about the 2 minutes.
  2. The Demo will:
    1. Open the Workbook C:\Test\Book1.xlsm using Automation Code.
    2. Search for the String 'Dim intCtr As Integer' in the Code Module of Sheet1.
    3. If found, replace this String with 'Dim lngCtr as Long'.

    CODE
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
    Dim appExcel As Excel.Application
    Dim wkb As Excel.Workbook
    Dim intNumOfLines As Integer
    Dim intCtr As Integer

    Const conFIND = "Dim intCtr As Integer"
    Const conREPLACE = "Dim lngCtr as Long"

    Set appExcel = New Excel.Application
        appExcel.Visible = True

    Set wkb = appExcel.Workbooks.Open("C:\Test\Book1.xlsm", , False)

    Set VBProj = wkb.VBProject
    Set VBComp = VBProj.VBComponents("Sheet1")
    Set CodeMod = VBComp.CodeModule

    intNumOfLines = CodeMod.CountOfLines

    For intCtr = 1 To intNumOfLines
      If CodeMod.Find(conFIND, 1, 1, -1, -1, False, False) Then
        CodeMod.ReplaceLine intCtr, conREPLACE
          Exit For
      End If
    Next

    wkb.Save
    wkb.Close
    appExcel.Quit
    Set appExcel = Nothing
    Set VBProj = Nothing
    Set VBComp = Nothing
    Set CodeMod = Nothing
  3. This should hopefully pointt you in the right direction.

This post has been edited by ADezii: Oct 4 2019, 06:01 PM
Go to the top of the page
 
dmhzx
post Oct 5 2019, 02:21 AM
Post#7



Posts: 7,115
Joined: 22-December 10
From: England


Had you thought about writing a new Excel file and putting the VBA in that?

This sounds like a one off, and it may be easier to do it all in Excel.

I don't actually know, as I've never tried it but it 'feels' as though using Access is an unnecessary complication

Mind you , looking at the solution offered, the code would be almost identical anyway.
This post has been edited by dmhzx: Oct 5 2019, 02:24 AM
Go to the top of the page
 
aggiemarine07
post Oct 7 2019, 08:14 AM
Post#8



Posts: 75
Joined: 7-January 11



@isladogs - thanks! I will read that and get myself educated on this smile.gif

@dmhzx - you are correct that it does make thing slightly more complicated but once I finished adjusting the excel sheets, I have them imported into a table in order to QC the fields and make sure nothing got seriously jacked up.

@Adezii - thank you! I will try this out when I get a chance at work today. Do you think its more sustainable/future-proofing to just move all of this code (and subsequently convert the code to public sub's) to a module and then import that each when adjustments need to be made? If that is the better long term solution then I would prefer to go with that.
Go to the top of the page
 
ADezii
post Oct 7 2019, 11:24 AM
Post#9



Posts: 2,700
Joined: 4-February 07
From: USA, Florida, Delray Beach


I made some enhancements to the Code, most notable of which is the encapsulating of Code Logic into a Sub-Procedure where it could more easily be called. In this manner, you could more easily process multiple Workbooks. Have a look and see if it suits your needs.
CODE
Public Sub ModifyExcelCode(strWBPath As String, strComponent As Variant, strFind As String, strReplace As String)
Dim appExcel As Excel.Application
Dim wkb As Excel.Workbook
Dim oProj As VBIDE.VBProject
Dim oComp As VBIDE.VBComponent
Dim oMod As VBIDE.CodeModule
Dim lngStartLine As Long
Dim lngEndLine As Long
Dim lngStartCol As Long
Dim lngEndCol As Long
Dim blnFound As Boolean
Dim lngIndex As Long

Set appExcel = New Excel.Application
Set wkb = appExcel.Workbooks.Open(strWBPath, , False)

appExcel.Visible = True

Set oProj = wkb.VBProject
Set oComp = oProj.VBComponents(strComponent)
Set oMod = oComp.CodeModule

With oMod
  lngStartLine = 1
  lngEndLine = .CountOfLines
  lngStartCol = 1
  lngEndCol = 255
  
  blnFound = .Find(strFind, lngStartLine, lngStartCol, lngEndLine, _
              lngEndCol, False, False, False)
  
  Do Until blnFound = False
    'MsgBox "Found at line: " & CStr(lngStartLine) & " | " & "Column: " & CStr(lngStartCol)
    .ReplaceLine lngStartLine, strReplace
    lngEndLine = .CountOfLines
    lngStartCol = lngEndCol + 1
    lngEndCol = 255
    
    blnFound = .Find(strFind, lngStartLine, lngStartCol, lngEndLine, _
                lngEndCol, False, False, False)
  Loop
End With

wkb.Close (True)
appExcel.Quit
Set appExcel = Nothing
Set oProj = Nothing
Set oComp = Nothing
Set oMod = Nothing
End Sub

CODE
Call ModifyExcelCode("C:\Test\Book1.xlsm", "Module1", "Private blnFileFound As Boolean", "Public blnFileFound As Boolean")

This post has been edited by ADezii: Oct 7 2019, 11:24 AM
Go to the top of the page
 
dmhzx
post Oct 8 2019, 05:02 AM
Post#10



Posts: 7,115
Joined: 22-December 10
From: England


Not sure if this helps, but the call routine could be within a for/next loop

Or if you have the sheet paths and names in an access table by looping through a record-set.

Go to the top of the page
 
ADezii
post Oct 8 2019, 06:48 AM
Post#11



Posts: 2,700
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
but the call routine could be within a for/next loop

As dmhzx has pointed out, this is the primary reason for using a Sub-Routine. It all the Workbooks have the same Extension (.xlsx, .xlsm), then it could be a simple as recursively calling the Dir() Function.
Go to the top of the page
 
aggiemarine07
post Oct 8 2019, 04:00 PM
Post#12



Posts: 75
Joined: 7-January 11



@ADezii - thanks for the code, copied it over and adjusted it to my test files but it gives a "subscript out of range" error on
CODE
Set oComp = oProj.VBComponents(strComponent)


I believe this is because its looking for "Module 1" in my excel file but these excel files have no modules within them. The VBA I am trying to edit is in Sheet2 of the workbook named "WFM PLOG"

How do I adjusted it for this?
Go to the top of the page
 
ADezii
post Oct 8 2019, 04:32 PM
Post#13



Posts: 2,700
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
I believe this is because its looking for "Module 1" in my excel file but these excel files have no modules within them.

You are correct, substitute 'Sheet2' for 'Module1' which has it's own Code Component.
This post has been edited by ADezii: Oct 8 2019, 04:33 PM
Go to the top of the page
 
aggiemarine07
post Oct 8 2019, 05:00 PM
Post#14



Posts: 75
Joined: 7-January 11



@ADezii - is it possible to base it off of sheet name? I spot checked some of the other excel files and while the sheet name ("WFM PLOG") is the same, the sheet number is different (in some workbooks its "Sheet2" and others its "Sheet4"
Go to the top of the page
 
ADezii
post Oct 9 2019, 06:30 AM
Post#15



Posts: 2,700
Joined: 4-February 07
From: USA, Florida, Delray Beach


I am a little confused that I am assuming that the Workbook Names will always be 'WFM PLOG', but the Code that you are wishing to change will be in either Sheet2 or Sheet4, bit not both? This definitely adds to the complexity but I'll see what I can come up with. If my previous assumption is not correct, I will need to know now. Will keep in touch.
Go to the top of the page
 
ADezii
post Oct 9 2019, 07:58 AM
Post#16



Posts: 2,700
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. I made the 'MAJOR ASSUMPTION' that, in your Workbooks, either Sheet2 or Sheet4 must exist, if not an Error will be generated.
  2. Changed the Logic and Call in the Sub-Routine ModifyExcelCode which will now be ModifyExcelCode2(). Error Checking has now been added as well as Code to check for the existence of Sheet2, if it does not exist then Sheet4 will be used as the Component.
  3. Created a Function (fDoesWorksheetExist()) that checks for the existence of Sheet2. It is called within ModifyExcelCode2() and. depending on its outcome, the appropriate Component is used.
  4. Revised Sub-Routine:
    CODE
    Public Sub ModifyExcelCode2(strWBPath As String, strFind As String, strReplace As String)
    On Error GoTo Err_ModifyExcelCode2
    Dim appExcel As Excel.Application
    Dim wkb As Excel.Workbook
    Dim oProj As VBIDE.VBProject
    Dim oComp As VBIDE.VBComponent
    Dim oMod As VBIDE.CodeModule
    Dim lngStartLine As Long
    Dim lngEndLine As Long
    Dim lngStartCol As Long
    Dim lngEndCol As Long
    Dim blnFound As Boolean
    Dim strComponent As String

    Set appExcel = New Excel.Application
    Set wkb = appExcel.Workbooks.Open(strWBPath, , False)

    appExcel.Visible = True

    If fDoesWorksheetExist("Sheet2", wkb) Then      'Does Sheet2 exist in WFM PLOG.xlsm?
      strComponent = "Sheet2"
    Else                                            'Sheet2 does NOT exist, but Sheet4 does
      strComponent = "Sheet4"
    End If

    Set oProj = wkb.VBProject
    Set oComp = oProj.VBComponents(strComponent)
    Set oMod = oComp.CodeModule

    With oMod
      lngStartLine = 1
      lngEndLine = .CountOfLines
      lngStartCol = 1
      lngEndCol = 255
      
      blnFound = .Find(strFind, lngStartLine, lngStartCol, lngEndLine, _
                  lngEndCol, False, False, False)
      
      Do Until blnFound = False
        'MsgBox "Found at line: " & CStr(lngStartLine) & " | " & "Column: " & CStr(lngStartCol)
        .ReplaceLine lngStartLine, strReplace
        lngEndLine = .CountOfLines
        lngStartCol = lngEndCol + 1
        lngEndCol = 255
        
        blnFound = .Find(strFind, lngStartLine, lngStartCol, lngEndLine, _
                    lngEndCol, False, False, False)
      Loop
    End With

    Exit_ModifyExcelCode2:
      wkb.Close (True)
      appExcel.Quit
      Set appExcel = Nothing
      Set oProj = Nothing
      Set oComp = Nothing
      Set oMod = Nothing
        Exit Sub

    Err_ModifyExcelCode2:
      MsgBox Err.Description, vbExclamation, "Error in ModifyExcelCode2()"
        Resume Exit_ModifyExcelCode2
    End Sub
  5. Function Definition:
    CODE
    Public Function fDoesWorksheetExist(strWorksheet As String, ByVal oWkb As Excel.Workbook) As Boolean
    Dim wks As Excel.Worksheet

    fDoesWorksheetExist = False     'Initialize

    For Each wks In oWkb.Worksheets
      If wks.Name = strWorksheet Then
        fDoesWorksheetExist = True
          Exit Function
      End If
    Next
    End Function
  6. Code to Call Sub-Routine:
    CODE
    Private Sub CommandButton5_Click()
    Dim strFileName As String
    '******************* USER DEFINED *******************
    Const conPATH = "C:\Test\WFM PLOG.xlsm"
    Const conFIND = "Private blnFileFound As Boolean"
    Const conREPLACE = "Public blnFileFound As Boolean"
    '****************************************************

    Call ModifyExcelCode2(conPATH, conFIND, conREPLACE)
    End Sub
  7. Realizing the utter confusion that I probably just generated, if you are still having problems, I should be able to create a workable Demo for you that should clarify matters.
Go to the top of the page
 
dmhzx
post Oct 9 2019, 09:40 AM
Post#17



Posts: 7,115
Joined: 22-December 10
From: England


Hope this helps your thoughts.

Put all the workbook file names with paths into an Access table, with fields for
FullFileName
SheetName ( the sheet with the code you want to work on)
ModuleName (if some of the modules are different names)

The you can loop through the recordset, passing the file, sheet, and module each time.

Subscript out of range usually means that Excel can't actually find what you've asked it to look for.

Note that if you look for a sheet called "Month1", it will not be found if the name of the sheet is "Month1 " (note the trailing space.- Excel can see it, but the users can't)

In this case I think you have a good reason for driving this from Access, since if you decide to do more sheets, you only need to update the table with new records.

David
Go to the top of the page
 
ADezii
post Oct 9 2019, 10:47 AM
Post#18



Posts: 2,700
Joined: 4-February 07
From: USA, Florida, Delray Beach


@dmhzx:
It appears that the Worksheets are either named Sheet2 or Sheet4 and apparently this fact is not known ahead of time as to which Sheet the Code that needs to be changed resides. I was under the assumption that the Workbook name was constant, or at least a portion of it was. The problem is that there are over 500 Workbooks in where the Code needs to be changed. To complicate matters further, the Code will also have to iterate all Sub-Folders under the Main Folder.

@aggiemarine07:
At this stage, I strongly suggest that these previously points be either verified or not, and if not verified, kindly provide the correct information. I also think that dmhzx's suggestion of pre-populating a Table with all required information, then processing this information in a Recordset is a sound approach.
This post has been edited by ADezii: Oct 9 2019, 11:00 AM
Go to the top of the page
 
dmhzx
post Oct 10 2019, 08:58 AM
Post#19



Posts: 7,115
Joined: 22-December 10
From: England


It should be possible to loop through all the module in all the sheets then.
Is what we're looking for always the same - I seem to recall that it's a specific line where 255 needs to be changed to 256

I know I can rebuild all pivot tables in an entire workbook with about five lines of code.

CODE
for each sheet in workbook. sheets
   for each pivottable in sheet.pivottables
    refresh
  next pivot table
next  sheet
(That's not actual code, but an 'in principle' approach)

Going through all the subfolders can be done with recursive calls. - I can probably help with that.

Go to the top of the page
 
aggiemarine07
post Oct 10 2019, 11:56 AM
Post#20



Posts: 75
Joined: 7-January 11



@dmhzx & ADezii - thank yall for all of yalls hardwork in helping me sort out this problem! To answer yalls questions:

I have a folder titled "PLOGs" where about 500+ excel workbooks exists with various names. All of them need to have the VBA in them altered.

With that said, all of these workbooks have a worksheet named "WFM PLOG" but the number associated with that worksheet various from workbook to workbook as I recently had to transition all of these to a standard format (it took about 6 hours of my computer "translating" everything). In some of these workbooks its Sheet1, in others its Sheet2, and yet others its Sheet3; it should not be greater than Sheet10.

I really dont want to keep wasting yalls time re-writing code. Would it not be easier to just loop through all excel files and remove all code, then loop through them again to add the new code? remove all code and then add a module?
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    6th December 2019 - 11:34 PM