to write to an excel file when it is closed you use theh ADO method.
All microsoft office prodcuts have the same file structure which
consitst of sheets (or table in access which are the same), documents,
slides, charts, pictures, ..., etc. the only real difference between an
excel xls file and an access mdb file is the file extension. So you can
read and write an xls file exactly like you would an access mdb file.

I have written to databases using ADO before but the syntax is
extremely sensitive and can take me hour to get on estatement correct.
This was the first time I tried using an excel workbook and it took me
literaly the whole day.

the SQL statement to open the work sheet required a dollar sign at the
end of the sheet name and required the sheet name to be in square
brackets. Some examples I had backward single quote which didn't work.
I tried all combinations of nobrackets, brackets, dollar signs, forward
single quotes, and backards single quotes before I got it to work. The
syntac isn't the same whan you work with a mdb file and an xls file.

Another problem I had was cuasd by the way your worksheet is organized.
Usually, ADO method is easier because the 1st row of the worksheet is
the names of the columns when working with databases. Since I didn't
know the names of the colunms (row 1 data) I had to use the column
Number to get the data which using ADO if the Fields.

The next problem I have is the code didn't work unless I had data in
Cell A1. I then put numbers in colunm A to help me diagnosis why the
code wasn't working. Doing this I never found Joel in cell A26. When
using ADO method you can't just specify go to row 14. Instead you have
to move the recordset forward 13 times from row 1. I put numbers in
column A from 1 to 25 and put my name Joel in Row 26. I reached the End
of file before I got to row 26. I think the code thought the format of
column A was number and didn't like that a string was also in the
column.

I then had to use an SQL statement to find "Joel" in column A. This
took hours to get the sytac correct. Finally I got the correct row and
columns, but the data never got saved. I had the wrong parameters in
the Recordset Open statement and the file was in a read only mode.

Warning, This code may not work with your data. I added some message
boxes to help you isolate any problems.


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=163315

Microsoft Office Help