Runtime Error '7': Out of Memory

[Ajain86]

I am trying to use vba in excel to open several excel files and format them
to what I need. When I run the procedure, I keep getting Runtime Error '7':
Out of Memory. I have tried running the procedure with nothing else open and
I do not understand how I could be out of memory. I am using Excel 2002. I
created a smaller procedure in a new excel file to test and still got the
error. Here is the code from my test procedure:

Public Sub test()

Dim xlApp As Excel.Application
Dim wb As Excel.Workbook
Dim str As String

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

str = "C:\temp\test.xls"
Set wb = xlApp.Workbooks.Open(str, False, True, , , , , , , False)

xlApp.Quit
Set xlApp = Nothing

End Sub

The error occurs on the line "Set xlApp = CreateObject("Excel.Application")".
I have tried using the line "Set xlApp = New Excel.Application" and the
error still occurs.

Also, when I close out of excel and try to open a new excel file, I get a
dialog box telling me that excel did not open properly last time and suggests
that I open excel in safe mode.

Can anyone please help me? Thank you.

 

[Moe Green]

Check Windows Task Manager and see if you have more than one instance of Excel open.


"Ajain86"
wrote in message
I am trying to use vba in excel to open several excel files and format them
to what I need. When I run the procedure, I keep getting Runtime Error '7':
Out of Memory. I have tried running the procedure with nothing else open and
I do not understand how I could be out of memory. I am using Excel 2002. I
created a smaller procedure in a new excel file to test and still got the
error. Here is the code from my test procedure:

Public Sub test()

Dim xlApp As Excel.Application
Dim wb As Excel.Workbook
Dim str As String

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

str = "C:\temp\test.xls"
Set wb = xlApp.Workbooks.Open(str, False, True, , , , , , , False)

xlApp.Quit
Set xlApp = Nothing

End Sub

The error occurs on the line "Set xlApp = CreateObject("Excel.Application")".
I have tried using the line "Set xlApp = New Excel.Application" and the
error still occurs.

Also, when I close out of excel and try to open a new excel file, I get a
dialog box telling me that excel did not open properly last time and suggests
that I open excel in safe mode.

Can anyone please help me? Thank you.

 

[Dave Peterson]

And if your code is in an excel workbook, why start a new instance of excel?

Public Sub test()

Dim wb As Workbook
Dim str As String

str = "C:\temp\test.xls"
Set wb = xlApp.Workbooks.Open(str, False, True, , , , , , , False)

End Sub

 

[Ajain86]

Thanks for the reply. I took over this process from someone else. I was able
to solve the problem. I do not know why it originally had opening a new
instance of excel. I changed my code to this and it works fine:

Public Sub test()

Dim wb As Excel.Workbook
Dim str As String

str = "C:\temp\test.xls"
Set wb = Workbooks.Open(str, False, True, , , , , , , False)

wb.Close
Set wb = Nothing

End Sub