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 procedu

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.

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 procedu

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.

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 wrote:

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 procedu

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

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