Avatar of Stuti Bhargava
Stuti Bhargava
Flag 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


VB ScriptMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
ste5an

8/22/2022 - Mon
ste5an

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.
Martin Liss

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.
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
Your help has saved me hundreds of hours of internet surfing.
fblack61
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.
Stuti Bhargava

ASKER
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


byundt

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. 

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Stuti Bhargava

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


byundt

You are correct. Code is permitted to be modified programmatically because the option is checked. 
Martin Liss

Try commenting out line 3.
Try commenting out line 4.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Stuti Bhargava

ASKER
Hi Martin,

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

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

Open in new window

Martin Liss

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

Stuti Bhargava

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.

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

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Martin Liss

If you are using VBA that shouldn't happen.

But in any case take a look at this article by Ron de Bruin.
Stuti Bhargava

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.

Martin Liss

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
ste5an

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question