Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel: Worksheet count and Row count HARDCODED.

Posted on 2003-11-09
4
Medium Priority
?
691 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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…
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…
Suggested Courses

596 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