Copying cells to successive rows in Excel

  • Thread starter Thread starter bruxerjk
  • Start date Start date
B

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?
 
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
 
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
 
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
 
For i As Integer = Asc("A") To Asc("Z")
.Range(Chr(i) & rowNum.ToString()).Select()
Next
Click to expand...

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
 
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
 
Back
Top