(vba) edit module n macro in different access db

[xarxas]

Dear all,

im stuck in vba for last 2 days.
i want to perform editing module(s) and macro(s) from different access database(s).

no progress in editing ( find/replace text ) macro,

well this is my code for replacing word in module ( please see attachment )
its only works when in the same database,
but as soon as i add 'Set Dbs = OpenDatabase(dbname) it didnt work..

any help would be great!

Thank!

 

[jdraw]

Dear all,

im stuck in vba for last 2 days.
i want to perform editing module(s) and macro(s) from different access database(s).

no progress in editing ( find/replace text ) macro,

well this is my code for replacing word in module ( please see attachment )
its only works when in the same database,
but as soon as i add 'Set Dbs = OpenDatabase(dbname) it didnt work..

any help would be great!

Thank!

Here is how I open a second database.

'----------------------------------------------------------------------------------
' Procedure : testdb
' Author    : Jack
' Created   : 12/8/2009
' Purpose   : Test opening second A2003 database.
'----------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Sub testdb()

Dim dbsCurrent As DAO.Database
Dim dbsSecond As DAO.Database
Dim tbl As DAO.TableDef
   On Error GoTo testdb_Error

Set dbsCurrent = CurrentDb
Set dbsSecond = DBEngine.Workspaces(0).OpenDatabase("d:\a2k\db1.mdb")
Debug.Print dbsCurrent.Name
Debug.Print dbsSecond.Name

For Each tbl In dbsSecond.TableDefs
Debug.Print "  " & tbl.Name
Next
Debug.Print Now()

   On Error GoTo 0
   Exit Sub

testdb_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure testdb of Module Module5"

End Sub

 

[xarxas]

Thank jdraw, but when i see your code, thats for table... what i want is for the module and macro. i did manage to make one for table, query but not for module and macro.

 

[SOS]

Are you REPLACING the entire module? If so, just use DoCmd.TransferDatabase to send the object over. You can delete the original objects by using an Access Application object.

 

[xarxas]

Hi sos thank for the reply.
yes im replacing some of "text" in module and macro, like find and replace in MSWORD.
but im replacing it in diffrent database, without deleting the original object
hmm.. can i use DAO ?

 

[jdraw]

Thank jdraw, but when i see your code, thats for table... what i want is for the module and macro. i did manage to make one for table, query but not for module and macro.

I was just showing code to open a second database.

 

[SimonB1978]

Hi,

The easiest solution would be to import it in your DB, then make the changes (as local object), and export it back to its original place...

You'll still have to figure out how to edit a module/macro via VBA... And if you do know I'd be interested in knowing how to do it!

Simon B.

 

[vbaInet]

Good thinking SimonB.

xarxas: It's just not worth the hassle in my opinion. I would imagine it's a security measure not to allow amendment of code/macro operations from outside the Project.

In any case, try opening the database in EXCLUSIVE MODE. It's one of the opendatabase arguments.

 

[DJkarl]

This is just air code, but should get you looking in the right direction.

Sub testApp()
Dim msACC As Access.Application
Set msACC = GetObject("C:\PathToDatabase\myDB.mdb")
Call msACC.Modules.Item("ModuleName").ReplaceLine(LineNumber, "ReplacementString")
msACC.Quit
Set msACC = Nothing
End Sub

 

[xarxas]

This is just air code, but should get you looking in the right direction.

Sub testApp()
Dim msACC As Access.Application
Set msACC = GetObject("C:\PathToDatabase\myDB.mdb")
Call msACC.Modules.Item("ModuleName").ReplaceLine(LineNumber, "ReplacementString")
msACC.Quit
Set msACC = Nothing
End Sub

Thank you! indeed it lead me to right direction, the find and replacing module is working now. do you know if i can apply the same method to macro?

 

[DJkarl]

Thank you! indeed it lead me to right direction, the find and replacing module is working now. do you know if i can apply the same method to macro?

Macros are tricky to edit via code, you cannot edit them in the same way(that I know of). The only way I've found to edit them is to export one to a text file, edit the text file, then delete the macro and import the new text file.

Export to text file

Application.SaveAsText acMacro, "mcr_MyMacro", "C:\Temp\MyMacro.txt"

Make edits to the text file here...then delete the existing macro.

Load from text file

Application.LoadFromText acMacro,"mcr_MyMacro", "C:\Temp\MyMacro.txt"

If SaveAsText and LoadFromText do not appear then hit F2, right click and select ShowHiddenMembers.

 

[xarxas]

Macros are tricky to edit via code, you cannot edit them in the same way(that I know of). The only way I've found to edit them is to export one to a text file, edit the text file, then delete the macro and import the new text file.

Export to text file

Application.SaveAsText acMacro, "mcr_MyMacro", "C:\Temp\MyMacro.txt"

Make edits to the text file here...then delete the existing macro.

Load from text file

Application.LoadFromText acMacro,"mcr_MyMacro", "C:\Temp\MyMacro.txt"

If SaveAsText and LoadFromText do not appear then hit F2, right click and select ShowHiddenMembers.

Thank for the tips.
Do you know if i can use Application.SaveAsText and Application.LoadFromText from other database?
like
Set msACC = GetObject(dbname)
mcACC.LoadFromText ? or mcACC.SaveAsText ?

 

[DJkarl]

Yes you can, they are just methods of the Application object, so if your object represents another database it would still work the same.

 

[xarxas]

Yes you can, they are just methods of the Application object, so if your object represents another database it would still work the same.

how can i delete the macro in other db?

 

[DJkarl]

Application.DoCmd.DeleteObject

 

[xarxas]

Application.DoCmd.DeleteObject

ouch thank! thats hit me on the head

another question, right now i use MSWORD to find and replace in txt file. do you know the better way to do it?

 

[DJkarl]

If MS Word is working for you then just use that, you could look at the Open and Line Input methods of file manipulation, or use a file scripting object, but if you aren't familiar with them then I'm not sure how they would benefit you if you already have a working solution.

 

[xarxas]

ok i will use msword for now. thank!!
hmm another question, can i do this also for excel file instead of access?

 

[DJkarl]

The object model in Excel is different than Access. Macros in Excel are pretty much VBA functions / subs so you need to access them as such.

Look at the VBProject and CodeModule properties off the workbook object, there are multiple methods, some add in new modules, other just add in lines of code, etc.

Dim XL As Excel.Application
Set XL = GetObject("C:\WorkbookName.xls")
XL.Workbooks("WorkBookName").VBProject.VBComponents.Item("ModuleName").CodeModule.AddFromString ("Line to Insert")

 

[xarxas]

thank! i will try it and for sure will get back to you!