Close workbook problem

Don S · 01-16-2006, 11:20 AM

I have the following code where I open a workbook, copy page and close
workbook. Excel is "locking up" (hourglass) at close workbook line. I have
used this same code in other applications with no problem. Any ideas?

Sheets("Directory").Select
Yr = Cells(2, 2)
Mth = Cells(4, 2)
Location = Cells(7, 2)
CAFile = Cells(8, 2)
ctpage = Cells(9, 2)
CTup = Cells(14, 2)

Dim bk As Workbook
Set bk = Workbooks.Open(Location & Yr & "\" & Mth & "\" & CAFile,
UpdateLinks:=False)

Dim wbkSource As Workbook
Dim wbkDestination As Workbook
Dim wksSource As Worksheet
Dim wksDestination As Worksheet
Dim rngSource As Range
Dim rngDestination As Range

Set wbkSource = Workbooks(CAFile)
Set wksSource = wbkSource.Sheets(ctpage)
Set rngSource = wksSource.Cells

Set wbkDestination = ThisWorkbook
Set wksDestination = wbkDestination.Sheets(CTup)
Set rngDestination = wksDestination.Range("A1")

rngSource.Copy
rngDestination.PasteSpecial (xlPasteValues)

Windows(CAFile).Activate
Application.DisplayAlerts = False
ActiveWorkbook.Close SaveChanges:=False
Application.DisplayAlerts = True

Jim Cone · 01-16-2006, 01:00 PM

Don,

Does Cells(8, 2) include the file extension?

You have the bk variable set as the opened workbook.
Why use another variable (wbkSource) for the same thing?

Do you really need to copy all 17 million cells?
(Sheet.Cells means all of them)

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

"Don S" <[email protected]>
wrote in message
news:[email protected]
I have the following code where I open a workbook, copy page and close
workbook. Excel is "locking up" (hourglass) at close workbook line. I have
used this same code in other applications with no problem. Any ideas?

Sheets("Directory").Select
Yr = Cells(2, 2)
Mth = Cells(4, 2)
Location = Cells(7, 2)
CAFile = Cells(8, 2)
ctpage = Cells(9, 2)
CTup = Cells(14, 2)

Dim bk As Workbook
Set bk = Workbooks.Open(Location & Yr & "\" & Mth & "\" & CAFile,
UpdateLinks:=False)

Dim wbkSource As Workbook
Dim wbkDestination As Workbook
Dim wksSource As Worksheet
Dim wksDestination As Worksheet
Dim rngSource As Range
Dim rngDestination As Range

Set wbkSource = Workbooks(CAFile)
Set wksSource = wbkSource.Sheets(ctpage)
Set rngSource = wksSource.Cells

Set wbkDestination = ThisWorkbook
Set wksDestination = wbkDestination.Sheets(CTup)
Set rngDestination = wksDestination.Range("A1")

rngSource.Copy
rngDestination.PasteSpecial (xlPasteValues)

Windows(CAFile).Activate
Application.DisplayAlerts = False
ActiveWorkbook.Close SaveChanges:=False
Application.DisplayAlerts = True

Don S · 01-16-2006, 01:35 PM

Yes

redundant, but still works

no, don't need all cells,but just as easy to copy entire page.

everything works fine with the copy until I try to close the workbook

"Jim Cone" wrote:

> Don,
>
> Does Cells(8, 2) include the file extension?
>
> You have the bk variable set as the opened workbook.
> Why use another variable (wbkSource) for the same thing?
>
> Do you really need to copy all 17 million cells?
> (Sheet.Cells means all of them)
>
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
>
>
>
>
> "Don S" <[email protected]>
> wrote in message
> news:[email protected]
> I have the following code where I open a workbook, copy page and close
> workbook. Excel is "locking up" (hourglass) at close workbook line. I have
> used this same code in other applications with no problem. Any ideas?
>
> Sheets("Directory").Select
> Yr = Cells(2, 2)
> Mth = Cells(4, 2)
> Location = Cells(7, 2)
> CAFile = Cells(8, 2)
> ctpage = Cells(9, 2)
> CTup = Cells(14, 2)
>
> Dim bk As Workbook
> Set bk = Workbooks.Open(Location & Yr & "\" & Mth & "\" & CAFile,
> UpdateLinks:=False)
>
>
> Dim wbkSource As Workbook
> Dim wbkDestination As Workbook
> Dim wksSource As Worksheet
> Dim wksDestination As Worksheet
> Dim rngSource As Range
> Dim rngDestination As Range
>
> Set wbkSource = Workbooks(CAFile)
> Set wksSource = wbkSource.Sheets(ctpage)
> Set rngSource = wksSource.Cells
>
> Set wbkDestination = ThisWorkbook
> Set wksDestination = wbkDestination.Sheets(CTup)
> Set rngDestination = wksDestination.Range("A1")
>
> rngSource.Copy
> rngDestination.PasteSpecial (xlPasteValues)
>
>
> Windows(CAFile).Activate
> Application.DisplayAlerts = False
> ActiveWorkbook.Close SaveChanges:=False
> Application.DisplayAlerts = True
>
>

Jim Cone · 01-16-2006, 01:50 PM

Don,

I assume you are not using automation.
If it doesn't work try something different...

Set wbkSource = Nothing
bk.Close false

Jim Cone
San Francisco, USA

"Don S" <[email protected]>
wrote in message
news:[email protected]
Yes

redundant, but still works
no, don't need all cells,but just as easy to copy entire page.
everything works fine with the copy until I try to close the workbook

"Jim Cone" wrote:
> Don,
> Does Cells(8, 2) include the file extension?
> You have the bk variable set as the opened workbook.
> Why use another variable (wbkSource) for the same thing?
> Do you really need to copy all 17 million cells?
> (Sheet.Cells means all of them)
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
>
>
>
>
> "Don S" <[email protected]>
> wrote in message
> news:[email protected]
> I have the following code where I open a workbook, copy page and close
> workbook. Excel is "locking up" (hourglass) at close workbook line. I have
> used this same code in other applications with no problem. Any ideas?
>
> Sheets("Directory").Select
> Yr = Cells(2, 2)
> Mth = Cells(4, 2)
> Location = Cells(7, 2)
> CAFile = Cells(8, 2)
> ctpage = Cells(9, 2)
> CTup = Cells(14, 2)
>
> Dim bk As Workbook
> Set bk = Workbooks.Open(Location & Yr & "\" & Mth & "\" & CAFile,
> UpdateLinks:=False)
>
>
> Dim wbkSource As Workbook
> Dim wbkDestination As Workbook
> Dim wksSource As Worksheet
> Dim wksDestination As Worksheet
> Dim rngSource As Range
> Dim rngDestination As Range
>
> Set wbkSource = Workbooks(CAFile)
> Set wksSource = wbkSource.Sheets(ctpage)
> Set rngSource = wksSource.Cells
>
> Set wbkDestination = ThisWorkbook
> Set wksDestination = wbkDestination.Sheets(CTup)
> Set rngDestination = wksDestination.Range("A1")
>
> rngSource.Copy
> rngDestination.PasteSpecial (xlPasteValues)
>
>
> Windows(CAFile).Activate
> Application.DisplayAlerts = False
> ActiveWorkbook.Close SaveChanges:=False
> Application.DisplayAlerts = True
>
>

Jim Cone · 01-16-2006, 02:20 PM

Furthermore, you could try removing the
displayalerts = false line and see if Excel
is trying to tell you something.

Jim Cone

"Jim Cone" <[email protected]> wrote in message news:[email protected]...
Don,

I assume you are not using automation.
If it doesn't work try something different...

Set wbkSource = Nothing
bk.Close false

Jim Cone
San Francisco, USA

"Don S" <[email protected]>
wrote in message
news:[email protected]
Yes

redundant, but still works
no, don't need all cells,but just as easy to copy entire page.
everything works fine with the copy until I try to close the workbook

"Jim Cone" wrote:
> Don,
> Does Cells(8, 2) include the file extension?
> You have the bk variable set as the opened workbook.
> Why use another variable (wbkSource) for the same thing?
> Do you really need to copy all 17 million cells?
> (Sheet.Cells means all of them)
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
>
>
>
>
> "Don S" <[email protected]>
> wrote in message
> news:[email protected]
> I have the following code where I open a workbook, copy page and close
> workbook. Excel is "locking up" (hourglass) at close workbook line. I have
> used this same code in other applications with no problem. Any ideas?
>
> Sheets("Directory").Select
> Yr = Cells(2, 2)
> Mth = Cells(4, 2)
> Location = Cells(7, 2)
> CAFile = Cells(8, 2)
> ctpage = Cells(9, 2)
> CTup = Cells(14, 2)
>
> Dim bk As Workbook
> Set bk = Workbooks.Open(Location & Yr & "\" & Mth & "\" & CAFile,
> UpdateLinks:=False)
>
>
> Dim wbkSource As Workbook
> Dim wbkDestination As Workbook
> Dim wksSource As Worksheet
> Dim wksDestination As Worksheet
> Dim rngSource As Range
> Dim rngDestination As Range
>
> Set wbkSource = Workbooks(CAFile)
> Set wksSource = wbkSource.Sheets(ctpage)
> Set rngSource = wksSource.Cells
>
> Set wbkDestination = ThisWorkbook
> Set wksDestination = wbkDestination.Sheets(CTup)
> Set rngDestination = wksDestination.Range("A1")
>
> rngSource.Copy
> rngDestination.PasteSpecial (xlPasteValues)
>
>
> Windows(CAFile).Activate
> Application.DisplayAlerts = False
> ActiveWorkbook.Close SaveChanges:=False
> Application.DisplayAlerts = True
>
>