Challenging?

[ChrisMattock]

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

[[email protected]]

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

[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

--
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
>
>

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

[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


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

[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 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

[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

------
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
>