Macro to access/edit/save a text file

I have an xl doc in which one of the sheet's column A changes value every 1 hour...

What I would like to know is.. if there is a method in which i can copy these values from column A to a text file every hour...

The range of cells containing values in Column A also varies every hour.

Also, the old values in the text file needs to get deleted before the new values are updated every hour.

Thanks in advance

Thanks for the quick response....

I tried running this, but there seems to be a small problem... All the values in the Column A gets transferred into the txt file. After this... a empty value also gets saved onto the txt file. Is there anyway to avoid this ?

Hello Chintu Raju,

If the last cell in column "A" contains one or spaces, the macro will consider this to be a non empty cell. Check the worksheet to see if there are any spaces in the line below the last one with visible data.

Sincerely,
Leith Ross

I rechecked this but there seems to be no space character anywhere on the sheet. But still the extra empty line comes up on the text file.

Paste the code onto the sheet module

Private Sub Worksheet_Calculate()
Dim fn As String
fn = ThisWorkbook.Path & "\" & Me.Name & Format$(Time,"hh-mm-ss") & ".text"
With Range("a1", Range("a" & Rows.Count).End(xlUp))
    Open fn For Output As #1
        Print #1, Join(Evaluate("transpose(" & .Address & ")"), vbCrLf)
    Close #1
End With
End Sub

I am sorry to say ... I didnt get you completely.. Copy this code and then ?

Originally Posted by Chintu Raju

I am sorry to say ... I didnt get you completely.. Copy this code and then ?

1) right click on sheet tab - [ViewCode]
2) paste the code onto the right pane
3) click x to close the window

It will create a text file according to the change made through file import...
So it doesn't need to have OnTime method.

Aren't you updating Col.A through importing data from other file ? (query)

Unfortunately I will have to use the Ontime method coz there are few calculations which happens on this Sheet (takes about 2 minutes for the final result), and then the final values update in Column A... But as you said if any value change in the Column A leads to making the text file, it would probably create it all the time when the calculation happens.

Does it ?

If you need OnTime method anyway then

Private myTime As Double

Sub test()
Dim fn As String
fn = ThisWorkbook.Path & "\" & Me.Name & Format$(Time,"hh-mm-ss") & ".text"
With Range("a1", Range("a" & Rows.Count).End(xlUp))
    Open fn For Output As #1
        Print #1, Join(Evaluate("transpose(" & .Address & ")"), vbCrLf)
    Close #1
End With
myTime = Now + TimeValue("01:00:00")
Application.OnTime myTime, "test"
End Sub

Sub Finish()
Application.OnTime myTime, "test", ,False
End sub

I know this appears to be an old thread, I'd like to revive it because when I tried this, I get an error.

I can set a key combo to activate the CreateTextFile sub, and the file is created/overwritten.

I changed the line TimeSerial(1,0,0) to (0,1,0) so it runs every minute. Which it does.

But when I run the sub RunSaveToFile, I get an error every minute.

"Cannot run the macro "pathto.xlsm'!CreateTextFile'. The macro may not be available in the workbook or all macros may be disabled."

I looked this error up online, and found how to enable all macros, as well as a macro patch for excel. Neither have resolved the problem. So now I ask you!

When run manually, the CreateTextFile sub executes as advertised. It's when the timer executes the sub that the error occurs.

Thanks in advance,
Jon