Open and Close another Workbook from VBA
 

Greetings.

I wrote the following subroutine to open, do some formatting,
and then close a workbook prior to importing its data into my
current workbook:

Sub CleanSrc(src As String)
Set srcApp = New Excel.Application
srcApp.Visible = False
Set srcWkBk = srcApp.Workbooks.Open(myFile)
Set srcSht = srcWkBk.Sheets(1)
srcLast = srcSht.Range("A1").End(xlDown).Row
srcSht.Rows((srcLast + 1) & ":" & (srcLast + 5)).Delete
srcWkBk.Close (True)
Set srcWkBk = Nothing
Set srcApp = Nothing
End Sub

My problem is that later when I attempt to import data from
this workbook, I get complaints it being locked (by myself!)
and read only.

It appears that somehow this workbook is never really closed
and/or the application I spun up wasn't exited properly.

Can somebody please tell me what I can do to fix this problem?

Regards,

Charles

Open and Close another Workbook from VBA
 

- Do you need another instance of Excel ?
Set srcApp = New Excel.Application
Just open all in your current instance.

- Also, it would us (and you) if you declared your variables
Dim srcWkBk as workbook
Check out "Option Explicit" in the VBA help.

- Also, I doubt you are opening the file you think. You pass "src" as an
argument, but open "myFile", whatever that is.
Sub CleanSrc(src As String)
.......
Set srcWkBk = srcApp.Workbooks.Open(myFile)

- Remove the ( ) from
srcWkBk.Close (True)

NickHK

"Charles in Iraq" wrote in message
...
Greetings.

I wrote the following subroutine to open, do some formatting,
and then close a workbook prior to importing its data into my
current workbook:

Sub CleanSrc(src As String)
Set srcApp = New Excel.Application
srcApp.Visible = False
Set srcWkBk = srcApp.Workbooks.Open(myFile)
Set srcSht = srcWkBk.Sheets(1)
srcLast = srcSht.Range("A1").End(xlDown).Row
srcSht.Rows((srcLast + 1) & ":" & (srcLast + 5)).Delete
srcWkBk.Close (True)
Set srcWkBk = Nothing
Set srcApp = Nothing
End Sub

My problem is that later when I attempt to import data from
this workbook, I get complaints it being locked (by myself!)
and read only.

It appears that somehow this workbook is never really closed
and/or the application I spun up wasn't exited properly.

Can somebody please tell me what I can do to fix this problem?

Regards,

Charles

Open and Close another Workbook from VBA
 

Thanks Nick. This advice helped a lot.

I rewrote my subroutine as follows:

Sub CleanSrc(src As String)
Dim srcWkBk As Excel.Workbook
Dim srcSht As Excel.Worksheet
Dim srcLast As Integer

Set srcWkBk = Application.Workbooks.Open(src)
Set srcSht = srcWkBk.Sheets(1)
srcLast = srcSht.Range("A1").End(xlDown).Row
srcSht.Rows((srcLast + 1) & ":" & (srcLast + 5)).Delete
srcWkBk.Close True
End Sub

However, now I notice another strange error.

If I double click on the source workbook while still inside my
current workbook, a dialog window pops up with the following
error message:

Cannot access myFile.xls

Do you know how I can fix this annoyting error?

Regards,

Charles
"NickHK" wrote:

- Do you need another instance of Excel ?
Set srcApp = New Excel.Application
Just open all in your current instance.

- Also, it would us (and you) if you declared your variables
Dim srcWkBk as workbook
Check out "Option Explicit" in the VBA help.

- Also, I doubt you are opening the file you think. You pass "src" as an
argument, but open "myFile", whatever that is.
Sub CleanSrc(src As String)
.......
Set srcWkBk = srcApp.Workbooks.Open(myFile)

- Remove the ( ) from
srcWkBk.Close (True)

NickHK

"Charles in Iraq" wrote in message
...
Greetings.

I wrote the following subroutine to open, do some formatting,
and then close a workbook prior to importing its data into my
current workbook:

Sub CleanSrc(src As String)
Set srcApp = New Excel.Application
srcApp.Visible = False
Set srcWkBk = srcApp.Workbooks.Open(myFile)
Set srcSht = srcWkBk.Sheets(1)
srcLast = srcSht.Range("A1").End(xlDown).Row
srcSht.Rows((srcLast + 1) & ":" & (srcLast + 5)).Delete
srcWkBk.Close (True)
Set srcWkBk = Nothing
Set srcApp = Nothing
End Sub

My problem is that later when I attempt to import data from
this workbook, I get complaints it being locked (by myself!)
and read only.

It appears that somehow this workbook is never really closed
and/or the application I spun up wasn't exited properly.

Can somebody please tell me what I can do to fix this problem?

Regards,

Charles

Open and Close another Workbook from VBA
 

I assume you mean that sourceWB is not open, but the code has run
successfully before on that WB. If so, not sure if this applicable...

I have just upgraded to XL2002 and noticed that I receive that error
occasionally on files that I have not opened recently, certainly not
involved in any coding.
Click OK and open again results in success.
Not sure what the cause is but seems to happen most often on XL files with
Chinese characters in the name and/or when I am working fast between
Explorer and Excel.

NickHK

"Charles in Iraq" wrote in message
...
Thanks Nick. This advice helped a lot.

I rewrote my subroutine as follows:

Sub CleanSrc(src As String)
Dim srcWkBk As Excel.Workbook
Dim srcSht As Excel.Worksheet
Dim srcLast As Integer

Set srcWkBk = Application.Workbooks.Open(src)
Set srcSht = srcWkBk.Sheets(1)
srcLast = srcSht.Range("A1").End(xlDown).Row
srcSht.Rows((srcLast + 1) & ":" & (srcLast + 5)).Delete
srcWkBk.Close True
End Sub

However, now I notice another strange error.

If I double click on the source workbook while still inside my
current workbook, a dialog window pops up with the following
error message:

Cannot access myFile.xls

Do you know how I can fix this annoyting error?

Regards,

Charles
"NickHK" wrote:

- Do you need another instance of Excel ?
Set srcApp = New Excel.Application
Just open all in your current instance.

- Also, it would us (and you) if you declared your variables
Dim srcWkBk as workbook
Check out "Option Explicit" in the VBA help.

- Also, I doubt you are opening the file you think. You pass "src" as an
argument, but open "myFile", whatever that is.
Sub CleanSrc(src As String)
.......
Set srcWkBk = srcApp.Workbooks.Open(myFile)

- Remove the ( ) from
srcWkBk.Close (True)

NickHK

"Charles in Iraq" wrote in
message
...
Greetings.

I wrote the following subroutine to open, do some formatting,
and then close a workbook prior to importing its data into my
current workbook:

Sub CleanSrc(src As String)
Set srcApp = New Excel.Application
srcApp.Visible = False
Set srcWkBk = srcApp.Workbooks.Open(myFile)
Set srcSht = srcWkBk.Sheets(1)
srcLast = srcSht.Range("A1").End(xlDown).Row
srcSht.Rows((srcLast + 1) & ":" & (srcLast + 5)).Delete
srcWkBk.Close (True)
Set srcWkBk = Nothing
Set srcApp = Nothing
End Sub

My problem is that later when I attempt to import data from
this workbook, I get complaints it being locked (by myself!)
and read only.

It appears that somehow this workbook is never really closed
and/or the application I spun up wasn't exited properly.

Can somebody please tell me what I can do to fix this problem?

Regards,

Charles