tommy1234
10-01-2009, 03:00 PM
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
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