I need to do the following.

I want to copy cells from a number of Excel files, say "1.xls",
"2.xls", "3.xls", etc, into successive rows of "All.xls".  That is, I'd
open "1.xls", copy cells and paste them into row 1 of "All.xls", then
open "2.xls", copy cells and paste them into row 2 of "All.xls", and so
on.  I figure I can do this with some sort of For loop, but can't quite
figure out how.  What I need to know is a way of pointing to the
correct cells in "All.xls".  I thought about naming the row as a number
"i" and then doing something like...

..Range("Ai").Select()
..ActiveSheet.Paste()

But that doesn't work obviously because the cell has to be a string.  I
could dimension the cell as a string like this...

Dim Cell as String = "A" & i

But I have many more columns than just column A (I currently am up to
column EZ) and would have to dimension a huge number of variables.  Is
there a better way to do this automatically?

For starters you should fire up excel and use it's macro recorder. This
will generate the "base code", which you should then modify to your
specifications.

Also, you can specify a start and end cell using "Range()"

i.e.

..Range("A1:EZ500").Select

....or something like that.

Thanks,

Seth Rowe


bruxerjk wrote:
Show quoteHide quote

It was far simpler than I thought....

        For i = 1 To n
                .Range("A" & i.ToString).Select
        Next i

But what if I wanted to point to columns?  Any way of making a for loop
that goes through the letters of the alphabet?  That is, something
like..

        For i = A To Z
                .Range("i" & row#).Select
        Next i




rowe_newsgroups wrote:
Show quoteHide quote

One possib is that you can use the Ascii codes... to loop, as follows:

    For i As Integer = Asc("A") To Asc("Z")
        .Range(Chr(i) & rowNum.ToString()).Select()
    Next



<jacob_bru***@hotmail.com> wrote in message
Show quoteHide quote

This is the best way if you don't need to go past column Z. To handle
that you could build a function that will return a column letter based
on the value of i. The function I wrote for a vb6  program just had a
Select Case statement that examined the range an integer was in, added
the neccesary amount to get it to correspond to a letter's ascii value,
and then returned the chr(...) string - nothing to complex. Then the
for loop turns into this:

    For i as integer = 1 to 256 ' (or whatever the max column count is)
        .Range(GetColumn(i) & rowNum.ToString()).Select()
    Next

I'll post the function's code tomorrow - it's on my work computer.

Thanks,

Seth Rowe


Arthur Dent wrote:
Show quoteHide quote

Here's the code, nothing complex just a little math:

Thanks,

Seth Rowe

    Private Function GetColumn(ByVal FieldNumber As Integer) As String
        On Error GoTo Err_Handler
        ' This Function converts the FieldNumber into an Excel Lettered
Column
        ' Given a range it adds/subtracts a set integer to the
FieldNumber
        ' Creating the ASCII Number representing the desired column,
then converts
        ' The ASCII Number back to text using Chr and returns that
value
        Select Case FieldNumber
            ' Columns A to Z
            Case 0 To 25
                GetColumn = Chr(FieldNumber + 97)
                ' Column AA to AZ
            Case 26 To 51
                GetColumn = "A" & Chr(FieldNumber + 71)
                ' Column BA to BZ
            Case 52 To 77
                GetColumn = "B" & Chr(FieldNumber + 45)
                ' Column CA to CZ
            Case 78 To 103
                GetColumn = "C" & Chr(FieldNumber + 19)
                ' Column DA to DZ
            Case 104 To 129
                GetColumn = "D" & Chr(FieldNumber - 7)
                ' Column EA to EZ
            Case 130 To 155
                GetColumn = "E" & Chr(FieldNumber - 33)
                ' Column FA to FZ
            Case 156 To 181
                GetColumn = "F" & Chr(FieldNumber - 59)
                ' Column GA to GZ
            Case 182 To 207
                GetColumn = "G" & Chr(FieldNumber - 85)
                ' Column HA to HZ
            Case 208 To 233
                GetColumn = "H" & Chr(FieldNumber - 111)
                ' Column IA to IV (Max Excel Column)
            Case 234 To 255
                GetColumn = "I" & Chr(FieldNumber - 137)
                ' Table has to many columns raise error
            Case Else
                MsgBox("The query returned more columns than could be
inputted into Excel." & vbCr & vbCr & _
                       "Please narrow down the query and try again.", ,
_
                       "To many columns.")
                ' Use the following setting in the calling sub to trap
this error
                GetColumn = "-1"
        End Select

    Exit_Handler:
        Exit Function
    Err_Handler:
        MsgBox(Err.Description)
        Resume Next
    End Function
rowe_newsgroups wrote:
Show quoteHide quote

some general mouse events should be there
newbie question
"Optional" reference?
Convert C# "writer.Write(@" to VB ?
VB.net 2003/2005- Can't figure out how to test for working internet connection
Property Grid
ASP web app
VS 2005 nightmares
Adding a control programmatically...
Newbie Add Row Question