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.'.

What methods are you using? YOu can read data from an Access Database
using q query, but you can't write the data back using a Query. I would
go to the Access VBA help and look up ADO method. YO ucan Use the Access
VBA language in Excel if you declare two references in the VBA menu

Tools - REference

Add the following by cliking the check box in the refernce menu and
then pressing OK

1) Microsoft Access 11.0 object library (or latest version on you PC)
2) Microsoft ActiveX daa objects 2.8 library (or latest version on you
PC)


here is some sample code

Sub MoveData()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set sourcesht = ThisWorkbook.Sheets("Sheet1")
Folder = "c:\Temp\"
DestFile = Folder & "Activity overview1.xls"
'excel worksheet must have dollar sign at end of name
DestShtName = "Sheet1" & "$"

With sourcesht
Person = .Range("A1")
EstWorkLoad = .Range("C4")
RealWorkLoad = .Range("C5")
WeekNum = .Range("F2")
End With

'open a connection, doesn't open the file
Set cn = New ADODB.Connection

With cn

ConnectStr = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & DestFile & ";" & _
"Mode=Share Deny None;" & _
"Extended Properties=""Excel 8.0;HDR=No;ReadOnly=False;"""

Open (ConnectStr)
End With

'open the recordset
Set rs = New ADODB.Recordset
With rs

MySQL = "SELECT * FROM [" & DestShtName & "] "

Open Source:=MySQL, _
ActiveConnection:=cn

If .EOF < True Then

RowCount = 1
Do While Not .EOF And RowCount < 14

MoveNext
RowCount = RowCount + 1
Loop

If .EOF Then
MsgBox ("Not Enough Rows - Exit macro")
End If

setLoad = ""

WorkWeekCol = 0
WorkWeek = 22
For Each Fld In rs.Fields
If Fld.Value = WorkWeek Then
'rows and columns are backwards from excel
WorkWeekCol = Range(Fld.Name).Row
Exit For
End If
Next Fld
End If

If WorkWeekCol = 0 Then
MsgBox ("Did not find WorkWeek : " & WorkWeek & ". Exiting
Macro")
Exit Sub
End If

Close

Person = "Joel"

MySQL = "SELECT *" & vbCrLf & _
"FROM [" & DestShtName & "] " & vbCrLf & _
"Where [" & DestShtName & ".F1]='" & Person & "'"

Open Source:=MySQL, _
ActiveConnection:=cn, _
LockType:=adLockOptimistic, _
CursorType:=adCmdTable

If .EOF = True Then
MsgBox ("count not find : " & Person & " Exit Macro")
Exit Sub
Else

EstWorkLoad = 123
RealWorkLoad = 456
'field start at zero, subtract one from index
Fields(WorkWeekCol - 1).Value = EstWorkLoad
Fields(WorkWeekCol).Value = RealWorkLoad
Update
End If


End With

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165269

Microsoft Office Help

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.'.

The easy way to get the column Number in excel is as follows

ColLetter = "BF"
ColNumber = Range(ColLetter & 1).Column

There are two methods getting column data in Access using a recordset
which are SQL, and Fields. The code I provided(see previous posting)
uses both methods. You can use the SQL statement to find the column
when row 1 of the database contains data and the FROM part of the SQL
will define the Table (the sheett in Excel) and the column Names. when
you don't have data in row 1 of the Access database then you can't
define the column using the BF statement and must use the fields Methods
which requires getting the number of the column like I did above.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165269

Microsoft Office Help