I am developing an Excel form for a very large group of inexperienced Excel
users at my workplace. The process needs to be as easy as possible! The
objective is for users to only have to input their unique data into the
master form and then click on a macro button. The macro generates a new
separate file with all their data processed in the manner necessary. That
all is working fine. However, each of these new files created through the
master form needs to have its own unique file name using a specific naming
convention. I would strongly prefer that the users not have to name their
new file themselves -- I want the macro to do it for them.

I can easily use formula to put the proper file name text into a cell of the
new workbook, but cannot figure out how to get it that text into the Save-as
file-name box when the file is first saved. This all would be very simple if
Excel worked like Word, where the default file name comes from the beginning
of the document. If I could just make it read A1 as the default that would
be great, but Excel defaults to "Book1" instead. Any suggestions would be
greatly appreciated!

Application.Dialogs(xlDialogSaveAs).Show

If this post helps click Yes
---------------
Jacob Skaria


"Allouette" wrote:

I am developing an Excel form for a very large group of inexperienced Excel
users at my workplace. The process needs to be as easy as possible! The
objective is for users to only have to input their unique data into the
master form and then click on a macro button. The macro generates a new
separate file with all their data processed in the manner necessary. That
all is working fine. However, each of these new files created through the
master form needs to have its own unique file name using a specific naming
convention. I would strongly prefer that the users not have to name their
new file themselves -- I want the macro to do it for them.

I can easily use formula to put the proper file name text into a cell of the
new workbook, but cannot figure out how to get it that text into the Save-as
file-name box when the file is first saved. This all would be very simple if
Excel worked like Word, where the default file name comes from the beginning
of the document. If I could just make it read A1 as the default that would
be great, but Excel defaults to "Book1" instead. Any suggestions would be
greatly appreciated!

You can avoid using the SaveAs and get the user name using the below

'--------GetUserName
Environ("Username")

OR

'-----Get User Name
Set objNet = CreateObject("WScript.NetWork")
strUserName = Trim(objNet.UserName)


OR even get the domain name

'-----Retrieve Domain Name
' Reference Active DS Type Library Tools|Reference
Dim strAD As New ADSystemInfo
strDomainname = strAD.DomainShortName

If this post helps click Yes
---------------
Jacob Skaria


"Allouette" wrote:

I am developing an Excel form for a very large group of inexperienced Excel
users at my workplace. The process needs to be as easy as possible! The
objective is for users to only have to input their unique data into the
master form and then click on a macro button. The macro generates a new
separate file with all their data processed in the manner necessary. That
all is working fine. However, each of these new files created through the
master form needs to have its own unique file name using a specific naming
convention. I would strongly prefer that the users not have to name their
new file themselves -- I want the macro to do it for them.

I can easily use formula to put the proper file name text into a cell of the
new workbook, but cannot figure out how to get it that text into the Save-as
file-name box when the file is first saved. This all would be very simple if
Excel worked like Word, where the default file name comes from the beginning
of the document. If I could just make it read A1 as the default that would
be great, but Excel defaults to "Book1" instead. Any suggestions would be
greatly appreciated!

Thank you for responding, but I'm afraid both your replies are above my head!
The process I am trying to manage is for recording incoming payments across
a large municipality. I don't need to know the user name for the file name.
The file name should be a concatenation of three pieces of information:
payment location, date of deposit, and dollar amount. I have formula so that
one of the cells of the newly created workbook has the text exactly as the
file should be named. The problem is actually getting the new file named
that way (with a macro or some method other than my end-users). Thanks again!

"Jacob Skaria" wrote:

You can avoid using the SaveAs and get the user name using the below

'--------GetUserName
Environ("Username")

OR

'-----Get User Name
Set objNet = CreateObject("WScript.NetWork")
strUserName = Trim(objNet.UserName)


OR even get the domain name

'-----Retrieve Domain Name
' Reference Active DS Type Library Tools|Reference
Dim strAD As New ADSystemInfo
strDomainname = strAD.DomainShortName

If this post helps click Yes
---------------
Jacob Skaria


"Allouette" wrote:

I am developing an Excel form for a very large group of inexperienced Excel
users at my workplace. The process needs to be as easy as possible! The
objective is for users to only have to input their unique data into the
master form and then click on a macro button. The macro generates a new
separate file with all their data processed in the manner necessary. That
all is working fine. However, each of these new files created through the
master form needs to have its own unique file name using a specific naming
convention. I would strongly prefer that the users not have to name their
new file themselves -- I want the macro to do it for them.

