+ Reply to Thread
Results 1 to 8 of 8

Challenging?

  1. #1
    Forum Contributor
    Join Date
    04-27-2006
    Location
    Cayman Islands
    Posts
    375

    Challenging?

    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

  2. #2
    aidan.heritage@virgin.net
    Guest

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



  3. #3
    Tom Ogilvy
    Guest

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


  4. #4
    Chip Pearson
    Guest

    Re: 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"
    <ChrisMattock.2918pa_1149684601.3091@excelforum-nospam.com> wrote
    in message
    news:ChrisMattock.2918pa_1149684601.3091@excelforum-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
    >




  5. #5
    Chip Pearson
    Guest

    Re: 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" <chip@cpearson.com> wrote in message
    news:%23A$SsMjiGHA.4572@TK2MSFTNGP05.phx.gbl...
    > 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"
    > <ChrisMattock.2918pa_1149684601.3091@excelforum-nospam.com>
    > wrote in message
    > news:ChrisMattock.2918pa_1149684601.3091@excelforum-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
    >>

    >
    >




  6. #6
    Forum Contributor
    Join Date
    04-27-2006
    Location
    Cayman Islands
    Posts
    375
    That is awesome thanks all!

  7. #7
    Dave Peterson
    Guest

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

  8. #8
    Tom Ogilvy
    Guest

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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1