Row Count of Imported Notepad File

[music_al]

Hi

I have a sub routine that prompts the user to open a file and the user then selects a NOTEPAD file. The sub routine then cleanses the file and formats certain cells.

I then want to return the number of rows in the file but the code I'm using only returns 1 when there are 230 rows in the file...

Batch_Row_Count = ActiveSheet.UsedRange.Rows.Count


Is this because it is not an Excel file even though I have opened it in Excel ?

Thank you in advance.

Al

 

[steve the fish]

Is the activesheet actually that sheet??

 

[music_al]

It is yes

 

[music_al]

Hi Steve, forget my last reply. Ive just checked and it isn't the active sheet. I just thought it would be. However, I've tried to activate it but I get an error on the line where the BatchFile.Activate.

Sub Format_Batch_File()

Dim BatchFile
BatchFile = Application.GetOpenFilename(, , "Choose the Batch File")
If BatchFile = False Then Exit Sub
Workbooks.OpenText BatchFile, DataType:=xlDelimited, other:=True, otherChar:="|", FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 5), Array _
(20, 1), Array(21, 1)), TrailingMinusNumbers:=True

'MsgBox BatchFile, vbInformation, "Batch File Name"
BatchFile.Activate
'Copy_Batch_File Macro
Batch_Row_Count = ActiveSheet.UsedRange.Rows.Count


more stuff here.....

End Sub

 

[steve the fish]

Try running this line just before you do your count:

MsgBox ActiveSheet.Name

Is it as expected? If so post the code you are using.

 

[music_al]

I checked it this way and I saw it ISNT the active sheet. I posted the code above.

 

[steve the fish]

The code you posted above will mean it is the activesheet. If you open a workbook it becomes active. A text file only has one sheet so it must be active. Put a break in the code after you open the file. Is the text file being opened correctly in excel? In this case do you see more than one row?

 

[music_al]

I too thought that as I'd opened the file it would become the Active workbook and therefore show the Active sheet.

The code below is triggered from a button in ANOTHER spreadsheet (Batch File Cleanse) and its this sheet that its seeing as the Active sheet.

Sub Format_Batch_File()

Dim BatchFile
BatchFile = Application.GetOpenFilename(, , "Choose the Batch File")
If BatchFile = False Then Exit Sub
Workbooks.OpenText BatchFile, DataType:=xlDelimited, other:=True, otherChar:="|", FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 5), Array _
(20, 1), Array(21, 1)), TrailingMinusNumbers:=True

MsgBox BatchFile, vbInformation, "Batch File Name"
Workbook(BatchFile).Activate
'Copy_Batch_File Macro
Batch_Row_Count = ActiveSheet.UsedRange.Rows.Count
MsgBox ActiveSheet.Name, vbInformation, "Sheet Name"
Range("A1:T1000").Copy 'Need to change this to the line below
'Range(Cells(1, 1), Cells(Batch_Row_Count, 20)).Select
Windows("Batch File Cleanse.xlsm").Activate

 

[steve the fish]

See what this does:

BatchFile = Application.GetOpenFilename(, , "Choose the Batch File")
If BatchFile = False Then Exit Sub

Workbooks.OpenText BatchFile, DataType:=xlDelimited, other:=True, otherChar:="|", FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 5), Array _
(20, 1), Array(21, 1)), TrailingMinusNumbers:=True

Set wb = ActiveWorkbook
Set ws = ActiveSheet
Batch_Row_Count = ws.UsedRange.Rows.Count

Range(ws.Cells(1, 1), ws.Cells(Batch_Row_Count, 20)).Copy

 

[music_al]

No that didnt work. It's still seeing the workbook that the Macro is run from as the Active workbook.