Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto save when form is closed
I have a userform that pops up when a file is open to make sure all of the
data goes into the correct place. I would like to add 1 more textbox that would give the used the ability to type in the file name, an example would be 4-4-08 Run 1. When the form is closed I would like to have the file automatically saved to a certian location with the file name added. An example would be I/work/engineeringdata/productdata/meters/turbine/(file name entered by user).xls. I could make a macro to run on the forms close to save the file name but am unsure how to attache the userinfo to the path and name. Thank you for your help. Tim Peter |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto save when form is closed
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
ThisWorkbook.Save End Sub "tpeter" wrote: I have a userform that pops up when a file is open to make sure all of the data goes into the correct place. I would like to add 1 more textbox that would give the used the ability to type in the file name, an example would be 4-4-08 Run 1. When the form is closed I would like to have the file automatically saved to a certian location with the file name added. An example would be I/work/engineeringdata/productdata/meters/turbine/(file name entered by user).xls. I could make a macro to run on the forms close to save the file name but am unsure how to attache the userinfo to the path and name. Thank you for your help. Tim Peter |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto save when form is closed
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Application.Dialogs(xlDialogSaveAs).Show ("4-4-08 Run 1") End Sub "tpeter" wrote: I have a userform that pops up when a file is open to make sure all of the data goes into the correct place. I would like to add 1 more textbox that would give the used the ability to type in the file name, an example would be 4-4-08 Run 1. When the form is closed I would like to have the file automatically saved to a certian location with the file name added. An example would be I/work/engineeringdata/productdata/meters/turbine/(file name entered by user).xls. I could make a macro to run on the forms close to save the file name but am unsure how to attache the userinfo to the path and name. Thank you for your help. Tim Peter |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto save when form is closed
Jeff,
That worked great thank you. I subsituted ("4-4-08 Run 1") for ("textbox26") and it did exaclty what I wanted. I still need to be able to put the path into this location.I:\work\Engineering Data\Product Data\Meters\Turbine Meters, I have tried putting it into the () and outside and I get an error. Any idea's? Thank you again for your help. "Jeff" wrote: Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) Application.Dialogs(xlDialogSaveAs).Show ("4-4-08 Run 1") End Sub "tpeter" wrote: I have a userform that pops up when a file is open to make sure all of the data goes into the correct place. I would like to add 1 more textbox that would give the used the ability to type in the file name, an example would be 4-4-08 Run 1. When the form is closed I would like to have the file automatically saved to a certian location with the file name added. An example would be I/work/engineeringdata/productdata/meters/turbine/(file name entered by user).xls. I could make a macro to run on the forms close to save the file name but am unsure how to attache the userinfo to the path and name. Thank you for your help. Tim Peter |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto save when form is closed
Try this
Option Explicit Private Const fPath As String = "I:\work\Engineering Data\Product Data\Meters\Turbine Meters\" Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) ThisWorkbook.SaveCopyAs (fPath & Chr(32) & TextBox26) End Sub "tpeter" wrote: Jeff, That worked great thank you. I subsituted ("4-4-08 Run 1") for ("textbox26") and it did exaclty what I wanted. I still need to be able to put the path into this location.I:\work\Engineering Data\Product Data\Meters\Turbine Meters, I have tried putting it into the () and outside and I get an error. Any idea's? Thank you again for your help. "Jeff" wrote: Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) Application.Dialogs(xlDialogSaveAs).Show ("4-4-08 Run 1") End Sub "tpeter" wrote: I have a userform that pops up when a file is open to make sure all of the data goes into the correct place. I would like to add 1 more textbox that would give the used the ability to type in the file name, an example would be 4-4-08 Run 1. When the form is closed I would like to have the file automatically saved to a certian location with the file name added. An example would be I/work/engineeringdata/productdata/meters/turbine/(file name entered by user).xls. I could make a macro to run on the forms close to save the file name but am unsure how to attache the userinfo to the path and name. Thank you for your help. Tim Peter |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto save when form is closed
Jeff,
I tried this and can't tell if it is working or not. fPath is breaking the code. I have made sure the path is correct for my network and gotten rid of the : between I and Work but it still breaks. Any Idea's? Thanks again for your assistance. "Jeff" wrote: Try this Option Explicit Private Const fPath As String = "I:\work\Engineering Data\Product Data\Meters\Turbine Meters\" Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) ThisWorkbook.SaveCopyAs (fPath & Chr(32) & TextBox26) End Sub "tpeter" wrote: Jeff, That worked great thank you. I subsituted ("4-4-08 Run 1") for ("textbox26") and it did exaclty what I wanted. I still need to be able to put the path into this location.I:\work\Engineering Data\Product Data\Meters\Turbine Meters, I have tried putting it into the () and outside and I get an error. Any idea's? Thank you again for your help. "Jeff" wrote: Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) Application.Dialogs(xlDialogSaveAs).Show ("4-4-08 Run 1") End Sub "tpeter" wrote: I have a userform that pops up when a file is open to make sure all of the data goes into the correct place. I would like to add 1 more textbox that would give the used the ability to type in the file name, an example would be 4-4-08 Run 1. When the form is closed I would like to have the file automatically saved to a certian location with the file name added. An example would be I/work/engineeringdata/productdata/meters/turbine/(file name entered by user).xls. I could make a macro to run on the forms close to save the file name but am unsure how to attache the userinfo to the path and name. Thank you for your help. Tim Peter |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto save when form is closed
this should do it....
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) Dim fPath As String ' Variable to hold filepath Dim fExtention As String 'Variable to hold File Extention Dim txtBoxValue As String 'Variable to hold TextBox Value Dim SavePath As String 'Variable to define final file path 'in your case this should be _ I:\work\Engineering Data\Product Data\Meters\Turbine Meters\ fPath = "C:\Documents and Settings\User\Desktop\" ' I am assuming you want to save this as an ".xls" 'but it can be whatever you need. fExtention = ".xls" 'This Just stores the TextBox26 value txtBoxValue = UserForm1.TextBox26.Value 'Packages it all up to a nice neat variable. SavePath = fPath & txtBoxValue & fExtention 'Saves a copy of the active workbook to the 'specified location. ThisWorkbook.SaveCopyAs (SavePath) End Sub "tpeter" wrote: Jeff, I tried this and can't tell if it is working or not. fPath is breaking the code. I have made sure the path is correct for my network and gotten rid of the : between I and Work but it still breaks. Any Idea's? Thanks again for your assistance. "Jeff" wrote: Try this Option Explicit Private Const fPath As String = "I:\work\Engineering Data\Product Data\Meters\Turbine Meters\" Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) ThisWorkbook.SaveCopyAs (fPath & Chr(32) & TextBox26) End Sub "tpeter" wrote: Jeff, That worked great thank you. I subsituted ("4-4-08 Run 1") for ("textbox26") and it did exaclty what I wanted. I still need to be able to put the path into this location.I:\work\Engineering Data\Product Data\Meters\Turbine Meters, I have tried putting it into the () and outside and I get an error. Any idea's? Thank you again for your help. "Jeff" wrote: Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) Application.Dialogs(xlDialogSaveAs).Show ("4-4-08 Run 1") End Sub "tpeter" wrote: I have a userform that pops up when a file is open to make sure all of the data goes into the correct place. I would like to add 1 more textbox that would give the used the ability to type in the file name, an example would be 4-4-08 Run 1. When the form is closed I would like to have the file automatically saved to a certian location with the file name added. An example would be I/work/engineeringdata/productdata/meters/turbine/(file name entered by user).xls. I could make a macro to run on the forms close to save the file name but am unsure how to attache the userinfo to the path and name. Thank you for your help. Tim Peter |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Call user form from ThisWorkbook; close file if form closed | Excel Programming | |||
Excel Form data to closed workbook | Excel Programming | |||
Global variable destroyed when form closed | Excel Programming | |||
ADO to get data form closed Excel | Excel Programming | |||
save Wsh to closed/opened WB | Excel Programming |