I can easily use formula to put the proper file name text into a cell of the
new workbook, but cannot figure out how to get it that text into the Save-as
file-name box when the file is first saved. This all would be very simple if
Excel worked like Word, where the default file name comes from the beginning
of the document. If I could just make it read A1 as the default that would
be great, but Excel defaults to "Book1" instead. Any suggestions would be
greatly appreciated!

If you are looking to get the save event wrote the code in the
Workbook_BeforeSave event

strFileName = Get file name here
'Save the new workbook.
ActiveWorkbook.SaveAs (strPath & strFileName)

If this post helps click Yes
---------------
Jacob Skaria


"Allouette" wrote:

Thank you for responding, but I'm afraid both your replies are above my head!
The process I am trying to manage is for recording incoming payments across
a large municipality. I don't need to know the user name for the file name.
The file name should be a concatenation of three pieces of information:
payment location, date of deposit, and dollar amount. I have formula so that
one of the cells of the newly created workbook has the text exactly as the
file should be named. The problem is actually getting the new file named
that way (with a macro or some method other than my end-users). Thanks again!

"Jacob Skaria" wrote:

You can avoid using the SaveAs and get the user name using the below

'--------GetUserName
Environ("Username")

OR

'-----Get User Name
Set objNet = CreateObject("WScript.NetWork")
strUserName = Trim(objNet.UserName)


OR even get the domain name

'-----Retrieve Domain Name
' Reference Active DS Type Library Tools|Reference
Dim strAD As New ADSystemInfo
strDomainname = strAD.DomainShortName

If this post helps click Yes
---------------
Jacob Skaria


"Allouette" wrote:

I am developing an Excel form for a very large group of inexperienced Excel
users at my workplace. The process needs to be as easy as possible! The
objective is for users to only have to input their unique data into the
master form and then click on a macro button. The macro generates a new
separate file with all their data processed in the manner necessary. That
all is working fine. However, each of these new files created through the
master form needs to have its own unique file name using a specific naming
convention. I would strongly prefer that the users not have to name their
new file themselves -- I want the macro to do it for them.

I can easily use formula to put the proper file name text into a cell of the
new workbook, but cannot figure out how to get it that text into the Save-as
file-name box when the file is first saved. This all would be very simple if
Excel worked like Word, where the default file name comes from the beginning
of the document. If I could just make it read A1 as the default that would
be great, but Excel defaults to "Book1" instead. Any suggestions would be
greatly appreciated!

strFileName = Range("A1")
OR
strFileName = Cells(1,1)
ActiveWorkbook.SaveAs (strPath & strFileName)

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

If you are looking to get the save event wrote the code in the
Workbook_BeforeSave event

strFileName = Get file name here
'Save the new workbook.
ActiveWorkbook.SaveAs (strPath & strFileName)

If this post helps click Yes
---------------
Jacob Skaria


"Allouette" wrote:

Thank you for responding, but I'm afraid both your replies are above my head!
The process I am trying to manage is for recording incoming payments across
a large municipality. I don't need to know the user name for the file name.
The file name should be a concatenation of three pieces of information:
payment location, date of deposit, and dollar amount. I have formula so that
one of the cells of the newly created workbook has the text exactly as the
file should be named. The problem is actually getting the new file named
that way (with a macro or some method other than my end-users). Thanks again!

"Jacob Skaria" wrote:

You can avoid using the SaveAs and get the user name using the below

'--------GetUserName
Environ("Username")

OR

'-----Get User Name
Set objNet = CreateObject("WScript.NetWork")
strUserName = Trim(objNet.UserName)


OR even get the domain name

'-----Retrieve Domain Name
' Reference Active DS Type Library Tools|Reference
Dim strAD As New ADSystemInfo
strDomainname = strAD.DomainShortName

If this post helps click Yes
---------------
Jacob Skaria


"Allouette" wrote:

I am developing an Excel form for a very large group of inexperienced Excel
users at my workplace. The process needs to be as easy as possible! The
objective is for users to only have to input their unique data into the
master form and then click on a macro button. The macro generates a new
separate file with all their data processed in the manner necessary. That
all is working fine. However, each of these new files created through the
master form needs to have its own unique file name using a specific naming
convention. I would strongly prefer that the users not have to name their
new file themselves -- I want the macro to do it for them.

I can easily use formula to put the proper file name text into a cell of the
new workbook, but cannot figure out how to get it that text into the Save-as
file-name box when the file is first saved. This all would be very simple if
Excel worked like Word, where the default file name comes from the beginning
of the document. If I could just make it read A1 as the default that would
be great, but Excel defaults to "Book1" instead. Any suggestions would be
greatly appreciated!