Stuti Bhargava
asked on
vbscript error : object doesn't support property or method 'vbproject'
I have a VB script where I am trying to import macro into excel but I am keep error saying "object doesn't support property or method 'vbproject'"
VBscript:-
ednetmanFormatExcel "C:\Users\bhargavas\Desktop\RM Analysis.xlsx"
Sub ednetmanFormatExcel(ByVal vFilePath)
Set xlApp = CreateObject("excel.application")
xlApp.Visible = False
xlApp.DisplayAlerts = False
Set objWorkbook = xlApp.Workbooks.Open(vFilePath) 'open file
For Each objWorksheet in objWorkbook.Worksheets
xlUp = -4162
objWorksheet.Activate
If objWorksheet.Name = "RM Analysis" then
objWorksheet.AutoFilterMode = False
With objWorkbook.ActiveSheet
.VBProject.VBComponents.Import "C:\Users\bhargavas\Desktop\Macro\Module1.bas"
End With
ELSE
objWorksheet.AutoFilterMode = False
Set LastCell = objWorksheet.Range("A" & objWorksheet.Rows.Count).End(xlUp)
With objWorkbook.ActiveSheet
.Range("A4:X" & LastCell.Row).EntireRow.AutoFit
End With
End If
With objWorkbook.ActiveSheet
.Range("A1").Select
End With
Next
objworkbook.worksheets(1).Activate
objWorkbook.SaveAs "C:\Users\bhargavas\Desktop\RM Analysis.xlsm"
objWorkbook.Close True 'save and close
xlApp.Quit
End Sub
You don't need to import a macro through code. If you put that macro in your Personal.xlsb file it will be available to all your workbooks. You could also put the macro into its own Module and the Export the module and Import it where you want it.
Please show a larger piece of your code so we can troubleshoot the problem, and explain what you are trying to accomplish.
»bp
»bp
ASKER
Sorry for not adding more details earlier
I am not sure the Personal.xlsb will work for me as what I am trying to do is add the Macro on my Excel report which generated weekly and then send it to some set of user's.
My Macro is adding the feature to double click on the data and filter the data in next sheet in the same excel.
I am not sure the Personal.xlsb will work for me as what I am trying to do is add the Macro on my Excel report which generated weekly and then send it to some set of user's.
My Macro is adding the feature to double click on the data and filter the data in next sheet in the same excel.
ASKER
My Code is this:-
ednetmanFormatExcel "C:\Users\bhargavas\Desktop\RM Analysis.xlsx"
ednetmanFormatExcel "C:\Users\bhargavas\Desktop\RM Analysis.xlsx"
Sub ednetmanFormatExcel(ByVal vFilePath)
Set xlApp = CreateObject("excel.application")
xlApp.Visible = False
xlApp.DisplayAlerts = False
Set objWorkbook = xlApp.Workbooks.Open(vFilePath) 'open file
For Each objWorksheet in objWorkbook.Worksheets
xlUp = -4162
objWorksheet.Activate
If objWorksheet.Name = "RM Analysis" then
objWorksheet.AutoFilterMode = False
With objWorkbook.ActiveSheet
.VBProject.VBComponents.Import "C:\Users\bhargavas\Desktop\Macro\Module1.bas"
End With
ELSE
objWorksheet.AutoFilterMode = False
Set LastCell = objWorksheet.Range("A" & objWorksheet.Rows.Count).End(xlUp)
With objWorkbook.ActiveSheet
.Range("A4:X" & LastCell.Row).EntireRow.AutoFit
End With
End If
With objWorkbook.ActiveSheet
.Range("A1").Select
End With
Next
objworkbook.worksheets(1).Activate
objWorkbook.SaveAs "C:\Users\bhargavas\Desktop\RM Analysis.xlsm"
objWorkbook.Close True 'save and close
xlApp.Quit
End Sub
By default, access to the VBProject is turned off. Even if macros are enabled, the VBA code is protected from modification. You can permit access to VBProject in File…Options…Trust Center…Trust Center Settings…Macro Settings as I recall.
ASKER
Thanks Byundt for the reply. I believe it's already enabled. Please find attach the screen shot for the same:-
You are correct. Code is permitted to be modified programmatically because the option is checked.
Try commenting out line 3.
Try commenting out line 4.
Try commenting out line 4.
ASKER
Hi Martin,
I tried to comment below code but still getting same error.
I tried to comment below code but still getting same error.
'xlApp.Visible = False
'xlApp.DisplayAlerts = False
Try this. I explicitly defined variables and formatted your code so that it's more readable.
Sub ednetmanFormatExcel(ByVal vFilePath)
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim rngLastCell As Range
Set xlApp = CreateObject("excel.application")
xlApp.Visible = False
xlApp.DisplayAlerts = False
Set xlWorkBook = xlApp.Workbooks.Open(vFilePath) 'open file
For Each xlSheet In xlWorkBook.Worksheets
xlSheet.Activate
If xlSheet.Name = "RM Analysis" Then
xlSheet.AutoFilterMode = False
With xlWorkBook.ActiveSheet
.VBProject.VBComponents.Import "C:\Users\bhargavas\Desktop\Macro\Module1.bas"
End With
Else
xlSheet.AutoFilterMode = False
Set rngLastCell = xlSheet.Range("A" & xlSheet.Rows.Count).End(xlUp)
With xlWorkBook.ActiveSheet
.Range("A4:X" & rngLastCell.Row).EntireRow.AutoFit
End With
End If
With xlWorkBook.ActiveSheet
.Range("A1").Select
End With
Next
xlWorkBook.Worksheets(1).Activate
xlWorkBook.SaveAs "C:\Users\bhargavas\Desktop\RM Analysis.xlsm"
xlWorkBook.Close True 'save and close
xlApp.Quit
End Sub
ASKER
Hi Martin,
Thanks for sharing the code. But I got the compilation error for following lines. After I commented them I got the same error on vbproject.
Thanks for sharing the code. But I got the compilation error for following lines. After I commented them I got the same error on vbproject.
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim rngLastCell As Range
If you are using VBA that shouldn't happen.
But in any case take a look at this article by Ron de Bruin.
But in any case take a look at this article by Ron de Bruin.
ASKER
Hi Martin,
I am not use what you mean by VBA. I am saving the above code as .vbs and trying to run the same to import Macro.
I am not use what you mean by VBA. I am saving the above code as .vbs and trying to run the same to import Macro.
I'm sorry for the confusion. I answer a lot of questions and I find that many people don't know the difference between vbscript and I unthinkingly thought you meant VBA.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Craft and post a concise and complete example instead. Explain more about the context.
Right now it is as the error messages says: .VBProject is not used in a scope where a VBProject property or function exists.