Close workbook problem
 

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

Close workbook problem
 

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"
wrote in message

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

Close workbook problem
 

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"
wrote in message

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

Close workbook problem
 

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

Jim Cone


"Jim Cone" wrote in message ...
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"
wrote in message

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"
wrote in message

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