Solved: Save data to notepad file and leave link in worksheet

[tccmdr]

Hello Gurus ,

I'm looking for any code that will allow me, using a CommandButton from Excel, to open NotePad, write some data, save the NotePad file to a location and leave a link in a cell in the worksheet containing the CommandButton??


Any assisstance is greatly appreciated

[mvidas]

Mike,

Do you want to write to the notepad file programmatically (ie not see notepad), or do you want to actually see it?

If you want to do it while not seeing notepad then it is very easy, we can supply that code for you (you'll have to tell us if you are creating a new file or an existing file, and if existing do you want to replace the current contents or simply add to them).

If you want to manually type into notepad, it can also be done, but you'll have to choose the filename ahead of time.

So basically the only thing we couldnt do (easily at least, but it could be done if need really be) is open a blank notepad/text file, let you type what you want into it and choose a name at that time, then have a link added.

So I guess, could you just explain a little more what you want?

[tccmdr]

G'Day Matt ,

No, don't need to see notepad unless, to view notes already saved, using the link.

I guess I'll need to be able to create the file in the first instance and then add to it as more information comes to hand;

perhaps with a date reference at the beginning of each entry

[mvidas]

I understand.. give the following a try, real basic example but it should give you what you're looking to do. Of course feel free to ask specifics, but this should help you out:[vba]Sub MikeTextFileExample()
Dim vFF As Long, vFile As String, tempStr As String

'output filename, we can choose this programmatically if desired
vFile = "C:\your file name.txt"

'get a string to add to the file, just for this example
tempStr = InputBox("Please enter string to add to file", "Enter text")

'get an unused file number (for vba to refer to it)
vFF = FreeFile

'open the file so we can write to it
Open vFile For Append As #vFF

'add the line to the file
Print #vFF, tempStr

'close the reference to the file
Close #vFF

'add the link to cell C11 of activesheet
ActiveSheet.Hyperlinks.Add Range("C11"), vFile
End Sub[/vba]

[mvidas]

Oops, forgot the date entry part [vba] Print #vFF, Format(Now, "yyyymmdd-hhmmss ") & tempStr[/vba]

[tccmdr]

Thanks for your assistance Matt, works great

OK, this is where I go for the Car.........

1. Can I create a line feed between the Date and the String???
2. Can tempStr = TextBox1.Value???
3. Can I create seperate files for seperate names in a ListBox and therefore, seperate links on the worksheet??? (i.e. Employee Notes)

[mvidas]

1) Sure! You can do it multiple ways, either concatenate a vbCrLf in between the date and tempStr, or just use 2 lines of Print:[vba]Print #vFF, Format(Now, "yyyymmdd-hhmmss ")
Print #vFF, tempStr[/vba]
Using Print like that adds a line to the file. As an FYI (not really applicable for your request), if you dont want a line feed added, put a semi colon after the line:[vba]Print #vFF, "a";
Print #vFF, "b";
Print #vFF, "c"[/vba]Those lines will add "abc" to the file. You can use as many Print statements as you want though.

2) Sure! You can use any string for tempStr (or just use TextBox1 in the Print statement):[vba]Print #vFF, TextBox1.Text[/vba]

3) Sure! The Range("C11") is the anchor for the link, just change that accordingly The easiest way you could probably do this is to use a starting cell, then Offset it by the ListIndex of the listbox (assuming you're not using a multiselect listbox--if so it just changes a little bit). The ListIndex is the index number of the selected item in a listbox (-1 means nothing selected, 0 for the first item, 1 for the second, etc). So you could use something like:[vba]ActiveSheet.Hyperlinks.Add Range("C11").Offset(ListBox1.ListIndex, 0), vFile[/vba]You may want to make sure that the ListIndex isn't -1 before you do that though.


You can even use the selected item of the listbox to identify the filename.. so if your listbox contains employee names, you could use something like[vba]vFile = "C:\employee files\" & ListBox1.Value & ".txt"[/vba]

[tccmdr]

Absolute Champion

I look forward to putting it together tomorrow morning.

I'll leave the thread unsolved for now so I can let you know how it goes.

Thanks for all the help

[tccmdr]

Matt,

Everything is working fine, used this
[VBA]
With UserForm1.ListBox1
Sheet1.Hyperlinks.Add Range(.RowSource)(.ListIndex + 1, 24), vFile
End With
[/VBA]
places in correct cell at the end of the relevant recordset.

Q.
How can I open a hyperlink from the same userform I created it from?

[mvidas]

You should be able to use something like:[vba] With UserForm1.ListBox1
Sheet1.Range(.RowSource)(.ListIndex + 1, 24).Hyperlinks(1).Follow
End With[/vba]

[tccmdr]

Matt,

Used the following with a CommandButton (Caption:="View Notes")

[VBA]
Private Sub CommandButton1_Click()
Unload Me
Dim SourceRange As Excel.Range
Dim Val1 As String, Val2 As String, Val3 As String
Set SourceRange = Range("Sheet1!A2:W2")

Val1 = UserForm1.ListBox1.Value
Val2 = SourceRange.Offset(UserForm1.ListBox1.ListIndex, 1).Resize(1, 1).Value
Val3 = SourceRange.Offset(UserForm1.ListBox1.ListIndex, 2).Resize(1, 1).Value

Dim sPath As String

sPath = "C:\Documents and Settings\tccmdr\Desktop\Working Projects\Employee Database\" & Val1 & Chr(44) & Chr(32) & Val2 & Chr(32) & Chr(32) & Val3 & ".txt"

If FExist(sPath) Then

Dim RetVFile
RetVFile = Shell("C:\WINDOWS\notepad.exe C:\Documents and Settings\tccmdr\Desktop\Working Projects\Employee Database\" & Val1 & Chr(44) & Chr(32) & Val2 & Chr(32) & Chr(32) & Val3 & ".txt", 1)
Else
Unload Me
MsgBox "No file currently exists for the employee selected.", vbInformation + vbOKOnly, "File Search Results"
End If
End Sub
[/VBA]

[tccmdr]

With this Function:

[VBA]
Function FExist(PathName As String) As Boolean
Dim GAPN As Integer
On Error Resume Next
GAPN = GetAttr(PathName)
Select Case Err.Number
Case Is = 0
FExist = True
Case Else
FExist = False
End Select
On Error GoTo 0
End Function
[/VBA]

[mvidas]

[vba]'If FExist(sPath) Then
If Len(Dir(sPath)) > 0 Then[/vba]Dir() returns a blank string if the file doesn't exist, otherwise it returns the filename (no path) if it does. Saves a few lines of code