Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving input figures.
I have built this program. Is there a way u I can save the imputed
information and not the entire templete used. Also when i want to click on this saved info will it load it into the templete? THANKS GLENN |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving input figures.
I think what you may be looking for Glenn is this -- it is just a model of
what you can do in the BeforeSave event in the ThisWorkbook code Module: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As _ Boolean, Cancel As Boolean) Dim i As Long Dim MyRange As Range Dim MyNameIs As String 'Procedure here to harvest the data into a range 'For example, suppose there are 'Data in Rows 1 through 10 On Error GoTo err_Handler For i = 1 To 10 If MyRange Is Nothing Then Set MyRange = Cells(i, 1).EntireRow Else Set MyRange = Union(MyRange, Cells(i, 1).EntireRow) End If Next i 'Create the workbook to actually save With Workbooks.Add MyRange.Copy Destination:=Range("A1") MyNameIs = InputBox("What shall we name this workbook " & _ "it cannot be: " & vbCr & _ ThisWorkbook.Path & "\" & _ ThisWorkbook.Name, _ "Saving to a new File", ThisWorkbook.Path & "\NewName.Xls") .SaveAs (MyNameIs) End With Exit_Me: Exit Sub err_Handler: MsgBox Err.Description Resume Exit_Me End Sub "glenn" wrote in message ... I have built this program. Is there a way u I can save the imputed information and not the entire templete used. Also when i want to click on this saved info will it load it into the templete? THANKS GLENN |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving input figures.
I forgot the most important part, it is too late at night, sorry!
Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As _ Boolean, Cancel As Boolean) Dim i As Long Dim MyRange As Range Dim MyNameIs As String 'Procedure here to harvest the data into a range 'For example, suppose there are 'Data in Rows 1 through 10 On Error GoTo err_Handler For i = 1 To 10 If MyRange Is Nothing Then Set MyRange = Cells(i, 1).EntireRow Else Set MyRange = Union(MyRange, Cells(i, 1).EntireRow) End If Next i 'Create the workbook to actually save With Workbooks.Add MyRange.Copy Destination:=Range("A1") MyNameIs = InputBox("What shall we name this workbook " & _ "it cannot be: " & vbCr & _ ThisWorkbook.Path & "\" & _ ThisWorkbook.Name, _ "Saving to a new File", ThisWorkbook.Path & "\NewName.Xls") .SaveAs (MyNameIs) End With Exit_Me: Cancel = True '''''' very important!!! Exit Sub err_Handler: MsgBox Err.Description Resume Exit_Me End Sub "William Benson" wrote in message ... I think what you may be looking for Glenn is this -- it is just a model of what you can do in the BeforeSave event in the ThisWorkbook code Module: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As _ Boolean, Cancel As Boolean) Dim i As Long Dim MyRange As Range Dim MyNameIs As String 'Procedure here to harvest the data into a range 'For example, suppose there are 'Data in Rows 1 through 10 On Error GoTo err_Handler For i = 1 To 10 If MyRange Is Nothing Then Set MyRange = Cells(i, 1).EntireRow Else Set MyRange = Union(MyRange, Cells(i, 1).EntireRow) End If Next i 'Create the workbook to actually save With Workbooks.Add MyRange.Copy Destination:=Range("A1") MyNameIs = InputBox("What shall we name this workbook " & _ "it cannot be: " & vbCr & _ ThisWorkbook.Path & "\" & _ ThisWorkbook.Name, _ "Saving to a new File", ThisWorkbook.Path & "\NewName.Xls") .SaveAs (MyNameIs) End With Exit_Me: Exit Sub err_Handler: MsgBox Err.Description Resume Exit_Me End Sub "glenn" wrote in message ... I have built this program. Is there a way u I can save the imputed information and not the entire templete used. Also when i want to click on this saved info will it load it into the templete? THANKS GLENN |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving input figures.
I am a novice at this and I copied what you wrote and pasted it in a moduel
in the workbook. Did not work. The fields I need to store a F4:H32, J24:J26, N28, L27, C29. You guys have helped me greatly over the months building this program. Sorry for being so dumb to your answers. I really do that you for you time and help. Glenn "glenn" wrote: I have built this program. Is there a way u I can save the imputed information and not the entire templete used. Also when i want to click on this saved info will it load it into the templete? THANKS GLENN |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving input figures.
Glenn, the code I provided only did half what I assumed you were trying to
do - help you get data out of the main workbook without letting the user save it there, and into a new workbook. And, since your ranges are so disparate, I have had to just assume you want the data in the same cells in the new workbook. Whatever failings my assumptions have, consider giving more detailed explanations of what you are trying to do in the future please. If you insert the below code in the default module "ThisWorkbook", it should work to intercept the Save function in Excel for the current workbook, save the data in the ranges you described in a new workbook. It will cancel the save function so that the first workbook does not save. That's all it does. Note: It is touchy to work with code which interrupts the Save event because it makes it tough to save your file in the workbook in the first place. I suggest you make sure you open the workbook without macros enabled, paste the code in, and save the workbook. Then close and re-open normally. If the below doesn't do what you really are trying to do, I was just giving you a few ideas, maybe someone else in the forum could help you. Bill Private Sub Workbook_BeforeSave(ByVal SaveAsUI As _ Boolean, Cancel As Boolean) Dim i As Long Dim MyRange As Range Dim MyNameIs As String Dim wbkFrom As Workbook Dim wbkTo As Workbook Dim ShFrom As Worksheet Dim ShTo As Worksheet 'Procedure here to harvest the data into a range 'For example, suppose there are 'Data in Rows 1 through 10 On Error GoTo err_Handler 'Create the workbook to actually save Set wbkFrom = ThisWorkbook Set ShFrom = ActiveSheet With Workbooks.Add Set wbkTo = ActiveWorkbook Set ShTo = ActiveSheet 'F4:H32 Set MyRange = Range("'[" & _ wbkFrom.Name & "]" & ShFrom.Name & "'!F4:H32") MyRange.Copy Destination:=Range("'[" & _ wbkTo.Name & "]" & ShTo.Name & "'!F4:H32") 'J24:J26 Set MyRange = Range("'[" & _ wbkFrom.Name & "]" & ShFrom.Name & "'!J24:J26") MyRange.Copy Destination:=Range("'[" & _ wbkTo.Name & "]" & ShTo.Name & "'!J24:J26") 'N28 Set MyRange = Range("'[" & _ wbkFrom.Name & "]" & ShFrom.Name & "'!N28") MyRange.Copy Destination:=Range("'[" & _ wbkTo.Name & "]" & ShTo.Name & "'!N28") 'L27 Set MyRange = Range("'[" & _ wbkFrom.Name & "]" & ShFrom.Name & "'!L27") MyRange.Copy Destination:=Range("'[" & _ wbkTo.Name & "]" & ShTo.Name & "'!L27") 'C29 Set MyRange = Range("'[" & _ wbkFrom.Name & "]" & ShFrom.Name & "'!C29") MyRange.Copy Destination:=Range("'[" & _ wbkTo.Name & "]" & ShTo.Name & "'!C29") MyNameIs = InputBox("Where should data be stored? " & _ "it cannot be: " & vbCr & _ ThisWorkbook.Path & "\" & _ ThisWorkbook.Name, _ "Saving to a new File", ThisWorkbook.Path & "\NewName.Xls") .SaveAs (MyNameIs) End With Exit_Me: Cancel = True '''''' very important!!! Exit Sub err_Handler: MsgBox Err.Description Resume Exit_Me End Sub As for the second part, getting data from this file back to the template "glenn" wrote in message ... I am a novice at this and I copied what you wrote and pasted it in a moduel in the workbook. Did not work. The fields I need to store a F4:H32, J24:J26, N28, L27, C29. You guys have helped me greatly over the months building this program. Sorry for being so dumb to your answers. I really do that you for you time and help. Glenn "glenn" wrote: I have built this program. Is there a way u I can save the imputed information and not the entire templete used. Also when i want to click on this saved info will it load it into the templete? THANKS GLENN |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to input password when saving doc in excel | Excel Discussion (Misc queries) | |||
Require input in a cell before saving file | Excel Discussion (Misc queries) | |||
Saving without deleting previous input | Excel Worksheet Functions | |||
Have cell require input before saving. | Excel Discussion (Misc queries) | |||
Saving a file from a command button with user input | Excel Programming |