Solved: Close workbook based upon cell value, but don't save workbook

[khalid79m]

Hi I use this code to open a workbook dependant on the value held in my range



Dim Rng As Range
Dim wsD As Worksheet

Set wsD = Workbooks("Returns.xls").Sheets("Control")

Set Rng = Range("C9")

If Not Rng.Value = vbNullString Then

Workbooks.Open Filename:= _
"P:\CET - Employee Engagement\Sourcing & Monitoring\" & Rng.Value

'my code goes here
'then i need to close the workbook based on the cell value Workbooks("Returns.xls").Sheets("Control").Range("C9") without saving it or showing any message boxes?


End If

How can I then close the workbook based on the cell value and not save it

[MikeO]

Application.DisplayAlerts = False
Workbooks(rng.Value).Close False
Application.DisplayAlerts = True

[khalid79m]

code help


The returns file is like my control panel.
I insert a file name in on sheet "control" in cell c9 and get the macro to run

The macro opens the file specified in c9 and copys sheet "calldata from range a2 to iv and lastrow and then pastes it back into the control panel on sheet "SITE" at the next available cell in row A.
Then it goes back to the open file and close it without save and without displaying the alerts.

can someone write this code for me ? as mine doesnt work and im now 20.44 late friday evening and i really want to go home

[mdmackillop]

Option Explicit
Sub Test()
Dim Rng As Range
Dim wsD As Worksheet
Dim WB As Workbook
Set wsD = Workbooks("Returns.xls").Sheets("Control")
Set Rng = Range("C9")
If Not Rng.Value = vbNullString Then
Set WB = Workbooks.Open("P:\CET - Employee Engagement\Sourcing & Monitoring\" & Rng.Value)
'my code goes here
'then i need to close the workbook based on the cell
'value Workbooks("Returns.xls").Sheets("Control").Range("C9")
'without saving it or showing any message boxes?
WB.Close False
End If
End Sub

[khalid79m]

Your the best man...

how can i get it to look at sheet "calldata" on the newly opened workbook (rng c9) and copy from a2 to iv and lastrow to the returns.xls sheet "SITE" in the next available cell in row a ?

the source is on sheet "CallData" how can i specify that ?
the target is a different sheet in returns.xls it is sheet "site" rather thar "control"?

You help is greatly appreciated

[mdmackillop]

Try

Sub Test2()
Dim Rng As Range, Source As Range, Tgt As Range
Dim wsD As Worksheet
Dim WB As Workbook
Set wsD = Workbooks("Returns.xls").Sheets("Control")
Set Rng = Range("C9")
If Not Rng.Value = vbNullString Then
Set WB = Workbooks.Open("P:\CET - Employee Engagement\Sourcing & Monitoring\" & Rng.Value)
With WB
Set Source = Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).Resize(, 256)
end with
Set Tgt = Cells(Rows.Count, 1).End(xlUp).Offset(1)
Source.Copy Tgt
'my code goes here
'then i need to close the workbook based on the cell
'value Workbooks("Returns.xls").Sheets("Control").Range("C9")
'without saving it or showing any message boxes?
WB.Close False
End If
End Sub

[khalid79m]

im trying t o follow your code what does the resize(, 256) do?

also how can the paste this data into the the returns.xls on sheet "site" in the next available cell in row a.?

[mdmackillop]

Sorry, not concentrating. Wathing the cycling on TV

Resize expands a range by stated numbers of rows and columns
Try

Range("C3").resize(5,10).select



Sub Test2()
Dim Rng As Range, Source As Range, Tgt As Range
Dim wsD As Worksheet
Dim WB As Workbook
Set wsD = Workbooks("Returns.xls").Sheets("Control")
Set Rng = Range("C9")
If Not Rng.Value = vbNullString Then
Set WB = Workbooks.Open("P:\CET - Employee Engagement\Sourcing & Monitoring\" & Rng.Value)
With WB
Set Source = Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).Resize(, 256)
End With
Set Tgt = Sheets("Site").Cells(Rows.Count, 1).End(xlUp).Offset(1)
Source.Copy Tgt
WB.Close False
End If
End Sub

[khalid79m]

its okay, im no expert on vba but been using it for the last year and am learning alot and the code you have written is excellent I hope one day ill be able to do the same.

One thing source is sheet "CallData"
so should that be :


With WB
Set Source = sheets("CallData").Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).Resize(, 256)
End With


also
WB.Close False does this close the workbook without save ? and stops any message boxes?:rotlaugh:

[mdmackillop]

With WB.Sheets("CallData")
Set Source = Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).Resize(, 256)
End With

[khalid79m]

WB.Close False

does this close the workbook without save ? and stops any message boxes?

[mdmackillop]

False means Do Not Save. There should be no message boxes, but if you want, add the DisplayAlerts code as shown in post 2

[khalid79m]

Private Sub SiteExtract()
'Written by Muneer Khalid
'30/03/2008
'This code will open a file based on the Cell Value "C9" on Workbook "Returns.xls" on sheets "Control"
'Once the workbook is open it will set the source as Sheet "CallData!Range("A2:IV"& LASTROW) On that opened workbook.
'It will set the target for the source at the next avaliable row in column A on workbook "Returns.xls" on sheets '"Site"
'Overall this code will allow you to open a workbook based on a cell value and copy data from it do another workbook (ie the 'workbook where the macro is being run from) and then close the workbook that was opened by the cell value

Dim Rng As Range, Source As Range, Tgt As Range
Dim wsD As Worksheet
Dim WB As Workbook

Set wsD = Workbooks("Returns.xls").Sheets("Control")
Set Rng = Range("C9")

If Not Rng.Value = vbNullString Then

Set WB = Workbooks.Open("P:\CET - Employee Engagement\Sourcing & Monitoring\" & Rng.Value)
With WB.Sheets("CallData")
Set Source = Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).Resize(, 256)
End With
Set Tgt = Sheets("Site").Cells(Rows.Count, 1).End(xlUp).Offset(1)
Source.Copy Tgt
Application.DisplayAlerts = False
WB.Close False
Application.DisplayAlerts = True
End If
End Sub


this code fails on
Set Tgt = Sheets("Site").Cells(Rows.Count, 1).End(xlUp).Offset(1)

the set tgt should be workbook "Returns.xls" and sheet "site" how can i chnage this ????

[Bob Phillips]

Do you mean



Set Tgt = Workbooks("Return.xls").Sheets("Site").Cells(Rows.Count, 1).End(xlUp).Offset(1)

[khalid79m]

ONE major problem, when the workbook is opened up it has some close workbook macros built in how do i stop it from running these ?

[khalid79m]

WB.Close False


When the script gets to this bit it comes up with subscirpt out of range ?


can anyone help

[khalid79m]

how can I add in enable events = false?

[Bob Phillips]

That suggests that WB is not defined.