Challenging?
 

Hi all, I have an excel spreadsheet that on pushing a button opens up a
input box, then modifies a template and saves it as a name defined fro
the inputbox, in a directory based on a field in the excel sheet.

What I also want it to do is to add a line to a notepad document as
log, preferably, saying the project name, time, date and the user wh
is using the Excel sheet. Any ideas?

fname$ = InputBox("Save Letter of Acceptance as
PROJECTNUMBER_PROJECTNAME_SUPPLIER_LOA:")
If fname$ = Cancel Then
End
End If

Dim appWD As Word.Application
Set appWD = CreateObject("word.application.8")
appWD.Visible = True
appWD.Documents.Ope
FileName:="\\lgwsvr011\group9\Projects-Everyone\51 Gatwick Airpor
Team\SE Airports TSA Tea
Folder\LOA_Generator\Templates\LOA_Template.doc"

appWD.ActiveDocument.Bookmarks("LOADate").Range
Format(strLOADate, "d mmmm yyyy")

If Dir("\\lgwsvr011\group9\Projects-Everyone\51 Gatwick Airpor
Team\SE Airports TSA Team Folder\LOA_Generator\LOA\"
strProjectNumber, vbDirectory) = "" Then MkDi
"\\lgwsvr011\group9\Projects-Everyone\51 Gatwick Airport Team\S
Airports TSA Team Folder\LOA_Generator\LOA\" & strProjectNumber
appWD.ActiveDocument.SaveA
FileName:="\\lgwsvr011\group9\Projects-Everyone\51 Gatwick Airpor
Team\SE Airports TSA Team Folder\LOA_Generator\LOA\" & strProjectNumbe
& "\" & fname$
appWD.ActiveDocument.Close
appWD.Qui

--
ChrisMattoc
-----------------------------------------------------------------------
ChrisMattock's Profile: http://www.excelforum.com/member.php...fo&userid=3391
View this thread: http://www.excelforum.com/showthread.php?threadid=54943

Challenging?
 

open "notepadfile" for append as #1
Write #1, Range("A1").Value
Close #1

should do it for you

by the way, if it IS a template, why not have it as a DOT (i.e.
template file) and File NEW it - this way, you have less chance of an
accidental save rather than saveas!

ChrisMattock wrote:
Hi all, I have an excel spreadsheet that on pushing a button opens up an
input box, then modifies a template and saves it as a name defined from
the inputbox, in a directory based on a field in the excel sheet.

What I also want it to do is to add a line to a notepad document as a
log, preferably, saying the project name, time, date and the user who
is using the Excel sheet. Any ideas?

fname$ = InputBox("Save Letter of Acceptance as
PROJECTNUMBER_PROJECTNAME_SUPPLIER_LOA:")
If fname$ = Cancel Then
End
End If

Dim appWD As Word.Application
Set appWD = CreateObject("word.application.8")
appWD.Visible = True
appWD.Documents.Open
FileName:="\\lgwsvr011\group9\Projects-Everyone\51 Gatwick Airport
Team\SE Airports TSA Team
Folder\LOA_Generator\Templates\LOA_Template.doc"

appWD.ActiveDocument.Bookmarks("LOADate").Range =
Format(strLOADate, "d mmmm yyyy")

If Dir("\\lgwsvr011\group9\Projects-Everyone\51 Gatwick Airport
Team\SE Airports TSA Team Folder\LOA_Generator\LOA\" &
strProjectNumber, vbDirectory) = "" Then MkDir
"\\lgwsvr011\group9\Projects-Everyone\51 Gatwick Airport Team\SE
Airports TSA Team Folder\LOA_Generator\LOA\" & strProjectNumber
appWD.ActiveDocument.SaveAs
FileName:="\\lgwsvr011\group9\Projects-Everyone\51 Gatwick Airport
Team\SE Airports TSA Team Folder\LOA_Generator\LOA\" & strProjectNumber
& "\" & fname$
appWD.ActiveDocument.Close
appWD.Quit


--
ChrisMattock
------------------------------------------------------------------------
ChrisMattock's Profile: http://www.excelforum.com/member.php...o&userid=33912
View this thread: http://www.excelforum.com/showthread...hreadid=549437

Challenging?
 

here is some prevously posted code for writing a LOG file (a text file)

Sub DoTheLog(myKey As String)
Open ThisWorkbook.Path & "\" & Left(ActiveWorkbook.Name, _
Len(ActiveWorkbook.Name) - 4) & "_usage.log" For Append As #1
Print #1, myKey & vbTab & Application.UserName _
& vbTab & fOSUserName _
& vbTab & fOSMachineName _
& vbTab & Format(Now, "mmmm dd, yyyy hh:mm:ss")
Close #1
End Sub

--
Regards,
Tom Ogilvy


"ChrisMattock" wrote:


Hi all, I have an excel spreadsheet that on pushing a button opens up an
input box, then modifies a template and saves it as a name defined from
the inputbox, in a directory based on a field in the excel sheet.

What I also want it to do is to add a line to a notepad document as a
log, preferably, saying the project name, time, date and the user who
is using the Excel sheet. Any ideas?

fname$ = InputBox("Save Letter of Acceptance as
PROJECTNUMBER_PROJECTNAME_SUPPLIER_LOA:")
If fname$ = Cancel Then
End
End If

Dim appWD As Word.Application
Set appWD = CreateObject("word.application.8")
appWD.Visible = True
appWD.Documents.Open
FileName:="\\lgwsvr011\group9\Projects-Everyone\51 Gatwick Airport
Team\SE Airports TSA Team
Folder\LOA_Generator\Templates\LOA_Template.doc"

appWD.ActiveDocument.Bookmarks("LOADate").Range =
Format(strLOADate, "d mmmm yyyy")

If Dir("\\lgwsvr011\group9\Projects-Everyone\51 Gatwick Airport
Team\SE Airports TSA Team Folder\LOA_Generator\LOA\" &
strProjectNumber, vbDirectory) = "" Then MkDir
"\\lgwsvr011\group9\Projects-Everyone\51 Gatwick Airport Team\SE
Airports TSA Team Folder\LOA_Generator\LOA\" & strProjectNumber
appWD.ActiveDocument.SaveAs
FileName:="\\lgwsvr011\group9\Projects-Everyone\51 Gatwick Airport
Team\SE Airports TSA Team Folder\LOA_Generator\LOA\" & strProjectNumber
& "\" & fname$
appWD.ActiveDocument.Close
appWD.Quit


--
ChrisMattock
------------------------------------------------------------------------
ChrisMattock's Profile: http://www.excelforum.com/member.php...o&userid=33912
View this thread: http://www.excelforum.com/showthread...hreadid=549437

Challenging?
 

Try

Dim FName As String
Dim FNum As String
FNum = FreeFile
Open ThisWorkbook.Path & "\Log.txt" For Append As #FNum
Print #FNum, ThisWorkbook.FullName, Format(Now, "dd-mmm-yyyy
hh:mm"), _
Environ("username")
Close #FNum


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"ChrisMattock"
wrote
in message
news:ChrisMattock.2918pa_1149684601.3091@excelforu m-nospam.com...

Hi all, I have an excel spreadsheet that on pushing a button
opens up an
input box, then modifies a template and saves it as a name
defined from
the inputbox, in a directory based on a field in the excel
sheet.

What I also want it to do is to add a line to a notepad
document as a
log, preferably, saying the project name, time, date and the
user who
is using the Excel sheet. Any ideas?

fname$ = InputBox("Save Letter of Acceptance as
PROJECTNUMBER_PROJECTNAME_SUPPLIER_LOA:")
If fname$ = Cancel Then
End
End If

Dim appWD As Word.Application
Set appWD = CreateObject("word.application.8")
appWD.Visible = True
appWD.Documents.Open
FileName:="\\lgwsvr011\group9\Projects-Everyone\51 Gatwick
Airport
Team\SE Airports TSA Team
Folder\LOA_Generator\Templates\LOA_Template.doc"

appWD.ActiveDocument.Bookmarks("LOADate").Range =
Format(strLOADate, "d mmmm yyyy")

If Dir("\\lgwsvr011\group9\Projects-Everyone\51 Gatwick Airport
Team\SE Airports TSA Team Folder\LOA_Generator\LOA\" &
strProjectNumber, vbDirectory) = "" Then MkDir
"\\lgwsvr011\group9\Projects-Everyone\51 Gatwick Airport
Team\SE
Airports TSA Team Folder\LOA_Generator\LOA\" & strProjectNumber
appWD.ActiveDocument.SaveAs
FileName:="\\lgwsvr011\group9\Projects-Everyone\51 Gatwick
Airport
Team\SE Airports TSA Team Folder\LOA_Generator\LOA\" &
strProjectNumber
& "\" & fname$
appWD.ActiveDocument.Close
appWD.Quit


--
ChrisMattock
------------------------------------------------------------------------
ChrisMattock's Profile:
http://www.excelforum.com/member.php...o&userid=33912
View this thread:
http://www.excelforum.com/showthread...hreadid=549437

Challenging?
 

Dim FNum As String

should be

Dim FNum As Integer


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Chip Pearson" wrote in message
...
Try

Dim FName As String
Dim FNum As String
FNum = FreeFile
Open ThisWorkbook.Path & "\Log.txt" For Append As #FNum
Print #FNum, ThisWorkbook.FullName, Format(Now, "dd-mmm-yyyy
hh:mm"), _
Environ("username")
Close #FNum


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"ChrisMattock"

wrote in message
news:ChrisMattock.2918pa_1149684601.3091@excelforu m-nospam.com...

Hi all, I have an excel spreadsheet that on pushing a button
opens up an
input box, then modifies a template and saves it as a name
defined from
the inputbox, in a directory based on a field in the excel
sheet.

What I also want it to do is to add a line to a notepad
document as a
log, preferably, saying the project name, time, date and the
user who
is using the Excel sheet. Any ideas?

fname$ = InputBox("Save Letter of Acceptance as
PROJECTNUMBER_PROJECTNAME_SUPPLIER_LOA:")
If fname$ = Cancel Then
End
End If

Dim appWD As Word.Application
Set appWD = CreateObject("word.application.8")
appWD.Visible = True
appWD.Documents.Open
FileName:="\\lgwsvr011\group9\Projects-Everyone\51 Gatwick
Airport
Team\SE Airports TSA Team
Folder\LOA_Generator\Templates\LOA_Template.doc"

appWD.ActiveDocument.Bookmarks("LOADate").Range =
Format(strLOADate, "d mmmm yyyy")

If Dir("\\lgwsvr011\group9\Projects-Everyone\51 Gatwick
Airport
Team\SE Airports TSA Team Folder\LOA_Generator\LOA\" &
strProjectNumber, vbDirectory) = "" Then MkDir
"\\lgwsvr011\group9\Projects-Everyone\51 Gatwick Airport
Team\SE
Airports TSA Team Folder\LOA_Generator\LOA\" &
strProjectNumber
appWD.ActiveDocument.SaveAs
FileName:="\\lgwsvr011\group9\Projects-Everyone\51 Gatwick
Airport
Team\SE Airports TSA Team Folder\LOA_Generator\LOA\" &
strProjectNumber
& "\" & fname$
appWD.ActiveDocument.Close
appWD.Quit


--
ChrisMattock
------------------------------------------------------------------------
ChrisMattock's Profile:
http://www.excelforum.com/member.php...o&userid=33912
View this thread:
http://www.excelforum.com/showthread...hreadid=549437

Challenging?
 

That is awesome thanks all

--
ChrisMattoc
-----------------------------------------------------------------------
ChrisMattock's Profile: http://www.excelforum.com/member.php...fo&userid=3391
View this thread: http://www.excelforum.com/showthread.php?threadid=54943

Challenging?
 

With a couple more functions:

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Private Declare Function apiGetComputerName Lib "kernel32" Alias _
"GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX < 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = ""
End If
End Function
Function fOSMachineName() As String
'Returns the computername
Dim lngLen As Long, lngX As Long
Dim strCompName As String
lngLen = 255
strCompName = String$(lngLen - 1, 0)
lngX = apiGetComputerName(strCompName, lngLen)
If lngX < 0 Then
fOSMachineName = Left$(strCompName, lngLen)
Else
fOSMachineName = ""
End If
End Function

Tom Ogilvy wrote:

here is some prevously posted code for writing a LOG file (a text file)

Sub DoTheLog(myKey As String)
Open ThisWorkbook.Path & "\" & Left(ActiveWorkbook.Name, _
Len(ActiveWorkbook.Name) - 4) & "_usage.log" For Append As #1
Print #1, myKey & vbTab & Application.UserName _
& vbTab & fOSUserName _
& vbTab & fOSMachineName _
& vbTab & Format(Now, "mmmm dd, yyyy hh:mm:ss")
Close #1
End Sub

--
Regards,
Tom Ogilvy

"ChrisMattock" wrote:


Hi all, I have an excel spreadsheet that on pushing a button opens up an
input box, then modifies a template and saves it as a name defined from
the inputbox, in a directory based on a field in the excel sheet.

What I also want it to do is to add a line to a notepad document as a
log, preferably, saying the project name, time, date and the user who
is using the Excel sheet. Any ideas?

fname$ = InputBox("Save Letter of Acceptance as
PROJECTNUMBER_PROJECTNAME_SUPPLIER_LOA:")
If fname$ = Cancel Then
End
End If

Dim appWD As Word.Application
Set appWD = CreateObject("word.application.8")
appWD.Visible = True
appWD.Documents.Open
FileName:="\\lgwsvr011\group9\Projects-Everyone\51 Gatwick Airport
Team\SE Airports TSA Team
Folder\LOA_Generator\Templates\LOA_Template.doc"

appWD.ActiveDocument.Bookmarks("LOADate").Range =
Format(strLOADate, "d mmmm yyyy")

If Dir("\\lgwsvr011\group9\Projects-Everyone\51 Gatwick Airport
Team\SE Airports TSA Team Folder\LOA_Generator\LOA\" &
strProjectNumber, vbDirectory) = "" Then MkDir
"\\lgwsvr011\group9\Projects-Everyone\51 Gatwick Airport Team\SE
Airports TSA Team Folder\LOA_Generator\LOA\" & strProjectNumber
appWD.ActiveDocument.SaveAs
FileName:="\\lgwsvr011\group9\Projects-Everyone\51 Gatwick Airport
Team\SE Airports TSA Team Folder\LOA_Generator\LOA\" & strProjectNumber
& "\" & fname$
appWD.ActiveDocument.Close
appWD.Quit


--
ChrisMattock
------------------------------------------------------------------------
ChrisMattock's Profile: http://www.excelforum.com/member.php...o&userid=33912
View this thread: http://www.excelforum.com/showthread...hreadid=549437



--

Dave Peterson

Challenging?
 

Actually, I was more focused on showing how to append to the text file rather
than specifically entering that information - his list of information to
write didn't include any of that except if by user, he meant the login name.


Probably more like:

'General Module
Public myname as String

'ThisWorkbook Module
Private Sub Workbook_Open()
myname = InputBox("hi, please enter your name")
end sub

------
Looks like the OP has a solution.

--
Regards,
Tom Ogilvy



"Dave Peterson" wrote:

With a couple more functions:

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Private Declare Function apiGetComputerName Lib "kernel32" Alias _
"GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX < 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = ""
End If
End Function
Function fOSMachineName() As String
'Returns the computername
Dim lngLen As Long, lngX As Long
Dim strCompName As String
lngLen = 255
strCompName = String$(lngLen - 1, 0)
lngX = apiGetComputerName(strCompName, lngLen)
If lngX < 0 Then
fOSMachineName = Left$(strCompName, lngLen)
Else
fOSMachineName = ""
End If
End Function

Tom Ogilvy wrote:

here is some prevously posted code for writing a LOG file (a text file)

Sub DoTheLog(myKey As String)
Open ThisWorkbook.Path & "\" & Left(ActiveWorkbook.Name, _
Len(ActiveWorkbook.Name) - 4) & "_usage.log" For Append As #1
Print #1, myKey & vbTab & Application.UserName _
& vbTab & fOSUserName _
& vbTab & fOSMachineName _
& vbTab & Format(Now, "mmmm dd, yyyy hh:mm:ss")
Close #1
End Sub

--
Regards,
Tom Ogilvy

"ChrisMattock" wrote:


Hi all, I have an excel spreadsheet that on pushing a button opens up an
input box, then modifies a template and saves it as a name defined from
the inputbox, in a directory based on a field in the excel sheet.

What I also want it to do is to add a line to a notepad document as a
log, preferably, saying the project name, time, date and the user who
is using the Excel sheet. Any ideas?

fname$ = InputBox("Save Letter of Acceptance as
PROJECTNUMBER_PROJECTNAME_SUPPLIER_LOA:")
If fname$ = Cancel Then
End
End If

Dim appWD As Word.Application
Set appWD = CreateObject("word.application.8")
appWD.Visible = True
appWD.Documents.Open
FileName:="\\lgwsvr011\group9\Projects-Everyone\51 Gatwick Airport
Team\SE Airports TSA Team
Folder\LOA_Generator\Templates\LOA_Template.doc"

appWD.ActiveDocument.Bookmarks("LOADate").Range =
Format(strLOADate, "d mmmm yyyy")

If Dir("\\lgwsvr011\group9\Projects-Everyone\51 Gatwick Airport
Team\SE Airports TSA Team Folder\LOA_Generator\LOA\" &
strProjectNumber, vbDirectory) = "" Then MkDir
"\\lgwsvr011\group9\Projects-Everyone\51 Gatwick Airport Team\SE
Airports TSA Team Folder\LOA_Generator\LOA\" & strProjectNumber
appWD.ActiveDocument.SaveAs
FileName:="\\lgwsvr011\group9\Projects-Everyone\51 Gatwick Airport
Team\SE Airports TSA Team Folder\LOA_Generator\LOA\" & strProjectNumber
& "\" & fname$
appWD.ActiveDocument.Close
appWD.Quit


--
ChrisMattock
------------------------------------------------------------------------
ChrisMattock's Profile: http://www.excelforum.com/member.php...o&userid=33912
View this thread: http://www.excelforum.com/showthread...hreadid=549437



--

Dave Peterson