Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Close workbook
I want tp close tempdata if it is open
If ActiveWorkbook = "Tempdata.xls" Then ActiveWorkbook.Close End If This dosn't work |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Close workbook
Try:
If ActiveWorkbook.Name = "Tempdata" Then ActiveWorkbook.Close End If -- Gary''s Student - gsnu200779 "Oldjay" wrote: I want tp close tempdata if it is open If ActiveWorkbook = "Tempdata.xls" Then ActiveWorkbook.Close End If This dosn't work |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Close workbook
Thiat worked but I didn't tell you everything.
Sometimes Tempdata .xlx is open but not the active workbook "Gary''s Student" wrote: Try: If ActiveWorkbook.Name = "Tempdata" Then ActiveWorkbook.Close End If -- Gary''s Student - gsnu200779 "Oldjay" wrote: I want tp close tempdata if it is open If ActiveWorkbook = "Tempdata.xls" Then ActiveWorkbook.Close End If This dosn't work |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Close workbook
On Apr 14, 7:15*pm, Oldjay wrote:
Thiat worked but I didn't tell you everything. Sometimes Tempdata .xlx is open but not the active workbook "Gary''s Student" wrote: Try: *If ActiveWorkbook.Name = "Tempdata" Then * * * * ActiveWorkbook.Close *End If -- Gary''s Student - gsnu200779 "Oldjay" wrote: I want tp close tempdata if it is open *If ActiveWorkbook = "Tempdata.xls" Then * * * * ActiveWorkbook.Close *End If This dosn't work Oldjay, This should do the trick: Sub CloseTempData() Dim wkb As Workbook For Each wkb In Workbooks If wkb.Name = "Tempdata" Then wkb.Close End If Next wkb End Sub Alex |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Close workbook
That didn't work. It steped thru if but didn't see Tempdata. I even tried to
add .xls just in case that worked. Maybe there is a better way to do what I want. Why I am checking to see if Tempdata was left open is because I need to copy data to it then save it under a other name. Here is the first part of the sub If Tempdata is open then you can't save it because another workbook is all ready open and the sub fails Sub SaveNewQuot() ' Saves input data for new quote Application.ScreenUpdating = False 'Keeps screen from updating thereby speeding routine Application.DisplayAlerts = False 'Suppresses normal alerts Dim Quote1 As String Dim quotenumber1 As String Dim numbersave As String Dim QuoteRecords As String Dim username As String username = Application.username numbersave = Range("C4") 'Save Estimate # to the variable "numbersave" Range("I190:J191").Select ' Reset FOB and origin Selection.Copy Range("B190").Select Selection.PasteSpecial Paste:=xlPasteFormulas For Each wkb In Workbooks If wkb.Name = "Tempdata" Then wkb.Close End If Next wkb Workbooks.Add ActiveWorkbook.SaveAs Filename:="TempData.xls" Windows("Master5.xls").Activate 'Customer info & part description Range("C4:C34").Select Selection.Copy Windows("TempData.XLS").Activate Range("C4").Select Selection.PasteSpecial Paste:=xlPasteFormulas "Alex Simmons" wrote: On Apr 14, 7:15 pm, Oldjay wrote: Thiat worked but I didn't tell you everything. Sometimes Tempdata .xlx is open but not the active workbook "Gary''s Student" wrote: Try: If ActiveWorkbook.Name = "Tempdata" Then ActiveWorkbook.Close End If -- Gary''s Student - gsnu200779 "Oldjay" wrote: I want tp close tempdata if it is open If ActiveWorkbook = "Tempdata.xls" Then ActiveWorkbook.Close End If This dosn't work Oldjay, This should do the trick: Sub CloseTempData() Dim wkb As Workbook For Each wkb In Workbooks If wkb.Name = "Tempdata" Then wkb.Close End If Next wkb End Sub Alex |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Close workbook
On Apr 14, 10:28*pm, Oldjay wrote:
That didn't work. It steped thru if but didn't see Tempdata. I even tried to add .xls just in case that worked. Maybe there is a better way to do what I want. Why I am checking to see if Tempdata was left open is because I need to copy data to it then save it under a other name. Here is the first part of the sub If Tempdata is open then you can't save it because another workbook is all ready open and the sub fails Sub SaveNewQuot() ' Saves input data for new quote * * Application.ScreenUpdating = False 'Keeps screen from updating thereby speeding routine * * Application.DisplayAlerts = False *'Suppresses normal alerts * * Dim Quote1 As String * * Dim quotenumber1 As String * * Dim numbersave As String * * Dim QuoteRecords As String * * Dim username As String * * username = Application.username * * * * numbersave = Range("C4") 'Save Estimate # to the variable "numbersave" * * * * Range("I190:J191").Select ' Reset FOB and origin * * * * Selection.Copy * * * * Range("B190").Select * * * * Selection.PasteSpecial Paste:=xlPasteFormulas * * * * For Each wkb In Workbooks * * * * * *If wkb.Name = "Tempdata" Then * * * * * * * wkb.Close * * * * * *End If * * * * Next wkb * * * * Workbooks.Add * * * * ActiveWorkbook.SaveAs Filename:="TempData.xls" * * * * Windows("Master5.xls").Activate 'Customer info & part description * * * * Range("C4:C34").Select * * * * Selection.Copy * * * * Windows("TempData.XLS").Activate * * * * Range("C4").Select * * * * Selection.PasteSpecial Paste:=xlPasteFormulas "Alex Simmons" wrote: On Apr 14, 7:15 pm, Oldjay wrote: Thiat worked but I didn't tell you everything. Sometimes Tempdata .xlx is open but not the active workbook "Gary''s Student" wrote: Try: *If ActiveWorkbook.Name = "Tempdata" Then * * * * ActiveWorkbook.Close *End If -- Gary''s Student - gsnu200779 "Oldjay" wrote: I want tp close tempdata if it is open *If ActiveWorkbook = "Tempdata.xls" Then * * * * ActiveWorkbook.Close *End If This dosn't work Oldjay, This should do the trick: Sub CloseTempData() Dim wkb As Workbook For Each wkb In Workbooks * * If wkb.Name = "Tempdata" Then * * wkb.Close * * End If Next wkb End Sub Alex Sorry, yes my previous post should read If wkb.Name = "Tempdata.xls", which works when I tested it. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Close workbook
On Apr 15, 12:53*am, Alex Simmons wrote:
On Apr 14, 10:28*pm, Oldjay wrote: That didn't work. It steped thru if but didn't see Tempdata. I even tried to add .xls just in case that worked. Maybe there is a better way to do what I want. Why I am checking to see if Tempdata was left open is because I need to copy data to it then save it under a other name. Here is the first part of the sub If Tempdata is open then you can't save it because another workbook is all ready open and the sub fails Sub SaveNewQuot() ' Saves input data for new quote * * Application.ScreenUpdating = False 'Keeps screen from updating thereby speeding routine * * Application.DisplayAlerts = False *'Suppresses normal alerts * * Dim Quote1 As String * * Dim quotenumber1 As String * * Dim numbersave As String * * Dim QuoteRecords As String * * Dim username As String * * username = Application.username * * * * numbersave = Range("C4") 'Save Estimate # to the variable "numbersave" * * * * Range("I190:J191").Select ' Reset FOB and origin * * * * Selection.Copy * * * * Range("B190").Select * * * * Selection.PasteSpecial Paste:=xlPasteFormulas * * * * For Each wkb In Workbooks * * * * * *If wkb.Name = "Tempdata" Then * * * * * * * wkb.Close * * * * * *End If * * * * Next wkb * * * * Workbooks.Add * * * * ActiveWorkbook.SaveAs Filename:="TempData.xls" * * * * Windows("Master5.xls").Activate 'Customer info & part description * * * * Range("C4:C34").Select * * * * Selection.Copy * * * * Windows("TempData.XLS").Activate * * * * Range("C4").Select * * * * Selection.PasteSpecial Paste:=xlPasteFormulas "Alex Simmons" wrote: On Apr 14, 7:15 pm, Oldjay wrote: Thiat worked but I didn't tell you everything. Sometimes Tempdata .xlx is open but not the active workbook "Gary''s Student" wrote: Try: *If ActiveWorkbook.Name = "Tempdata" Then * * * * ActiveWorkbook.Close *End If -- Gary''s Student - gsnu200779 "Oldjay" wrote: I want tp close tempdata if it is open *If ActiveWorkbook = "Tempdata.xls" Then * * * * ActiveWorkbook.Close *End If This dosn't work Oldjay, This should do the trick: Sub CloseTempData() Dim wkb As Workbook For Each wkb In Workbooks * * If wkb.Name = "Tempdata" Then * * wkb.Close * * End If Next wkb End Sub Alex Sorry, yes my previous post should read If wkb.Name = "Tempdata.xls", which works when I tested it. And also, what are you trying to do with the numbersave variable? Your code is confusing here - perhaps it should be: Let numbersave = Range("C4").Value I'm also not sure exactly what you're trying to achieve - it's difficult to understand without your full code there. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Close workbook
numbersave = Range("C4") 'Save Estimate # to the variable "numbersave"
After copying and pasting about 50 variables the fileis saved quotenumber1 = InputBox("Please enter QUOTE file name to save to Server3", _ "The AuldCompany", "\\server3\jobs\estimate1\New_Quot1\" & numbersave) Quote1 = quotenumber1 & ".XLS" ActiveWorkbook.SaveAs Filename:=Quote1 ActiveWorkbook.Close I know this is not good code but I have use it for a long time and I knew that I couldn't have Tempdata.xls open to save a quote. I am now trying to teach some one else to use this workbook and I am trying to clean up this 3.5Meg 15 year old spreadsheet. "Alex Simmons" wrote: On Apr 15, 12:53 am, Alex Simmons wrote: On Apr 14, 10:28 pm, Oldjay wrote: That didn't work. It steped thru if but didn't see Tempdata. I even tried to add .xls just in case that worked. Maybe there is a better way to do what I want. Why I am checking to see if Tempdata was left open is because I need to copy data to it then save it under a other name. Here is the first part of the sub If Tempdata is open then you can't save it because another workbook is all ready open and the sub fails Sub SaveNewQuot() ' Saves input data for new quote Application.ScreenUpdating = False 'Keeps screen from updating thereby speeding routine Application.DisplayAlerts = False 'Suppresses normal alerts Dim Quote1 As String Dim quotenumber1 As String Dim numbersave As String Dim QuoteRecords As String Dim username As String username = Application.username numbersave = Range("C4") 'Save Estimate # to the variable "numbersave" Range("I190:J191").Select ' Reset FOB and origin Selection.Copy Range("B190").Select Selection.PasteSpecial Paste:=xlPasteFormulas For Each wkb In Workbooks If wkb.Name = "Tempdata" Then wkb.Close End If Next wkb Workbooks.Add ActiveWorkbook.SaveAs Filename:="TempData.xls" Windows("Master5.xls").Activate 'Customer info & part description Range("C4:C34").Select Selection.Copy Windows("TempData.XLS").Activate Range("C4").Select Selection.PasteSpecial Paste:=xlPasteFormulas "Alex Simmons" wrote: On Apr 14, 7:15 pm, Oldjay wrote: Thiat worked but I didn't tell you everything. Sometimes Tempdata .xlx is open but not the active workbook "Gary''s Student" wrote: Try: If ActiveWorkbook.Name = "Tempdata" Then ActiveWorkbook.Close End If -- Gary''s Student - gsnu200779 "Oldjay" wrote: I want tp close tempdata if it is open If ActiveWorkbook = "Tempdata.xls" Then ActiveWorkbook.Close End If This dosn't work Oldjay, This should do the trick: Sub CloseTempData() Dim wkb As Workbook For Each wkb In Workbooks If wkb.Name = "Tempdata" Then wkb.Close End If Next wkb End Sub Alex Sorry, yes my previous post should read If wkb.Name = "Tempdata.xls", which works when I tested it. And also, what are you trying to do with the numbersave variable? Your code is confusing here - perhaps it should be: Let numbersave = Range("C4").Value I'm also not sure exactly what you're trying to achieve - it's difficult to understand without your full code there. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide all Sheets in Workbook then Close Workbook | Excel Programming | |||
Open New Workbook / Save and Close Current Workbook | Excel Programming | |||
Closing a workbook from a macro doesn't close the workbook | Excel Programming | |||
Help on Workbook close and workbook save events | Excel Programming | |||
Close a the current workbook and load another specified workbook | Excel Programming |