Copying cells to successive rows in Excel

[bruxerjk]

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?

 

[rowe_newsgroups]

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

 

[jacob_bruxer]

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

 

[Arthur Dent]

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

 

[rowe_newsgroups]

For i As Integer = Asc("A") To Asc("Z")

.Range(Chr(i) & rowNum.ToString()).Select()
Next

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

 

[rowe_newsgroups]

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