Solved

Row Count from a closed worksheet for selected columns

Posted on 2001-06-13
16
651 Views
Last Modified: 2008-02-01

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 As Integer, intStartCol As Integer, intEndCol As Integer)

    p = "D:\Projects\STATS\"
    f = "frequention_week.xls"
    s = "Sheet1"
   
    Application.ScreenUpdating = False
 
    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 = True
   
     
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").Address(, , xlR1C1)

'   Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
End Function

=================================================================
=================================================================
0
Comment
Question by:cb168
  • 7
  • 7
  • 2
16 Comments
 
LVL 13

Expert Comment

by:cri
ID: 6185448
"...I need to be able to find the row count of my range..."

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

0
 
LVL 13

Expert Comment

by:cri
ID: 6185453
Detail: intRowCount as Long, unless you know for sure you always have less than 32,767 rows
0
 
LVL 13

Expert Comment

by:cri
ID: 6185487
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.
0
 

Author Comment

by:cb168
ID: 6185599
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

0
 
LVL 17

Expert Comment

by:calacuccia
ID: 6185818
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
0
 
LVL 13

Expert Comment

by:cri
ID: 6185983
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
0
 

Author Comment

by:cb168
ID: 6186003
Hi

Have any coded examples of this process, PLEASE.

cheers
0
 

Author Comment

by:cb168
ID: 6186298
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  
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:cb168
ID: 6186318
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  
0
 

Author Comment

by:cb168
ID: 6186333
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  
0
 

Author Comment

by:cb168
ID: 6186348
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  
0
 
LVL 13

Accepted Solution

by:
cri earned 100 total points
ID: 6186933
Problem is that GetValues returns the values hyperlink/external reference style...

Therefore I modified the GetValue function to and used the ISBLANK function. Seems to work. Test it though...


Sub testMethods()
    GetRangeValues 1, 1, 8
End Sub


Sub GetRangeValues(intStartRow As Long, intStartCol As Integer, intEndCol As Integer)
  'ATTENTION: - First parameter new meaning, remove if not necessary
 
  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 iRow = intStartRow To 65536 Step 1
      With Cells(iRow, iCol)
        retVal = GetValue(p, f, s, .Address)
        If retVal = "BlankVideNadaLeerNiente" Then
          Exit For
        Else
          .Value = retVal
        End If
      End With
    Next iRow
  Next iCol
  Application.ScreenUpdating = True
   
End Sub


Private Function GetValue(path, file, sheet, ref) As Variant
'   Retrieves a value from a closed workbook
'   NOTE: Argument building glitch corrected
   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).Address(, , xlR1C1)
   
'   Execute an XLM macros
   If ExecuteExcel4Macro("ISBLANK(" & arg & ")") = False Then
     GetValue = ExecuteExcel4Macro(arg)
   Else
     GetValue = "BlankVideNadaLeerNiente"
   End If
End Function


0
 

Author Comment

by:cb168
ID: 6189998
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.

0
 
LVL 13

Expert Comment

by:cri
ID: 6190578
Are you a teacher ? If not, see http://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
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 6193202
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
0
 
LVL 13

Expert Comment

by:cri
ID: 6194240
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
         



0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
excel VBA sending mail 9 50
EXCEL: Parse a formatted address out of a JSON Response into a new field 11 54
MS Office subscription 11 47
Microsoft Edge, Outlook OWA 7 43
Some time ago I was asked to create a VBA function that would calculate a check digit for an input number, using the following procedure: First, sum up all the individual digits in the number If that sum value has more than one digit, then sum up …
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

895 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

11 Experts available now in Live!

Get 1:1 Help Now