Looking for help with user input variable€¦
 

I have created a .bat file that prompts a user for a Project name. The
program then creates a project specific directory; copies a couple Excel
templates into the directory, then rename the templates to be project
specific and makes them .xls files based on the user information.

My question is there a way to have this .bat file update the templates, like
put the Project name in cell A1?

I guess what I am trying to say is that I cant seem to paste my variable
from my .bat into an excel worksheet. Is there a way to accomplish this, or
another way to make it happen?

Thank you in advance.

Looking for help with user input variable€¦
 

You could have your .bat file create a small .txt file in the same folder
with the project name in it as a one-line entry. Then in the workbook's
_Open event you could put some code like this:

Private Sub Workbook_Open()
Dim myProjectName As String
Dim myFilenum As Integer
If ThisWorkbook.Path = "" Then
Exit Sub
End If
myFilenum = FreeFile()

If DIR(ThisWorkbookPath & Application.PathSeparator _
& "ProjectName.txt") < "" Then
Open ThisWorkbook.Path & Application.PathSeparator _
& "ProjectName.txt" For Input As #myFilenum
Input #myFilenum, myProjectName
Close #myFilenum
Else
Exit Sub
End If
On Error Resume Next
Worksheets("Sheet1").Range("A1") = myProjectName
If Err<0 Then
Err.Clear
End If
On Error Goto 0
End Sub

That code will work in a the .xls file(s) - won't when you create new from
the .xlt file initially. What it does is determine the path where the .xls
file is stored and then goes to look for a file named 'ProjectName.txt' in
that same folder, reads one line from it and stuffs it into Sheet1!A1

That code needs to go into the workbook event section. To get their
quickly, right click on the Excel icon immediately to the left of [File] in
the menu bar and choose View Code from the popup list. Copy and paste the
code into it.

It checks to make sure that the ProjectName.txt file exists and if not, then
just quits. Later it watches out for the case where there is not a sheet
with the name you've specified (Sheet1 in the code, change as required) in
the workbook, which might be the case if you've got users who get frisky
about renaming worksheets and such as that.

"Steve" wrote:

I have created a .bat file that prompts a user for a Project name. The
program then creates a project specific directory; copies a couple Excel
templates into the directory, then rename the templates to be project
specific and makes them .xls files based on the user information.

My question is there a way to have this .bat file update the templates, like
put the Project name in cell A1?

I guess what I am trying to say is that I cant seem to paste my variable
from my .bat into an excel worksheet. Is there a way to accomplish this, or
another way to make it happen?

Thank you in advance.

Looking for help with user input variable€¦
 

Thank you for your help! It is crazy how all this stuff works.

I have 2 other questions now.

1. How do I save the user imput to a text file? The variable is taken from
a "set /p project" in the .bat file.

2. Is there a way to make this _Open event to run only on the 1st time the
file is opened?

Thank you once again.


"JLatham" wrote:

You could have your .bat file create a small .txt file in the same folder
with the project name in it as a one-line entry. Then in the workbook's
_Open event you could put some code like this:

Private Sub Workbook_Open()
Dim myProjectName As String
Dim myFilenum As Integer
If ThisWorkbook.Path = "" Then
Exit Sub
End If
myFilenum = FreeFile()

If DIR(ThisWorkbookPath & Application.PathSeparator _
& "ProjectName.txt") < "" Then
Open ThisWorkbook.Path & Application.PathSeparator _
& "ProjectName.txt" For Input As #myFilenum
Input #myFilenum, myProjectName
Close #myFilenum
Else
Exit Sub
End If
On Error Resume Next
Worksheets("Sheet1").Range("A1") = myProjectName
If Err<0 Then
Err.Clear
End If
On Error Goto 0
End Sub

That code will work in a the .xls file(s) - won't when you create new from
the .xlt file initially. What it does is determine the path where the .xls
file is stored and then goes to look for a file named 'ProjectName.txt' in
that same folder, reads one line from it and stuffs it into Sheet1!A1

That code needs to go into the workbook event section. To get their
quickly, right click on the Excel icon immediately to the left of [File] in
the menu bar and choose View Code from the popup list. Copy and paste the
code into it.

It checks to make sure that the ProjectName.txt file exists and if not, then
just quits. Later it watches out for the case where there is not a sheet
with the name you've specified (Sheet1 in the code, change as required) in
the workbook, which might be the case if you've got users who get frisky
about renaming worksheets and such as that.

"Steve" wrote:

I have created a .bat file that prompts a user for a Project name. The
program then creates a project specific directory; copies a couple Excel
templates into the directory, then rename the templates to be project
specific and makes them .xls files based on the user information.

My question is there a way to have this .bat file update the templates, like
put the Project name in cell A1?

I guess what I am trying to say is that I cant seem to paste my variable
from my .bat into an excel worksheet. Is there a way to accomplish this, or
another way to make it happen?

Thank you in advance.