Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.access.macros,microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Can I "Save As..." an Excel Sheet to an Access Table?
Hi,
I want to "push" data from an Excel report to an Access database. Do you know if there is a VBA script that saves an Excel sheet as an Access table? Do you know if there is any other way to "push" data from an Excel report to an Access database. The Excel report is different every day, the Access is the same and keeps the history. John |
#2
Posted to microsoft.public.access.macros,microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Can I "Save As..." an Excel Sheet to an Access Table?
Hi
You can open an Excel sheet in Access. With that in mind, I would suggest writing an append query in Access to add the details from the Excel sheet to the existing table. -- Andy. "John Bixtis" wrote in message ... Hi, I want to "push" data from an Excel report to an Access database. Do you know if there is a VBA script that saves an Excel sheet as an Access table? Do you know if there is any other way to "push" data from an Excel report to an Access database. The Excel report is different every day, the Access is the same and keeps the history. John |
#3
Posted to microsoft.public.access.macros,microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Can I "Save As..." an Excel Sheet to an Access Table?
The Excel file has a different name every day, so the Access query won't
work. The "Save as..." Access table would be my best solution I think. John "Andy B" wrote in message ... Hi You can open an Excel sheet in Access. With that in mind, I would suggest writing an append query in Access to add the details from the Excel sheet to the existing table. -- Andy. "John Bixtis" wrote in message ... Hi, I want to "push" data from an Excel report to an Access database. Do you know if there is a VBA script that saves an Excel sheet as an Access table? Do you know if there is any other way to "push" data from an Excel report to an Access database. The Excel report is different every day, the Access is the same and keeps the history. John |
#4
Posted to microsoft.public.access.macros,microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Can I "Save As..." an Excel Sheet to an Access Table?
OK. Point taken! But even if you get a script to save the sheet as a table,
you're still going to have to run an append query to get the data in. I would have thought it would be easeir to do a Save As each day on your sheet to save a copy as the name that your append query uses. -- Andy. "John Bixtis" wrote in message ... The Excel file has a different name every day, so the Access query won't work. The "Save as..." Access table would be my best solution I think. John "Andy B" wrote in message ... Hi You can open an Excel sheet in Access. With that in mind, I would suggest writing an append query in Access to add the details from the Excel sheet to the existing table. -- Andy. "John Bixtis" wrote in message ... Hi, I want to "push" data from an Excel report to an Access database. Do you know if there is a VBA script that saves an Excel sheet as an Access table? Do you know if there is any other way to "push" data from an Excel report to an Access database. The Excel report is different every day, the Access is the same and keeps the history. John |
#5
Posted to microsoft.public.access.macros,microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Can I "Save As..." an Excel Sheet to an Access Table?
John,
Save the new Excel file to the same path & name each day, with Access linked to this file. NickHK "John Bixtis" wrote in message ... The Excel file has a different name every day, so the Access query won't work. The "Save as..." Access table would be my best solution I think. John "Andy B" wrote in message ... Hi You can open an Excel sheet in Access. With that in mind, I would suggest writing an append query in Access to add the details from the Excel sheet to the existing table. -- Andy. "John Bixtis" wrote in message ... Hi, I want to "push" data from an Excel report to an Access database. Do you know if there is a VBA script that saves an Excel sheet as an Access table? Do you know if there is any other way to "push" data from an Excel report to an Access database. The Excel report is different every day, the Access is the same and keeps the history. John |
#6
Posted to microsoft.public.access.macros,microsoft.public.excel.programming
|
|||
|
|||
Can I "Save As..." an Excel Sheet to an Access Table?
John,
Are you familiar with ADO? It works fine for me, though quite complex. Basically you open a connection to your Access db, then open a recordset, then populate the recordset with your Excel data, then update the Access table with the recordset. Regards, Ian "John Bixtis" wrote: Hi, I want to "push" data from an Excel report to an Access database. Do you know if there is a VBA script that saves an Excel sheet as an Access table? Do you know if there is any other way to "push" data from an Excel report to an Access database. The Excel report is different every day, the Access is the same and keeps the history. John |
#7
Posted to microsoft.public.access.macros,microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Can I "Save As..." an Excel Sheet to an Access Table?
You may find it easier to take the advice of the MS Access users and
perform an import or 'append query' (I think they mean INSERT INTO) in MS Access. However, it is possible to perform the INSERT INTO from the Excel workbook. You must take care to avoid the memory leak bug that occurs when one queries an open workbook. The example code below avoids this problem by saving the worksheet to a temporary workbook and querying the (closed) temp workbook: Option Explicit Sub UpdateRemoteWorkbook() Dim wb As Excel.Workbook Dim ConLocal 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 ConLocal = CreateObject("ADODB.Connection") With ConLocal .ConnectionString = strConXL .Open .Execute strSql1, lngRowsAffected End With ConLocal.Close Debug.Print lngRowsAffected End Sub Jamie. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting "Save As" adds "Copy of" to file name- MS Excel 2007 | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
In Excel 2003 is there a way to prevent "Save As" and "Print"? | Excel Discussion (Misc queries) | |||
"Save" and "Save As" options greyed out - "Save as Webpage" option | Excel Discussion (Misc queries) | |||
Copy recordset from an Access "make table" query | Excel Programming |