shlikjohn
asked on
Excel: Worksheet count and Row count HARDCODED.
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(txtF ilename.Te xt)
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).Worksh eets(h).Ce lls(6, 7) & " "
For i = 9 To 19
If i = 9 And h = 2 Then
excWrkNew.ActiveSheet.Cell s(i - 8, 1) = "Category"
excWrkNew.ActiveSheet.Cell s(i - 8, 2) = "Issue/Information"
excWrkNew.ActiveSheet.Cell s(i - 8, 3) = "Actions"
excWrkNew.ActiveSheet.Cell s(i - 8, 4) = "Who"
excWrkNew.ActiveSheet.Cell s(i - 8, 5) = "Status"
excWrkNew.ActiveSheet.Cell s(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).Worksh eets(h).Ce lls(i, j) & " "
ElseIf j = 3 Then
tmpStrIss = tmpStrIss & excApp.Workbooks(1).Worksh eets(h).Ce lls(i, j) & " "
ElseIf j = 4 Then
tmpStrStra = tmpStrStra & excApp.Workbooks(1).Worksh eets(h).Ce lls(i, j) & " "
ElseIf j = 5 Then
tmpStrAct = excApp.Workbooks(1).Worksh eets(h).Ce lls(i, j) & " "
ElseIf j = 6 Then
tmpStrWho = excApp.Workbooks(1).Worksh eets(h).Ce lls(i, j) & " "
ElseIf j = 7 Then
tmpStrDue = excApp.Workbooks(1).Worksh eets(h).Ce lls(i, j) & " "
End If
Next
If tmpStrCat <> " " Then
If h = 2 Then
If cboBMR.ListIndex = 1 Then
excWrkNew.ActiveSheet.Cell s(i - 8, 1) = tmpStrBG & tmpStrCat
excWrkNew.ActiveSheet.Cell s(i - 8, 2) = tmpStrIss & tmpStrStra
Else
excWrkNew.ActiveSheet.Cell s(i - 8, 1) = tmpStrBG
excWrkNew.ActiveSheet.Cell s(i - 8, 2) = tmpStrCat & tmpStrIss & tmpStrStra
End If
excWrkNew.ActiveSheet.Cell s(i - 8, 3) = tmpStrAct
excWrkNew.ActiveSheet.Cell s(i - 8, 4) = tmpStrWho
excWrkNew.ActiveSheet.Cell s(i - 8, 5) = "Open"
excWrkNew.ActiveSheet.Cell s(i - 8, 6) = tmpStrDue
x = i
Else
If cboBMR.ListIndex = 1 Then
excWrkNew.ActiveSheet.Cell s(tmpCtr + i - 9, 1) = tmpStrBG & tmpStrCat
excWrkNew.ActiveSheet.Cell s(tmpCtr + i - 9, 2) = tmpStrIss & tmpStrStra
Else
excWrkNew.ActiveSheet.Cell s(tmpCtr + i - 9, 1) = tmpStrBG
excWrkNew.ActiveSheet.Cell s(tmpCtr + i - 9, 2) = tmpStrCat & tmpStrIss & tmpStrStra
End If
excWrkNew.ActiveSheet.Cell s(tmpCtr + i - 9, 3) = tmpStrAct
excWrkNew.ActiveSheet.Cell s(tmpCtr + i - 9, 4) = tmpStrWho
excWrkNew.ActiveSheet.Cell s(tmpCtr + i - 9, 5) = "Open"
excWrkNew.ActiveSheet.Cell s(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.
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(txtF
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).Worksh
For i = 9 To 19
If i = 9 And h = 2 Then
excWrkNew.ActiveSheet.Cell
excWrkNew.ActiveSheet.Cell
excWrkNew.ActiveSheet.Cell
excWrkNew.ActiveSheet.Cell
excWrkNew.ActiveSheet.Cell
excWrkNew.ActiveSheet.Cell
ElseIf i = 9 Then
'Do nothing
Else
For j = 2 To 7
If j = 2 Then
tmpStrCat = excApp.Workbooks(1).Worksh
ElseIf j = 3 Then
tmpStrIss = tmpStrIss & excApp.Workbooks(1).Worksh
ElseIf j = 4 Then
tmpStrStra = tmpStrStra & excApp.Workbooks(1).Worksh
ElseIf j = 5 Then
tmpStrAct = excApp.Workbooks(1).Worksh
ElseIf j = 6 Then
tmpStrWho = excApp.Workbooks(1).Worksh
ElseIf j = 7 Then
tmpStrDue = excApp.Workbooks(1).Worksh
End If
Next
If tmpStrCat <> " " Then
If h = 2 Then
If cboBMR.ListIndex = 1 Then
excWrkNew.ActiveSheet.Cell
excWrkNew.ActiveSheet.Cell
Else
excWrkNew.ActiveSheet.Cell
excWrkNew.ActiveSheet.Cell
End If
excWrkNew.ActiveSheet.Cell
excWrkNew.ActiveSheet.Cell
excWrkNew.ActiveSheet.Cell
excWrkNew.ActiveSheet.Cell
x = i
Else
If cboBMR.ListIndex = 1 Then
excWrkNew.ActiveSheet.Cell
excWrkNew.ActiveSheet.Cell
Else
excWrkNew.ActiveSheet.Cell
excWrkNew.ActiveSheet.Cell
End If
excWrkNew.ActiveSheet.Cell
excWrkNew.ActiveSheet.Cell
excWrkNew.ActiveSheet.Cell
excWrkNew.ActiveSheet.Cell
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
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
how about this,
lRow = Range("A65535").End(xlUp).
activates a65536 then counts back till the first non blank row is met and gives the row number
ASKER
It works!
thanks a lot
thanks a lot
ASKER
ActiveSheet.UsedRange.Rows