Solved

Excel: Worksheet count and Row count HARDCODED.

Posted on 2003-11-09
4
668 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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 process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now