VBScript Help with Excel Script

[Tim Wardlaw]

Hey guys,
I have searched around on a bunch of sites and have not been able to find code that would suite my needs. What I am trying to do is the following. I have one script that goes out and scans all servers on our network and dumps all the results into separate tab delimited files. (Over 300 files). Now the script that I am working on now will append each file onto a master file with a little bit of other search ability options being added at the same time. My problem is writing code that will tell me what the last cell in a column is that contains data. I have found many examples but none really seem to work in my code.

For some reason the SpecialCell call does not work in my code. Gives Unable to get SpecialCells property of the Range class error.

Here is a snippet of my code:

Dim objexcel
Dim w_Name
Dim Filename
Dim FSO
Dim EOF
Dim Name
Dim ExcelLastCell
Dim LastColWithData
Dim Col
'On Error Resume Next


'This checks to see if excel is running, if not it opens it
'******SUB 1
Set objexcel = GetObject(, "Excel.Application")

If TypeName(objexcel) = "Empty" Then
    Set objexcel = createobject("Excel.application") '<--Opens Excel
End If
objexcel.Visible = True '<-- Makes it visible
'******END SUB 1



'This opens a file with the names of all the files needed.
'******SUB 2
Filename = "D:\Data\Scripts\Permission_Compile\DIR.txt"  '<--Location of list of file Names
Set FSO = CreateObject("Scripting.FileSystemObject")  '<--This loop opens the file and reads each line.
	Set EOF = FSO.OpenTextFile(Filename)	      '<--The names are used to open each file.
	Do Until EOF.AtEndOfStream
		Name = trim(EOF.ReadLine)
		
		
	objexcel.Workbooks.open("D:\Data\Scripts\Permission_Compile\Files\" & Name)  '<--Opens specific File.
	objexcel.Selection.EntireColumn.Insert  '<--Inserts a column
	w_Name = objexcel.ActiveCell.Worksheet.Name  '<--Grabs name of worksheet since it is based on server name.


	

	ExcelLastCell = objexcel.Application.ActiveCell.SpecialCells(xlLastCell)
	' Determine the last column with data in it (must also copy the top para for this to work) 
	LastColWithData = objexcel.ExcelLastCell.Column 
	Col = ExcelLastCell.Column 
	Do While objexcel.Application.CountA(objexcel.ActiveSheet.Columns(Col)) = 0 And Col <> 1 
    		Col = Col - 1 
		Loop 
	LastColWithData = Col' Column number 

	MsgBox LastColWithData,"Col Status"

'If Not objexcel.ActiveSheet.AutoFilterMode Then
'    objexcel.ActiveSheet.Range("C1:C65536").AutoFilter
'  End If
Loop
EOF.Close

Sorry if my notes are shitty, post if you need clarification with anything.

If anyone could help me out it would be much appreciated!

 

[Uraki]

This may or may not work for you in your situation, but I use the following code to set a print area dynamically and as a side-effect, it finds the last cell:

Sheets("Detail").Select         ' set print area to variable data
ActiveSheet.Range("A15").Select
Selection.End(xlDown).Select
Selection.End(xlToRight).Select
ActiveSheet.PageSetup.PrintArea = "$A$15:$H$" & ActiveCell.Row

However, this WON'T work if the data has empty cells in it, as the xlDown/xlToRight will stop when they see a blank cell. My data doesn't have blank cells in it, so this method works for me.

The "A15" is arbitrary; it just happens to be the first cell on the sheet that I want to use for the uppper left corner of the print area. Note that the "ActiveCell" at the end of the code will be the last cell on the page, so "ActiveCell.Row" & "ActiveCell.Column" will get you what you want, I think.

 

[Tim Wardlaw]

Thanks,
I'll give it a shot. My data has spaces in it, but I can sort it first and put all the spaces at the bottom.

I'll let yea know.
tw

 

[MjrStryker]

Here's what I use when I'm looking for the last used cell in a particular column of data.

LastRow = Sheets("Generic Sheet").Range("A65536").End(xlUp).Row

What it does is select the last possible cell of a range (65536) and then forces the function that Excel runs when you press the key combination End and Up Arrow. This will go to the next used cell upward from your current location. Detect what row that cell is in using the Row property and add 1 to that to get the next unused row.

This works regardless of spaces, so long as the data you're searching through has no extraneous data or labels beneath it.

 

[IanG]

Try LastRow = Sheets("Generic Sheet").usedrange.rows

This will give the number of active rows in your sheet (from the first row with any data to the last one). If you have an empty row or 2 at the top of the sheet it might not work for you.

 

[Tim Wardlaw]

Hey IanG,
Your code below seems to almost work, however, once I get to 50 lines the array stops filling with data? There are no blank lines in the data. I test it with 49 and that value comes back true, but once I put in 51+ pieces of data it returns only 50? Is this a limitation of the class?

I tried setting LastRow to: LastRow(65535) but errored out....
LastRow = Sheets("Generic Sheet").usedrange.rows

 

[Uraki]

Just tried it, this works. Just set up a macro with this and you can quickly test it as you move the "last cell" by changing the value of some cell:

Sub TestLastCell()

Cells(1, 1).Select
ActiveCell.Value = "Last Row"
Cells(2, 1).Select
ActiveCell.Value = Sheets("Sheet1").UsedRange.Rows.Count

Cells(1, 2).Select
ActiveCell.Value = "Last Col"
Cells(2, 2).Select
ActiveCell.Value = Sheets("Sheet1").UsedRange.Columns.Count

Cells(10, 1).Select
End Sub

It's the "UsedRange.Rows/Columns.COUNT" that return the actual data we are looking for.
Even if the cell has a single space in it, it will be the "last cell". If it is empty, it doesn't count.