Link to home
Start Free TrialLog in
Avatar of Stuti Bhargava
Stuti BhargavaFlag for United States of America

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

Open in new window


Avatar of ste5an
ste5an
Flag of Germany image

And how does posting a fragment of a code line helps here to explain what you're dong?

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.
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.
Avatar of Bill Prew
Bill Prew

Please show a larger piece of your code so we can troubleshoot the problem, and explain what you are trying to accomplish.

»bp
Avatar of Stuti Bhargava

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.
My Code is this:-

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

Open in new window


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. 

Thanks Byundt for the reply. I believe it's already enabled. Please find attach the screen shot for the same:-

User generated image
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.
Hi Martin,

I tried to comment   below code but still getting same error.

'xlApp.Visible = False
   'xlApp.DisplayAlerts = False 

Open in new window

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

Open in new window

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.

Dim xlApp As Excel.Application 
Dim xlWorkBook As Excel.Workbook 
Dim xlSheet As Excel.Worksheet 
Dim rngLastCell As Range

Open in new window

If you are using VBA that shouldn't happen.

But in any case take a look at this article by Ron de Bruin.
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'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
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial