Hello

Writing a little macro, got partway, now I need this bit:

I've got a worksheet (one of many in the workbook) called Adjust with formulae in A1:J2000 but the formulae are such that only a certain number of rows have values in them and that will vary. So, for example, today only A1:J1082 have values in them, next week it could be A1:J1200 or A1:J1010 (the cells with values will always be in one 'block'). I want to copy all the rows (from column A to J) with values in them, open Notepad, paste the values into the Notepad file and Save As C:\...ABC.txt - but C:\...ABC.txt will already exist and I'll want to discard it and replace it with the new version.

Thanks.

robzrob wrote:

Writing a little macro, got partway, now I need this bit:

I've got a worksheet (one of many in the workbook) called Adjust with
formulae in A1:J2000 but the formulae are such that only a certain
number of rows have values in them and that will vary. So, for example,
today only A1:J1082 have values in them, next week it could be A1:J1200
or A1:J1010 (the cells with values will always be in one 'block'). I
want to copy all the rows (from column A to J) with values in them, open
Notepad, paste the values into the Notepad file and Save As
C:\...ABC.txt - but C:\...ABC.txt will already exist and I'll want to
discard it and replace it with the new version.

No need to involve notepad; VBA can handle text files quite easily. This
stops with the first all-blank line:

Sub writeNonBlanksToText()
Const OUTPUTFILENAME = "C:\ABC.txt"
Dim cell As Range, ro As Range, outP As String
fnum = FreeFile
Open OUTPUTFILENAME For Output As fnum
For Each ro In Range("A1:J" & Cells.End(xlDown).Row).Rows
outP = ""
allBlank = True
For Each cell In ro.Cells
If cell.Column < 1 Then outP = outP & vbTab
outP = outP & cell.Value
If Len(Trim$(cell.Value)) Then allBlank = False
Next
If allBlank Then Exit For
Print #fnum, outP
Next
Close fnum
End Sub

--
What's a little aggravated assault amongst proper European scientists?

On Monday, July 2, 2012 5:53:11 AM UTC+1, Auric__ wrote:
robzrob wrote:

Writing a little macro, got partway, now I need this bit:

I've got a worksheet (one of many in the workbook) called Adjust with
formulae in A1:J2000 but the formulae are such that only a certain
number of rows have values in them and that will vary. So, for example,
today only A1:J1082 have values in them, next week it could be A1:J1200
or A1:J1010 (the cells with values will always be in one 'block'). I
want to copy all the rows (from column A to J) with values in them, open
Notepad, paste the values into the Notepad file and Save As
C:\...ABC.txt - but C:\...ABC.txt will already exist and I'll want to
discard it and replace it with the new version.

No need to involve notepad; VBA can handle text files quite easily. This
stops with the first all-blank line:

Sub writeNonBlanksToText()
Const OUTPUTFILENAME = "C:\ABC.txt"
Dim cell As Range, ro As Range, outP As String
fnum = FreeFile
Open OUTPUTFILENAME For Output As fnum
For Each ro In Range("A1:J" & Cells.End(xlDown).Row).Rows
outP = ""
allBlank = True
For Each cell In ro.Cells
If cell.Column < 1 Then outP = outP & vbTab
outP = outP & cell.Value
If Len(Trim$(cell.Value)) Then allBlank = False
Next
If allBlank Then Exit For
Print #fnum, outP
Next
Close fnum
End Sub

--
What's a little aggravated assault amongst proper European scientists?


Wonderful. Thanks very much.