Write Excel to Access

  • Thread starter Thread starter Vicente.'.
  • Start date Start date
V

Vicente.'.

I need to write data from an Excel worksheet to a Access Data Base, data from
column "B" to column "BF" for every line.
How can I get the columns behind the "Z" column??? (When they became "AA"
and so on ...)
I´ll realy apreciate some sugestions.

Best regards,

Vicente Basso.'.
 
Hi,
I guess your problem might be about to conversion between colume letter and
numbers so you can do looping. Below are 2 functions do the work.

========= start coding

Function LetterToNumber(Letter As String) As Long
LetterToNumber = Cells(1, Letter).Column
End Function

Function ColChar(i As Long) As String
ColChar = IIf(i > 26, Chr((i - 1) \ 26 + 64), "") & _
Chr((i - 1) Mod 26 + 65)
End Function

========= end coding


There are quite a number of ways to achive the result, for example:

1 iterate each cell content and write a SQL to insert it. this is the most
primitive way but becareful about the date/time formatting and those string
containing " " ".

So you constricut each SQL statement of INSERT then execute it using the ADO
connection object.


2 Make Excel data into a columne-to-colume look like format then set it as a
range and put it as a datasource name (DSN) then from Access use ADO
copyrecordset method to directly paste to Access table.
 
Back
Top