make a macro to insert a macro

[mithu]

is there anyway to insert a macro that puts a macro into another
file..

i currently have a macro that opens up other excel files.. I need to
insert a UDF into those xls files.. is there anyway to make a macro
create a module and insert code?

 

[Ron de Bruin]

Hi mithu

See this page from Chip Pearson
http://www.cpearson.com/excel/vbe.htm

 

[mithu]

The reason i need this is because i am editing a folder with out 300
excel files.. i know how to access all files one at a time.

i need to extract a number from cell a1 which is usually something
like "company name 34222 dkafaf" where i am extracting those numbers
in this case 34222.

i was told to use this function below in conjunction with vlookup.. so
like =vlookup(numberit(a1).....) this works great.. but i need this
function in all my files for it to work.

Function NumberIt(CompanyCell As Range) As Double
C = CompanyCell.Value
For x = 1 To Len(C)
If IsNumeric(Mid(C, x, 1)) Then n = n & Mid(C, x, 1)
Next x
NumberIt = --n
End Function


i place this function in my main excel spreadsheet that i run the
macro from..
it works fine if one of the files i am editing has that function in
it.. but it wont if it doesnt have that function..

so i am trying to write a code that will add module and add this
numberIT function to each of the spreadsheets..

i hope this makes sense.. please let me know if there is an easier
way.

 

[Ron de Bruin]

Do you only want the values from A1 of every excel file in the folder ???

 

[mithu]

Yes. the company code is in A1 along with some text.. I am able to
extract it using that function. but i need to put that function in
every spreadsheet.

is there anyway to have a global function that will work on any
spreadsheet i open?

 

[Ron de Bruin]

Yes you can put it in your personal.xls
http://www.rondebruin.nl/personal.htm

If you only want to have the value of A1 of each workbook you can create links to the cells
and then use the function in another cell.
You not have to open the workbooks then

See
http://www.rondebruin.nl/summary.htm

 

[mithu]

Thanks ron..

your the man

Yes you can put it in your personal.xlshttp://www.rondebruin.nl/personal.htm

If you only want to have the value of A1 of each workbook you can create links to the cells
and then use the function in another cell.
You not have to open the workbooks then

Seehttp://www.rondebruin.nl/summary.htm

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm







- Show quoted text -