Switching to next worksheet

[jdarmiento]

Ok I've read every post and tried tons of code to no avail.
I have a script that Opens and existing Workbook finds the next free cell reads free disk space from a servers harddrives and places the data. Howerver easch server is on a different TAB. I need to tell excel to move to the next tab and rerun the code for the next server. All code to goto the the next sheet seems to be ignored. Here is my code.
On Error Resume Next

Const xpRangeAutoFormatList2 = 11
Const xlDescending = 2
Const xlDown = -4121




'folderinfo represents the share you want to search!

'The following Excel VBA code opens a Excel Worksheet and sets up the columns for

Vbscript data

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = Serverlog.xls
objExcel.Workbooks.Open("C:\Scripts\Serverlog.xls")
For i = 1 To Worksheets.Count
MsgBox Worksheets(i).Name
Next



objWorksheet.Cells(1,1) = 1
objWorksheet.Cells(2,1) = 2
objWorksheet.Cells(3,1) = 3
objWorksheet.Cells(4,1) = 4
objWorksheet.Cells(5,1) = 5

Set objRange = objExcel.Range("A1")
objRange.End(xlDown).Activate

intNewRow = objExcel.ActiveCell.Row + 1
strNewCell = "A" & intNewRow
objExcel.Range(strNewCell).Activate



x = 2
y = 2

'The following Vbscript firsts echos message prompt to the screen. Then runs

Vbscript code
'to locate the Drive Infromation which is then imported into the opened Excel

Spreadsheet.

'WScript.Echo
'WScript.Echo
'WScript.Echo "Process started at: " & Now
'WScript.Echo ""
'WScript.Echo ""
'WScript.Echo "Please wait. While completing your Drive Space Spreadsheet

..................."
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("c:\scripts\server.txt", 1)

Do Until objFile.AtEndOfStream
strComputer = objFile.ReadLine
Set objWMIService = GetObject("winmgmts://" & strComputer)

Set colDisks = objWMIService.ExecQuery _
("SELECT * FROM Win32_LogicalDisk WHERE DriveType = 3")
For Each objDisk In colDisks
If objDisk.Freespace < 8000000000000 Then
objExcel.ActiveCell(1, 1) = Date
objExcel.ActiveCell(1, X)= FormatNumber(((objDisk.Freespace / 1024) / 1024) /

1024,2)
x = x + 1
End If

Next
Loop

Sub
Sheets("USS_exch").Select
End Sub



'ThisExcel.Worksheets.Item("USS_EXCH").Activate
'Set objRange = objWorksheet.UsedRange
'objRange.AutoFormat(xpRangeAutoFormatList2)
'objRange.EntireColumn.Autofit()
'Set objRange5 = objExcel.Range("E1")
'objRange.Sort objRange10,,,,,,,1
'objExcel.ActiveWorkbook.Save
'objExcel.ActiveWorkbook.Close
' Quit Excel.
'objExcel.Application.Quit





'WScript.Echo
'WScript.Echo
'WScript.Echo "Your query completed at: " & Now

Any help would be greatly appreciated

 

[SkipVought]

Hi,

Howerver easch server is on a different TAB.

You have started off shooting yourself in the foot, assuming that you designed the application. You multiply problems by segmenting your data into separate sheets.

My STRONG recommendation is to recast your design into a single table, by adding a column for Server.

BTW, objWorksheet is never set in the code that you posted.

Also, I would avoid using the Activate and Select methods as it severely affects performance and is unnecessary.

How Can I Make My Code Run Faster? faq707-4105


Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}