i have a workbook that i'm working on and when hits the BeforeSav
event, i'd like to have specific cells of the file saved to anothe
excel file located on a server. i would like to be able to do thi
without opening the file on the server because the file is quite larg
and it would be rather slow for the user to save if the user has t
open the remote excel file each time just to save. i'm told i can d
this with odbc or ado but i'm not sure exactly how that works. i'v
seen some documentation for vb and for accessing ms access files but
don't know about excel files. can someone please point me to som
documentation or give me a sample? thanks

--
Message posted from http://www.ExcelForum.com

majikman wrote...

i have a workbook that i'm working on and when hits the BeforeSave
event, i'd like to have specific cells of the file saved to another
excel file located on a server. i would like to be able to do this
without opening the file on the server because the file is quite large
and it would be rather slow for the user to save if the user has to
open the remote excel file each time just to save. i'm told i can do
this with odbc or ado but i'm not sure exactly how that works.

Sure can. One thing you need to consider is that you can't query an
open workbook with ADO because of the dreaded 'ADO memory leak bug'.
The workaround is to save your Excel 'tables' to a temporary closed
workbook and query from there.

But you've provided no details. While it is possible to build a
generic sample (e.g. that uses lots of constants for you to amend to
suit your purposes, uses late binding so you don't have to set
references in your VB project, etc) and which makes a lots of
assumptions (the Excel 'tables' are worksheets arranged as a database
i.e. rows of columns with column/field headers, both the local and
server sheets have the same columns/fields in the same order, etc) it
would take a lot of work on the part of that person, especially with
the memory leak workaround involved, and it's likely the result would
be such a new experience you may bulk, or you may simply not check
back to see if your post has been answered, so that person may end up
doing a lot of work for zero appreciation.

Oh well, call me a fool:

Option Explicit

Sub UpdateRemoteWorkbook()

Dim wb As Excel.Workbook
Dim ConLocal As Object
Dim strConLocal As String
Dim strConServer As String
Dim strPathLocal As String
Dim strSql1 As String
Dim lngRowsAffected As Long

' Amend the following constants to suit
Const SHEET_NAME_LOCAL As String = "" & _
"MySheet"

Const FILENAME_LOCAL_TEMP As String = "" & _
"delete_me.xls"

Const PATH_FILENAME_SERVER As String = "" & _
"C:\Tempo\db.xls"

Const SHEET_NAME_SERVER As String = "" & _
"General"

' Do NOT amend the following constants
Const CONN_STRING_LOCAL As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<PATH<FILENAME;" & _
"Extended Properties='Excel 8.0;HDR=YES'"

Const CONN_STRING_SERVER As String = "" & _
"[Excel 8.0;" & _
"database=<PATH_FILENAME;" & _
"HDR=Yes;]"

' Build connection strings
strPathLocal = ThisWorkbook.Path & _
Application.PathSeparator

strConLocal = CONN_STRING_LOCAL
strConLocal = Replace(strConLocal, _
"<PATH", strPathLocal)
strConLocal = Replace(strConLocal, _
"<FILENAME", FILENAME_LOCAL_TEMP)

strConServer = CONN_STRING_SERVER
strConServer = Replace(strConServer, _
"<PATH_FILENAME", PATH_FILENAME_SERVER)

' Build sql statement
strSql1 = ""
strSql1 = strSql1 & "INSERT INTO " & strConServer
strSql1 = strSql1 & ".[" & SHEET_NAME_SERVER & "$]"
strSql1 = strSql1 & " SELECT * FROM [" & SHEET_NAME_LOCAL & "$]"

' Delete old instance of temp workbook
On Error Resume Next
Kill strPathLocal & FILENAME_LOCAL_TEMP
On Error GoTo 0

' Save copy of worksheet to temp workbook
Set wb = Excel.Application.Workbooks.Add()
With wb
ThisWorkbook.Worksheets(SHEET_NAME_LOCAL). _
Copy .Worksheets(1)
.SaveAs strPathLocal & FILENAME_LOCAL_TEMP
.Close
End With

' Open connection to temp workbook
Set ConLocal = CreateObject("ADODB.Connection")
With ConLocal
.ConnectionString = strConLocal
.Open
.Execute strSql1, lngRowsAffected
End With

ConLocal.Close

Debug.Print lngRowsAffected

End Sub

--