1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel Macro Copy Cell to log file.

Discussion in 'Business Applications' started by Squashman, May 15, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. Squashman

    Squashman Trusted Advisor Thread Starter

    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?
     
  2. Jimmy the Hand

    Jimmy the Hand

    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
     
  3. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
  4. Squashman

    Squashman Trusted Advisor Thread Starter

    Joined:
    Apr 4, 2003
    Messages:
    19,786
    Thanks, that worked fine. I had to modify it a bit because all I wanted was the CELL contents.
     
  5. Squashman

    Squashman Trusted Advisor Thread Starter

    Joined:
    Apr 4, 2003
    Messages:
    19,786
    I think that is kind of overkill for what I needed. I don't even know what the heck that is doing.
     
  6. Rollin_Again

    Rollin_Again

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

    Squashman Trusted Advisor Thread Starter

    Joined:
    Apr 4, 2003
    Messages:
    19,786
    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.
     
  8. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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.
     
  9. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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.
     
  10. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,223
    To Zack

    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:

    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
     
  11. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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...

    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). :)
     
  12. Squashman

    Squashman Trusted Advisor Thread Starter

    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.
     
  13. Squashman

    Squashman Trusted Advisor Thread Starter

    Joined:
    Apr 4, 2003
    Messages:
    19,786
    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.
     
  14. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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.
     
  15. Squashman

    Squashman Trusted Advisor Thread Starter

    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.
     
  16. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/712631

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice