?
Solved

Excel: Worksheet count and Row count HARDCODED.

Posted on 2003-11-09
4
Medium Priority
?
694 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 200 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month9 days, 20 hours left to enroll

569 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