Challenging?

[ChrisMattock]

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

 

[aidan.heritage]

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!

 

[Tom Ogilvy]

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

 

[Chip Pearson]

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"
in message
news:[email protected]...

 

[Chip Pearson]

Dim FNum As String

should be

Dim FNum As Integer


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

 

[ChrisMattock]

That is awesome thanks all

 

[Dave Peterson]

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]

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