Write Excel to Access
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.
"Vicente.'." wrote:
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.'.
|