Getting "Object Variable or With Block Variable not set" error

[Suraj]

Hi,

I am getting the error "Object Variable or With Block Variable not set"
when I try to execute following VBA code.

Dim a As Excel.Application
Dim wb As Workbook

For Each wb In a.Workbooks
wb.Save
Next wb

Please let me know what is the problem

Thanks,
suraj

[Jim Thomlinson]

Why are you wanting an new instance of the excel application?

Dim a As Excel.Application

You never create the instance. So it does not know what a is. You can
probably just remove the a...

Dim wbk As Workbook

For Each wbk In Workbooks
MsgBox wbk.Name
Next wbk
--
HTH...

Jim Thomlinson


"Suraj" wrote:

> Hi,
>
> I am getting the error "Object Variable or With Block Variable not set"
> when I try to execute following VBA code.
>
> Dim a As Excel.Application
> Dim wb As Workbook
>
> For Each wb In a.Workbooks
> wb.Save
> Next wb
>
> Please let me know what is the problem
>
> Thanks,
> suraj
>
>

[Andrew Taylor]

The line
Dim a As Excel.Application
only declares a as being of that type, it doesn't create an Excel
object
(so a is Nothing, which is the reason for the error)

If you're using VBA within Excel the Application object already exists,
so you can just say

For Each wb In Application.Workbooks
' or even just For Each wb In Workbooks
wb.Save
Next wb

Hope this helps
Andrew

Suraj wrote:
> Hi,
>
> I am getting the error "Object Variable or With Block Variable not set"
> when I try to execute following VBA code.
>
> Dim a As Excel.Application
> Dim wb As Workbook
>
> For Each wb In a.Workbooks
> wb.Save
> Next wb
>
> Please let me know what is the problem
>
> Thanks,
> suraj

[Suraj]

Thanks Jim and Andrew.
Jim,
I tried in the way you suggested, but the statements inside the For
Loop are not being executed. This time I didn't get any error. Pls let
me know what to do. I am using a tool called "TestPartner" to test the
applications. I am using the VBA in that tool.

Andrew,
I am using VBA but not within ExcelApplication. I am using a tool
called "TestPartner" to test the applications. I am using the VBA in
that tool.

Please suggest me.

Thanks,
Suraj

[Jim Thomlinson]

I have to admit that I am unfamilliar with "TestPartner", but what you are
trying to do is not going to work. What you need to do is to set "a" to the
current instance of Excel. That being said it is possible to have multiple
instances of Excel running at the same time, so you are going to have
difficulty attaching to the instace that you want. You can create a new
instance of Excel something like this

Dim xlApp As Object

Set xlApp = CreateObject("Excel.Application")

With xlApp
.Visible = True
.Workbooks.Add
.Workbooks.Open "C:\MyBook.xls"
End With
--
HTH...

Jim Thomlinson


"Suraj" wrote:

> Thanks Jim and Andrew.
> Jim,
> I tried in the way you suggested, but the statements inside the For
> Loop are not being executed. This time I didn't get any error. Pls let
> me know what to do. I am using a tool called "TestPartner" to test the
> applications. I am using the VBA in that tool.
>
> Andrew,
> I am using VBA but not within ExcelApplication. I am using a tool
> called "TestPartner" to test the applications. I am using the VBA in
> that tool.
>
> Please suggest me.
>
> Thanks,
> Suraj
>
>

[Suraj]

Thanks Jim. I got the logic. I have one more query.

Using the VBA I want to save an Excel file which is already opened. I
tried to do this by ActiveWorkBook.SaveAs(" ").
But not working.
Please let me know the correct way of doing that or correct way of
using the above command.

Thanks,
Suraj

[Andrew Taylor]

Using Jim's code, you would say
xlApp.ActiveWorkBook.SaveAs(" ")
or
.ActiveWorkBook.SaveAs(" ")
if it's inside the With xlApp block.

(I presume there's actually a file name in the " ")

You might also want to put
.Application.DisplayAlerts = False
and
.Application.DisplayAlerts = True
around the SaveAs line to prevent a prompt if the file already exists
(or you could handle this possibility another way, e.g. by checking
whether it exists before trying to save)

Andrew

Suraj wrote:
> Thanks Jim. I got the logic. I have one more query.
>
> Using the VBA I want to save an Excel file which is already opened. I
> tried to do this by ActiveWorkBook.SaveAs(" ").
> But not working.
> Please let me know the correct way of doing that or correct way of
> using the above command.
>
> Thanks,
> Suraj