cb168
asked on
Row Count from a closed worksheet for selected columns
When I select a file I want to read the 2- 6 column.
I call the following procedure.
VBA code examples please.
Thanks in advance.
GetRangeValues 95, 2, 6
The first argument is the row count.
The second argument is the start column.
The third argument is the end column.
I need to be able to find the row count of my range from say
column 2 til 6, column 8 til 10, column 29 til 33,
column 35 til 39 in VBA CODE.
e.g.
Sub testMethods()
'works fine
GetRangeValues 95, 2, 6
'works fine
GetRangeValues 95, 8, 10
'works fine
GetRangeValues 75, 29, 33
'works fine
GetRangeValues 75, 35, 39
End Sub
==========================
==========================
Sub GetRangeValues(intRowCount
p = "D:\Projects\STATS\"
f = "frequention_week.xls"
s = "Sheet1"
Application.ScreenUpdating
For r = 1 To intRowCount
For c = intStartCol To intEndCol
a = Cells(r, c).Address
Cells(r, c) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating
End Sub
==========================
==========================
Private Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String
' Make sure the file exists
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If
' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Add
' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function
==========================
==========================
Detail: intRowCount as Long, unless you know for sure you always have less than 32,767 rows
Is using an auxiliary worksheet i.e. create a 'mirror sheet' with external references an option ? If the path is not given/constant, either use INDIRECT (slow) or write the required links with VBA. Kind of messy, but then you have the full command set.
ASKER
Yeah I mean the rows actually filled in the closed source workbook.
There are not empty cells within the used range?
Could you provide a code example of the workaround.
Cheers
There are not empty cells within the used range?
Could you provide a code example of the workaround.
Cheers
The best way is probably to open the workbook on the background, and to count the last row directly in the other workbook.
I don't see a good method which will allow you to get the last row of each specified column without opening the workbook on the background.
calacuccia
I don't see a good method which will allow you to get the last row of each specified column without opening the workbook on the background.
calacuccia
You could use this IF you are absolutely sure there can be no 0 (zeros) in your data OR you can provide an unique stopword.
Sub GetRangeValues(intStartRow As Long, intStartCol As Integer, intEndCol As Integer)
'ATTENTION: - First parameter new meaning, adapt if not necessary
' - Procedure will fail if there are legitimate zero values
Dim iCol As Integer, iRow As Long
Dim retVal As Variant
Dim p As String, f As String, s As String
p = "D:\Projects\STATS\"
f = "frequention_week.xls"
s = "Sheet1"
Application.ScreenUpdating = False
For iCol = intStartCol To intEndCol
For r = intStartRow To 65536 Step 1
With Cells(iRow, iCol)
retVal = GetValue(p, f, s, .Address)
'If IsEmpty(retVal) Then 'according help, but does not work, sigh...
'If reVal = "" Then 'does not work either
If retVal = 0 Then 'premature exit if legitimate zero values...
Exit For
Else
.Value = retVal
End If
End With
Next iRow
Next iCol
Application.ScreenUpdating = True
End Sub
Sub GetRangeValues(intStartRow
'ATTENTION: - First parameter new meaning, adapt if not necessary
' - Procedure will fail if there are legitimate zero values
Dim iCol As Integer, iRow As Long
Dim retVal As Variant
Dim p As String, f As String, s As String
p = "D:\Projects\STATS\"
f = "frequention_week.xls"
s = "Sheet1"
Application.ScreenUpdating
For iCol = intStartCol To intEndCol
For r = intStartRow To 65536 Step 1
With Cells(iRow, iCol)
retVal = GetValue(p, f, s, .Address)
'If IsEmpty(retVal) Then 'according help, but does not work, sigh...
'If reVal = "" Then 'does not work either
If retVal = 0 Then 'premature exit if legitimate zero values...
Exit For
Else
.Value = retVal
End If
End With
Next iRow
Next iCol
Application.ScreenUpdating
End Sub
ASKER
Hi
Have any coded examples of this process, PLEASE.
cheers
Have any coded examples of this process, PLEASE.
cheers
ASKER
This code works fine except that I have 0 values
in file.
If retVal = 0 Then
Exit For
Else
.Value = retVal
End If
What can I do to make sure that 0 values are copied
across. Then the points are yours
in file.
If retVal = 0 Then
Exit For
Else
.Value = retVal
End If
What can I do to make sure that 0 values are copied
across. Then the points are yours
ASKER
This code works fine except that I have 0 values
in file.
If retVal = 0 Then
Exit For
Else
.Value = retVal
End If
What can I do to make sure that 0 values are copied
across. Then the points are yours
in file.
If retVal = 0 Then
Exit For
Else
.Value = retVal
End If
What can I do to make sure that 0 values are copied
across. Then the points are yours
ASKER
This code works fine except that I have 0 values
in file.
If retVal = 0 Then
Exit For
Else
.Value = retVal
End If
What can I do to make sure that 0 values are copied
across. Then the points are yours
in file.
If retVal = 0 Then
Exit For
Else
.Value = retVal
End If
What can I do to make sure that 0 values are copied
across. Then the points are yours
ASKER
This code works fine except that I have 0 values
in file.
If retVal = 0 Then
Exit For
Else
.Value = retVal
End If
What can I do to make sure that 0 values are copied
across. Then the points are yours
in file.
If retVal = 0 Then
Exit For
Else
.Value = retVal
End If
What can I do to make sure that 0 values are copied
across. Then the points are yours
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks you very much CRI.
Tried and tested solution to my problem.
One last question ExecuteExcel4Macro for xl2000.
Is there a more updated method that can be used.
Tried and tested solution to my problem.
One last question ExecuteExcel4Macro for xl2000.
Is there a more updated method that can be used.
Are you a teacher ? If not, see https://www.experts-exchange.com/jsp/cmtyQuestAnswer.jsp#3
As for the 'more updated' method:
a) Why can this workbook (or a copy of it) not be opened ?
b) Does the source have to be an Excel workbook ?
c) See ExcelHelp|Find: Ways retrieve data external database
As for the 'more updated' method:
a) Why can this workbook (or a copy of it) not be opened ?
b) Does the source have to be an Excel workbook ?
c) See ExcelHelp|Find: Ways retrieve data external database
I do agree that the terrific support cri gave you certainly deserves an A. In fact, I have rarely seen responses who deserve an A as much as this assistance here did. Consider that we experts are not paid, and are doing this on voluntary basis. Besides the points, which buys us nothing, but a ranking here, we do this for the satisfaction of being rewarded with a good grade. That's why you better think twice before assigning B. If I had to explore my comment, about opening another workbook, I would agree that I only responded to your question with a non-desired workaround, and I would probably have deserved a B then, but in a case like this, where cri found an ondocumented way to get the information you want in the way you want, I honestly cannot see the need to grade as you did.
calacuccia
calacuccia
calacuccia, thanks for the support. I did not take it personal, cb168 never ever gave an A. And s/he is correct, using 'BlankVideNadaLeerNiente' is not clean programming...
Therefore modify
GetValue = "BlankVideNadaLeerNiente"
to
GetValue=Empty
If retVal = "BlankVideNadaLeerNiente" Then
to
If retVal = Empty Then
Therefore modify
GetValue = "BlankVideNadaLeerNiente"
to
GetValue=Empty
If retVal = "BlankVideNadaLeerNiente" Then
to
If retVal = Empty Then
Do you mean how find out many rows are actually filled in the closed source workbook ? This probably is not possible, getting the value is already a trick relying on a Excel 5 macro. A work-around could be devised if you know for sure that there are no empty cells within the UsedRange (or a given column) of the source worksheet or if you can provide a stop word to exit a loop