Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel worksheet export into access table | Excel Worksheet Functions | |||
Can you export a pivot table into Access? | Excel Discussion (Misc queries) | |||
Is there any way to export a .xls table to Access .mdb without Acc | Excel Discussion (Misc queries) | |||
vb code to export data into existing Access table | Excel Programming | |||
Export Data to Access Table | Excel Programming |