Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Challenging?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 244
Default 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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default 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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default 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 With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7,247
Default 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



Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7,247
Default 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





Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Challenging Charting C TO Charts and Charting in Excel 0 January 17th 05 06:57 PM
Need help on challenging question tanner[_5_] Excel Programming 0 July 28th 04 04:19 PM
Very challenging madcat Excel Programming 2 June 25th 04 02:13 AM
Something Challenging Swift2003[_4_] Excel Programming 3 April 17th 04 09:03 AM
Challenging Formula in VB Bruce Roberson[_2_] Excel Programming 7 January 21st 04 12:28 PM


All times are GMT +1. The time now is 09:35 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement