Solved: Excel Macro Copy Cell to log file.

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Squashman

Thread Starter
Trusted Advisor
Joined
Apr 4, 2003
Messages
19,786
I have been reading up on creating log files with Excel Macros.

I am trying to figure out if it is possible to have a Macro output the contents of the currently selected Cell to a text file?
 
Joined
Jul 28, 2006
Messages
1,223
Try this:

Code:
Sub WritingToTextfile()
    Const ForReading = 1, ForWriting = 2
    Dim FSO As Object, TS As Object, TextLine As String, FN As String
    
    FN = "D:\log.txt"
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set TS = FSO.OpenTextfile(FileName:=FN, IOMode:=ForWriting, Create:=True)
    TS.WriteLine "A1=" & ThisWorkbook.Sheets(1).Range("A1").Value
    TS.WriteLine "Activecell=" & ActiveCell.Value
    TS.Close
    Set TS = Nothing
    Set FSO = Nothing
End Sub
HTH
Jimmy
 

Squashman

Thread Starter
Trusted Advisor
Joined
Apr 4, 2003
Messages
19,786
Try this:

Code:
Sub WritingToTextfile()
    Const ForReading = 1, ForWriting = 2
    Dim FSO As Object, TS As Object, TextLine As String, FN As String
    
    FN = "D:\log.txt"
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set TS = FSO.OpenTextfile(FileName:=FN, IOMode:=ForWriting, Create:=True)
    TS.WriteLine "A1=" & ThisWorkbook.Sheets(1).Range("A1").Value
    TS.WriteLine "Activecell=" & ActiveCell.Value
    TS.Close
    Set TS = Nothing
    Set FSO = Nothing
End Sub
HTH
Jimmy
Thanks, that worked fine. I had to modify it a bit because all I wanted was the CELL contents.
 
Joined
Sep 4, 2003
Messages
4,912
You can just use a macro to copy the cell contents to a new workbook and then save the new workbook as a text file. It doesn't get an more simple

Regards,
Rollin
 

Squashman

Thread Starter
Trusted Advisor
Joined
Apr 4, 2003
Messages
19,786
You can just use a macro to copy the cell contents to a new workbook and then save the new workbook as a text file. It doesn't get an more simple

Regards,
Rollin
Jimmy's solution was perfect. It makes it completely automated. The cell I copy is acutally code that creates a batch file. The formula that creates the cell is quite in depth. Right now I just have it creating the batch file, but eventually I will stick in the code to create the batch file and then automatically launch it.
 
Joined
Jul 25, 2004
Messages
5,458
No, not overkill, actually less-kill than Jimmy's solution. It'll be faster and more portable, especially as you don't have to use objects and external references with it. Here is an example for a single cells value to a known text file location...

Code:
Sub SendCellToTextFile()
    Dim sCell As String, iFileNum As Integer
    Const sTextFile As String = "C:\Users\Zack\Desktop\mytext.txt"
    iFileNum = FreeFile
    Open sTextFile For Append Access Write As #iFileNum
    Print #iFileNum, Chr(10) & ActiveCell.Value & Chr(10)
    Close #iFileNum
End Sub
This will probably be 8-10 times faster than referencing FSO.
 
Joined
Jul 25, 2004
Messages
5,458
Try this:

Code:
Sub WritingToTextfile()
    Const ForReading = 1, ForWriting = 2
    Dim FSO As Object, TS As Object, TextLine As String, FN As String
    
    FN = "D:\log.txt"
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set TS = FSO.OpenTextfile(FileName:=FN, IOMode:=ForWriting, Create:=True)
    TS.WriteLine "A1=" & ThisWorkbook.Sheets(1).Range("A1").Value
    TS.WriteLine "Activecell=" & ActiveCell.Value
    TS.Close
    Set TS = Nothing
    Set FSO = Nothing
End Sub
HTH
Jimmy
Jimmy, since VBA is managed code, there is no need to set anything to Nothing when exiting, as they lose focus and will be handled (in memory via CLR) on exit. Kind of like a double negative, just repeating what is already going to happen.
 
Joined
Jul 28, 2006
Messages
1,223
To Zack

This will probably be 8-10 times faster than referencing FSO.
Your kung-fu is just terrific (y) :D I think I will learn your style.
I didn't know that using FilesystemObject to file acces was so slow, or that there was a much better way. If "FSO style" is so inferior, why is it so frequent and popular on VBAX and TSG?
I don't seem to remember ever having met the method you just presented. Maybe it's my selective eyesight..? :confused:

since VBA is managed code, there is no need to set anything to Nothing when exiting, as they lose focus and will be handled (in memory via CLR) on exit.
I hope the OP doesn't mind my digging deeper into this subject.
This remark of yours is quite intriguing. I have seen lots of such code lines (i.e. setting object variables to nothing, just before exiting a procedure) in various code examples in various forums. And from apparently expert people, too. I've had the impression that it was one of the "best practices", just like declaring variables. Now you say I've been wrong? :confused:
Or are there cases when it is recommended to set something to Nothing?

To Squashman

I'm sorry for giving you a second class solution, but it was the only one I've known until now. :eek:


Cheers to you all,

Jimmy
 
Joined
Jul 25, 2004
Messages
5,458
No problem Jimmy. First of all, your solution was definitely not "second class". It was excellent, and well put together. I'll try to line out my opinions on the items you mentioned. Where applicable I'll note what VBA would/should be doing to underline my reasonings for them. This does not mean that I am absolutely right. There are plenty of ways to "skin the cat". Hopefully my reasoning will shed some insight as to why I choose the methods I do.

Using the FileSystemObject (FSO):
This doesn't necessarily have to be a "slow" method, but calling an external reference will generally slow things down. This one isn't too bad, it's not like calling an entire application to load, but it takes time and resources, which are the two killers for any programmer. The FSO is wonderful and very powerful. I associate it to having a Ferrari motor in your lawn mower. Sometimes you just don't need all that power and overhead - or the fuel bill. ;)

Why is the FSO used so frequently?
It's popular and is found in abundance. Because it is found more readily, just means more people use it. Of course, it does have it's place, and is great for working with files and folders, even sometimes with text files. The reason I would choose to use the Open/Print/Close methods here is because of their speed and ease of use. If you tested the code then you've realized how fast they are. I must say, my original estimation was not too accurate, although there is still a dramatic time increase. Here are my test results...

Test1:
FSO: 57.4590421408307 seconds
Open/Print/Close: 27.9274466447551 seconds

Test2:
FSO: 56.616636751319 seconds
Open/Print/Close: 27.7623348777568 seconds
On Test2 I took out the "= Nothing" lines and ran it, so there was some minor time shaved off for that. For the timing I used the QueryPerformanceCounter API, here is the code...
Code:
Option Explicit

Private Type LARGE_INTEGER
    LowPart As Long
    HighPart As Long
End Type

Private Declare Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As LARGE_INTEGER) As Long
Private Declare Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As LARGE_INTEGER) As Long
Private Declare Sub CopyMemory Lib "kernel32.dll" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)

Private Sub Test_FSO()
    'KPD-Team 2001
    'URL: http://www.allapi.net/
    'E-Mail: [email protected]
    Dim T As Long, liFrequency As LARGE_INTEGER, liStart As LARGE_INTEGER, liStop As LARGE_INTEGER
    Dim cuFrequency As Currency, cuStart As Currency, cuStop As Currency
    'Retrieve the frequency of the performance counter
    If QueryPerformanceFrequency(liFrequency) = 0 Then
        MsgBox "Your hardware doesn't support a high-resolution performance counter!", vbInformation
    Else
        'convert the large integer to currency
        cuFrequency = LargeIntToCurrency(liFrequency)
        'retrieve tick count
        QueryPerformanceCounter liStart
        'do something
        For T = 0 To 100000
            Call WritingToTextfile
        Next T
        'retrieve tick count
        QueryPerformanceCounter liStop
        'convert large integers to currency's
        cuStart = LargeIntToCurrency(liStart)
        cuStop = LargeIntToCurrency(liStop)
        'calculate how many seconds passed, and show the result
        Debug.Print "FSO: " + CStr((cuStop - cuStart) / cuFrequency) + " seconds"
    End If
End Sub

Private Sub Test_OPC()
    'KPD-Team 2001
    'URL: http://www.allapi.net/
    'E-Mail: [email protected]
    Dim T As Long, liFrequency As LARGE_INTEGER, liStart As LARGE_INTEGER, liStop As LARGE_INTEGER
    Dim cuFrequency As Currency, cuStart As Currency, cuStop As Currency
    'Retrieve the frequency of the performance counter
    If QueryPerformanceFrequency(liFrequency) = 0 Then
        MsgBox "Your hardware doesn't support a high-resolution performance counter!", vbInformation
    Else
        'convert the large integer to currency
        cuFrequency = LargeIntToCurrency(liFrequency)
        'retrieve tick count
        QueryPerformanceCounter liStart
        'do something
        For T = 0 To 100000
            Call SendCellToTextFile
        Next T
        'retrieve tick count
        QueryPerformanceCounter liStop
        'convert large integers to currency's
        cuStart = LargeIntToCurrency(liStart)
        cuStop = LargeIntToCurrency(liStop)
        'calculate how many seconds passed, and show the result
        Debug.Print "Open/Print/Close: " + CStr((cuStop - cuStart) / cuFrequency) + " seconds"
    End If
End Sub

Private Function LargeIntToCurrency(liInput As LARGE_INTEGER) As Currency
    'copy 8 bytes from the large integer to an ampty currency
    CopyMemory LargeIntToCurrency, liInput, LenB(liInput)
    'adjust it
    LargeIntToCurrency = LargeIntToCurrency * 10000
End Function

Sub SendCellToTextFile()
    Dim sCell As String, iFileNum As Integer
    iFileNum = FreeFile
    Open sTextFile For Append Access Write As #iFileNum
    Print #iFileNum, Chr(10) & ActiveCell.Value & Chr(10)
    Close #iFileNum
End Sub

Sub WritingToTextfile()
    Const ForReading = 1, ForWriting = 2
    Dim FSO As Object, TS As Object, TextLine As String, FN As String
    FN = sTextFile
    Set FSO = CreateObject("Scripting.FileSystemObject")
    'IOMode: ForReading=1, ForWriting=2, ForAppending=8
    Set TS = FSO.OpenTextfile(Filename:=FN, IOMode:=8, Create:=True)
    TS.WriteLine Chr(10) & ActiveCell.Value & Chr(10)
    TS.Close
End Sub
The Open/Close method of Input/Output isn't really used too much, but for straight writing to text files, I prefer it. The FSO has so many more options, so if you're going to be doing anything else, it's probably going to benefit you to indeed use the FSO.

VBA is managed code, much to the thought of many - especially those .NET programmers. (Read here for reference: http://en.wikipedia.org/wiki/Common_Language_Runtime) Basically, the object created in VBA will be stored into CLR memory at runtime, when the routine is done with it, it will then be destroyed - which is the basis for "managed code". So this makes VBA managed code, meaning it handles all of it's components and cleans up after itself when ran. This pretty much negates the fact for ever having to manually set an object to Nothing. The only reason you should do that is if you would need to do it during runtime (i.e. you were going to re-use an object). It's not "best practices", it's just not needed. I too was once on a "clean-up mission" and set everything to Nothing, but after talking to a professor and some developers (who we all know, but I cannot say), I was educated on the issue. So the idea is good, but taken care of for us already. :)

Hope this makes sense (my ramblings). :)
 

Squashman

Thread Starter
Trusted Advisor
Joined
Apr 4, 2003
Messages
19,786
The solution Jimmy presented worked fine. We are talking about copying one cell to a file. I blinked and it was done. I looked at the link and immediately saw 50 lines of code that I didn't understand and paniced.

But I did notice on that link that you can have it prompt you for the filename. That would be something we might consider doing. How would that look in your macro?

But I think it would be great if it created the filename and saved it to the output path automatically. The directory path for the Workflow actually sits in cell E2. The output file of the macro needs to get saved into a subdirecotry called batch. The cell I copy(which is selected by the macro with Active Cell) is in Column B. The batch file could actually be named with whatever is in the Adjacent cell in Column A. So if I Select Cell B6, it would name the batch file with whatever is in Column A. But it would aslo prefix the filename with the Word String. If 01A was in Cell A6, then the output file would be called String_01A.bat and would save it in the batch diretory which is a subdirectory of what is Cell E2. I hope that makes sense.

You both have been great so far and I really appreciate the help.
 

Squashman

Thread Starter
Trusted Advisor
Joined
Apr 4, 2003
Messages
19,786
No, not overkill, actually less-kill than Jimmy's solution. It'll be faster and more portable, especially as you don't have to use objects and external references with it. Here is an example for a single cells value to a known text file location...

Code:
Sub SendCellToTextFile()
    Dim sCell As String, iFileNum As Integer
    Const sTextFile As String = "C:\Users\Zack\Desktop\mytext.txt"
    iFileNum = FreeFile
    Open sTextFile For Append Access Write As #iFileNum
    Print #iFileNum, Chr(10) & ActiveCell.Value & Chr(10)
    Close #iFileNum
End Sub
This will probably be 8-10 times faster than referencing FSO.
Something goofy with the output from your Macro. It ouputs a LineFeed then whats in the ActiveCell. Then another LineFeed. Then a Carriage Return Line Feed.
 
Joined
Jul 25, 2004
Messages
5,458
Yes, Jimmy's solution should work fine for you. Like I said, just another way to skin the cat. If you're not doing a lot of iterating to files, you probably won't even notice a difference. And if you ever want to do more with files/folders, the FSO method is probably the way to go.

As far as your data goes, "0A1" is in A and "String" would be in B? And always E2 would house the path? So you'd want "String_0A1.bat" transfered to the file for which the full name was in E2? If so...

Code:
Sub SendCellToTextFile()
    Dim sCell As String, sPath As String, iFileNum As Integer
    If Intersect(ActiveCell, Range("A:B")) Is Nothing Then
        MsgBox "You must select a cell in A or B!", vbExclamation, "ERROR!"
        Exit Sub
    End If
    If Cells(ActiveCell.Row, "B").Value = vbNullString Or Cells(ActiveCell.Row, "A").Value = vbNullString Then
        MsgBox "You need to fill out both cells!", vbExclamation, "ERROR!"
        Exit Sub
    End If
    sPath = Range("E2").Value
    sCell = Cells(ActiveCell.Row, "B").Value & "_" & Cells(ActiveCell.Row, "A").Value & ".bat"
    iFileNum = FreeFile
    Open sPath For Append Access Write As #iFileNum
    Print #iFileNum, Chr(10) & sCell
    Close #iFileNum
End Sub
The last Chr(10) wasn't really needed, just added an additional line feed. I assumed, since you didn't say, you wanted each return on a new line.
 

Squashman

Thread Starter
Trusted Advisor
Joined
Apr 4, 2003
Messages
19,786
Its one line of code for the batch file all piped together. It actually launches another program that takes cmd line arguements.

We would select a cell in Column B before launching the macro.
Column A just contains an identifying job number. So Column A would contain 01A, 02A, 03a, etc, etc... Column B has the corresponding code for the batch file that needs to launch the job for what is in Column A.

The actual path to the current working directory (we like to call it workflow) is in cell E2.
F:\ServerShare\Client\OrderNumber\Workflow

We would preceed the batch filename with the word string. It is not in any cells. string01a.bat and needs to be saved to the path in cell E2 in the subdirectory called batch.
F:\ServerShare\Client\OrderNumber\Workflow\batch.

I am really horrible with excel. Never really wanted to learn it.

So with your existing macro I can just take out those chr(10)'s and ampersands? I just want the active cell only.
Code:
Sub SendCellToTextFile()
    Dim sCell As String, iFileNum As Integer
    Const sTextFile As String = "C:\Users\Zack\Desktop\mytext.txt"
    iFileNum = FreeFile
    Open sTextFile For Append Access Write As #iFileNum
    Print #iFileNum, ActiveCell.Value
    Close #iFileNum
End Sub
How do I integrate this code below from the website that prompts me for the output filename into your small macro that you wrote above. The separator stuff can be taken out as well.
Code:
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DoTheExport
' This prompts the user for the FileName and the separtor
' character and then calls the ExportToTextFile procedure.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub DoTheExport()
    Dim FileName As Variant
    Dim Sep As String
    FileName = Application.GetSaveAsFilename(InitialFileName:=vbNullString, FileFilter:="Text Files (*.txt),*.txt")
    If FileName = False Then
        ''''''''''''''''''''''''''
        ' user cancelled, get out
        ''''''''''''''''''''''''''
        Exit Sub
    End If
    Sep = Application.InputBox("Enter a separator character.", Type:=2)
    If Sep = vbNullString Then
        ''''''''''''''''''''''''''
        ' user cancelled, get out
        ''''''''''''''''''''''''''
        Exit Sub
    End If
    Debug.Print "FileName: " & FileName, "Separator: " & Sep
    ExportToTextFile FName:=CStr(FileName), Sep:=CStr(Sep), _
       SelectionOnly:=False, AppendData:=True
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' END DoTheExport
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
I just want to present a few different solutions to my co-workers.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top