(vba) edit module n macro in different access db

[SimonB1978]

Hi,

The way I do it is to open the text file in a string. I then do whatever I need with the string variable and then write it back to the file.

Simon B.

 

[xarxas]

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")

Hi there again!
hows ur weekend?

i got error in it " type mismatch " in Set XL = GetObject("C:\WorkbookName.xls")

 

[xarxas]

Hi,

The way I do it is to open the text file in a string. I then do whatever I need with the string variable and then write it back to the file.

Simon B.

Thank Simon, but how about if the file is really big? i mean if its exceeding the string max value?

 

[SimonB1978]

Hi xarxas,

And just what do you mean by really big? I actually did this with 100+mb text file... I doubt any module of macro will be that big...

From the MSDN website:
A string can contain from 0 to approximately 2 billion (2 ^ 31) Unicode characters.

Simon B.

 

[xarxas]

Thank Simon, will try it!

 

[xarxas]

Hi there again!
hows ur weekend?

i got error in it " type mismatch " in Set XL = GetObject("C:\WorkbookName.xls")

its working now thank