export spreadsheet from excel to access table
 

I have a situation where I would like to be able to
export an Excel spreadsheet to an Access table while
still in Excel.

I have a command button on an Excel Sheet that creates
another Spreadsheet with just the data I need to put in
an Access table. In that same command button, I would
like to update an Access table with the newly created
spreadsheet.

If you understand this, is it possible?

Rick

export spreadsheet from excel to access table
 

hi,
i have need to use excel date in access. frequently.
but i just leave the data in excel. I link the excel files
as a named range to access. i can then use the
excel "tables" as an access table in querys ect. the
linked file shows up like a table in access but has the xl
icon instead of the normal table icon.
The xl tables can be added to .. just make sure the named
range is expanded to take in the new data. all this can be
done by macro.

-----Original Message-----
I have a situation where I would like to be able to
export an Excel spreadsheet to an Access table while
still in Excel.

I have a command button on an Excel Sheet that creates
another Spreadsheet with just the data I need to put in
an Access table. In that same command button, I would
like to update an Access table with the newly created
spreadsheet.

If you understand this, is it possible?

Rick
.

export spreadsheet from excel to access table
 

"Rick" wrote ...

I have a situation where I would like to be able to
export an Excel spreadsheet to an Access table while
still in Excel.

I have a command button on an Excel Sheet that creates
another Spreadsheet with just the data I need to put in
an Access table. In that same command button, I would
like to update an Access table with the newly created
spreadsheet.

If you understand this, is it possible?

Sure is:

Option Explicit

Sub UpdateRemoteDB()

Dim wb As Excel.Workbook
Dim Con As Object
Dim strConXL As String
Dim strConDB As String
Dim strPathXL As String
Dim strSql1 As String
Dim lngRowsAffected As Long

' Amend the following constants to suit
Const XL_WORKBOOK_TEMP As String = "" & _
"delete_me.xls"

Const XL_SHEET As String = "" & _
"MySheet"

Const DATABASE_PATH_FILENAME As String = "" & _
"C:\MyDatabase.mdb"

Const DATABASE_TABLE As String = "" & _
"MyTable"

' 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 = "" & _
"[database=<PATH_FILENAME;]"

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

strConXL = CONN_STRING_LOCAL
strConXL = Replace(strConXL, _
"<PATH", strPathXL)
strConXL = Replace(strConXL, _
"<FILENAME", XL_WORKBOOK_TEMP)

strConDB = CONN_STRING_SERVER
strConDB = Replace(strConDB, _
"<PATH_FILENAME", DATABASE_PATH_FILENAME)

' Build sql statement
strSql1 = ""
strSql1 = strSql1 & "INSERT INTO " & strConDB
strSql1 = strSql1 & "." & DATABASE_TABLE
strSql1 = strSql1 & " SELECT * FROM [" & XL_SHEET & "$]"

' Delete old instance of temp workbook
On Error Resume Next
Kill strPathXL & XL_WORKBOOK_TEMP
On Error GoTo 0

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

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

Debug.Print lngRowsAffected

End Sub

Jamie.

--