Copying multiple cells from Excel to Access

  • Thread starter Thread starter Wally Steadman
  • Start date Start date
W

Wally Steadman

Greetings all,
I have a database built for tracking information of which part is stored
in MS Excel by another department and I was wondering if there is a way to
be able to copy the information from the cells in Excel and when I paste
have it paste into multiple fields. Example is:

MS Excel file
A B C D
1 ONE BLUE ALPHA MALE
2 TWO GREEN BRAVO MALE
3 THREE YELLOW CHARLIE MALE
4 FOUR WHITE DELTA MALE


so when I copy the selection from A1:D4 and then I want to paste it so it
populates 16 fields in a record in the database. Is that possible? Even
programatically? Would appreciate any assistance on this one as I want to
get the DB done and rather than having folks have to copy and paste
individual cells, if there is a way they can get it to populate into a Form
in the database.

TIA

Wally Steadman
 
Programmatically, you can refer to the 16 cells as:

Dim objExcel As Object
Dim intRow As Integer
Dim intColumn As Integer

Set objExcel = CreateObject("Excel.Application")
objExcel.Application.Workbooks.Open "C:\Folder\File.xls"

With objExcel.Application.Workbooks(1)
For intRow = 1 To 4
For intColumn = 1 To 4
Debug.Print .Worksheets(1).Cells(intRow, intColumn)
Next intColumn
Next intRow
.Close SaveChanges:=False
End With

objExcel.Application.Quit
Set objExcel = Nothing
 
Doug thanks for your reply. Going to figure a way to work it in.
Unfortunately for me, the excel worksheet that the information is copied and
pasted from is very large and while the columns I am copying from will be
the same, the rows will always be different and never the last rows in the
spreadsheet. So my apologies for not clarifying that in the first post.

What happens is we gather information and put it in an excel worksheet and
then when we are about to take action on that information we have a
checklist to make sure we are doing all the steps correctly and on the
checklist we have to list some of the same information that is on the
worksheet.

Right now what I personally do is copy information from cell C239 and then
paste it in to appropriate text box on the Access form, then I go back to
D239 and repeat with the next text box in Access, and I continue this until
the information from between 8 to 16 cells is copied in to Access text boxes
on the form. Even this way saves some time for sure, I am just trying to be
as efficient as possible. So the columns in excel stay the same and the
text boxes in access are the same per record.

The excel spreadsheet contains alot of information that is not necessary for
the database. Again sorry for being unclear, I hope this post cleared it up
as to my true needs.
 
It's not clear from your post whether you need something more than what I've
already given you.

As long as you can identify where on the sheet the data is, you should be
fine.

You can always loop through every cell (as in the example), checking its
Value property until you find the appropriate place, and then proceed from
there.
 
How about if Wally's spreadsheet has a named cell. That way, he
doesn't have to find a specific row, column reference. If the cell
was named "TheTotal" it wouldn't matter where it was in the workbook.

Costa
 
That would probably work. To be honest, I've never tried automating looking
up a named cell, just creating them from Access.
 
Back
Top