Solved

Excel: Worksheet count and Row count HARDCODED.

Posted on 2003-11-09
4
677 Views
Last Modified: 2008-02-01
How do I NOT hardcoded the worksheet count and row count?

Here is my code:

    Dim excApp As Excel.Application
    Dim excWrk As Excel.Workbook
    Dim excWrkNew As Excel.Workbook
    Dim h, i, j, tmpCtr As Integer
    Set excApp = New Excel.Application
    excApp.Visible = True
    Set excWrk = excApp.Workbooks.Open(txtFilename.Text)
   
    Set excWrkNew = excApp.Workbooks.Add
    Dim tmpStrBG As String
    Dim tmpStrCat As String
    Dim tmpStrIss As String
    Dim tmpStrStra As String
    Dim tmpStrAct As String
    Dim tmpStrWho As String
    Dim tmpStrDue As String
    Dim x As Integer
    tmpCtr = 0
    x = 0
    For h = 2 To 7
        tmpStrBG = excApp.Workbooks(1).Worksheets(h).Cells(6, 7) & " "
        For i = 9 To 19
            If i = 9 And h = 2 Then
                excWrkNew.ActiveSheet.Cells(i - 8, 1) = "Category"
                excWrkNew.ActiveSheet.Cells(i - 8, 2) = "Issue/Information"
                excWrkNew.ActiveSheet.Cells(i - 8, 3) = "Actions"
                excWrkNew.ActiveSheet.Cells(i - 8, 4) = "Who"
                excWrkNew.ActiveSheet.Cells(i - 8, 5) = "Status"
                excWrkNew.ActiveSheet.Cells(i - 8, 6) = "Due Date"
            ElseIf i = 9 Then
                'Do nothing
            Else
                For j = 2 To 7
                    If j = 2 Then
                        tmpStrCat = excApp.Workbooks(1).Worksheets(h).Cells(i, j) & " "
                    ElseIf j = 3 Then
                        tmpStrIss = tmpStrIss & excApp.Workbooks(1).Worksheets(h).Cells(i, j) & " "
                    ElseIf j = 4 Then
                        tmpStrStra = tmpStrStra & excApp.Workbooks(1).Worksheets(h).Cells(i, j) & " "
                    ElseIf j = 5 Then
                        tmpStrAct = excApp.Workbooks(1).Worksheets(h).Cells(i, j) & " "
                    ElseIf j = 6 Then
                        tmpStrWho = excApp.Workbooks(1).Worksheets(h).Cells(i, j) & " "
                    ElseIf j = 7 Then
                        tmpStrDue = excApp.Workbooks(1).Worksheets(h).Cells(i, j) & " "
                    End If
                Next
                If tmpStrCat <> " " Then
                    If h = 2 Then
                        If cboBMR.ListIndex = 1 Then
                            excWrkNew.ActiveSheet.Cells(i - 8, 1) = tmpStrBG & tmpStrCat
                            excWrkNew.ActiveSheet.Cells(i - 8, 2) = tmpStrIss & tmpStrStra
                        Else
                            excWrkNew.ActiveSheet.Cells(i - 8, 1) = tmpStrBG
                            excWrkNew.ActiveSheet.Cells(i - 8, 2) = tmpStrCat & tmpStrIss & tmpStrStra
                        End If
                        excWrkNew.ActiveSheet.Cells(i - 8, 3) = tmpStrAct
                        excWrkNew.ActiveSheet.Cells(i - 8, 4) = tmpStrWho
                        excWrkNew.ActiveSheet.Cells(i - 8, 5) = "Open"
                        excWrkNew.ActiveSheet.Cells(i - 8, 6) = tmpStrDue
                        x = i
                    Else
                        If cboBMR.ListIndex = 1 Then
                            excWrkNew.ActiveSheet.Cells(tmpCtr + i - 9, 1) = tmpStrBG & tmpStrCat
                            excWrkNew.ActiveSheet.Cells(tmpCtr + i - 9, 2) = tmpStrIss & tmpStrStra
                        Else
                            excWrkNew.ActiveSheet.Cells(tmpCtr + i - 9, 1) = tmpStrBG
                            excWrkNew.ActiveSheet.Cells(tmpCtr + i - 9, 2) = tmpStrCat & tmpStrIss & tmpStrStra
                        End If
                        excWrkNew.ActiveSheet.Cells(tmpCtr + i - 9, 3) = tmpStrAct
                        excWrkNew.ActiveSheet.Cells(tmpCtr + i - 9, 4) = tmpStrWho
                        excWrkNew.ActiveSheet.Cells(tmpCtr + i - 9, 5) = "Open"
                        excWrkNew.ActiveSheet.Cells(tmpCtr + i - 9, 6) = tmpStrDue
                        x = i
                    End If
                   
                    tmpStrCat = ""
                    tmpStrIss = ""
                    tmpStrStra = ""
                Else
                    tmpStrCat = ""
                    tmpStrIss = ""
                    tmpStrStra = ""
                End If
            End If
        Next
        If h = 2 Then
            tmpCtr = x - 8
        Else
            tmpCtr = tmpCtr + x - 9
        End If
    Next
   
    excWrkNew.ActiveSheet.Name = "star_upload"
    excWrkNew.SaveAs App.Path & "\" & txtSaveAs.Text
   
    Set excApp = Nothing
    Set excWrk = Nothing
    Set excWrkNew = Nothing

This is working right now as expected.  
However, 7 (worksheet count) and 19 (row count) are hardcoded here.
I don't want the two to be hardcoded values.  How do I do it?

Thanks.

0
Comment
Question by:shlikjohn
  • 2
  • 2
4 Comments
 
LVL 24

Accepted Solution

by:
R_Rajesh earned 50 total points
ID: 9712653
Hi shlikjohn,

see if this helps,

Sub rCount()
If Cells(1, 1).Value = "" Then Cells(1, 1).Value = " "
nWorksheetcount = ActiveWorkbook.Worksheets.Count
lRow = ActiveSheet.UsedRange.Rows.Count
lCol = ActiveSheet.UsedRange.Columns.Count
'could also try this
'lCol = Range("IU1").End(xlToLeft).Column
'lRow = Range("A65535").End(xlUp).Row
MsgBox nWorksheetcount
MsgBox lRow
MsgBox lCol
End Sub

0
 

Author Comment

by:shlikjohn
ID: 9712746

 ActiveSheet.UsedRange.Rows.Count does not work
0
 
LVL 24

Expert Comment

by:R_Rajesh
ID: 9712785
shlikjohn,

how about this,
lRow = Range("A65535").End(xlUp).Row
activates a65536 then counts back till the first non blank row is met and gives the row number
0
 

Author Comment

by:shlikjohn
ID: 9712927
It works!

thanks a lot
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question