update procedure in excel file from personal.xls

[tommy1234]

Hello

I wrote a procedure in the past in excel template file (the procedure is in each excel file) and now due to changes my company done i have to update data in each on of the files (there are more than 100).
to solve this problem i created a module (with 4 procedures) on personal.xls that update each of the files. the problem is that a get an error on the varaible declaration ("user-defined...) what's the problem with the code? why it can't run from personal.xls ?

thank you



Sub Update_Existing_Data()
Dim VBProj As VBIDE.VBProject '=>i get an error on this line
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
Const DQUOTE = """"

Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("Module3")
Set CodeMod = VBComp.CodeModule

With CodeMod
LineNum = 7
'insert the new line
.InsertLines LineNum, "//nt7/data_analysis/budget/bfg_01.xls"
End With

End Sub


Hello

i wrote a code that import .txt file into excel as a new module.
the problem is that i can use the code only within excel and not from personal.xls

Sub Import_SAP_Module()
ActiveWorkbook.Activate
Dim source_path$, Title$, filename$, message Message As bMsgBoxResult
'Path of the imported file
source_path = "c:\test_folder\SAP_Import1.txt"
filename = source_path
On Error GoTo Finish
Application.VBE.ActiveVBProject.VBComponents.Import
message = MsgBox("SAP module - import" & vbCrLf & "was completed", vbInformation, "Information")
Exit Sub
'in case of error write message and exit sub
Finish:
MsgBox "Please check if the file exist" & Chr(10) & "or make sure the path is correct"
Exit Sub
End Sub


Thank you

hello
i worte a code that run ok from any excel file. the problem is that i want to run it from personal.xls
what should i do in order to fix the code that it can be run from personal.xls

thanks



Sub Import_SAP_Module()
ActiveWorkbook.Activate
Dim source_path$, Title$, filename$, message Message As bMsgBoxResult
'Path of the imported file
source_path = "c:\test_folder\SAP_Import1.txt"
filename = source_path
On Error Goto Finish
Application.VBE.ActiveVBProject.VBComponents.Import
message = MsgBox("SAP module - import" & vbCrLf & "was completed", vbInformation, "Information")
Exit Sub
'in case of error write message and exit sub
Finish:
MsgBox "Please check if the file exist" & Chr(10) & "or make sure the path is correct"
Exit Sub
End Sub


Hello
i'm tring to remove VBproject password and run aother module on the excel.
the problem is that i can't remove the password (i have the password), the password is written on the excel worksheet and not VBproject properties.

Please Help !!!!
Thanks


Sub UnprotectProject()
With Application
'Go to the VBE
.SendKeys "%{F11}", True
'Activate Project Explorer window
.SendKeys "^r", True
.SendKeys "{TAB}", True
.SendKeys "{TAB}", True
'Enter
.SendKeys "~", True
'my password
.SendKeys "a12b1", True
'Enter again
.SendKeys "~", True
'close vba Window
.SendKeys "%f", True
.SendKeys "c", True
End With
End Sub


hello
how do i activate the vbe window ?
i open a workbook and i'm trying to run code from personal.xls that will change a few line on the workbook i opened.
i tried to use sendkeys, but its not so good, is there another way for doing that.
the workbook modules are protected (i have the unprotect code)

i have to solve it as soon as possible, can someone help me ?

thanks

[Bob Phillips]

Just late bind it all



Sub Update_Existing_Data()
Dim VBProj As Object
Dim VBComp As Object
Dim CodeMod As Object
Dim LineNum As Long
Const DQUOTE = """"

Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("Module3")
Set CodeMod = VBComp.CodeModule

With CodeMod
LineNum = 7
'insert the new line
.InsertLines LineNum, "//nt7/data_analysis/budget/bfg_01.xls"
End With

End Sub

[mdmackillop]

Tommy,
I've merged all these posts as they all seem to relate to the same issue. Please respond to posted answers.
MD