Can I "Save As..." an Excel Sheet to an Access Table?

[John Bixtis]

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

 

[Andy B]

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.

 

[John Bixtis]

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]

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.

 

[NickHK]

John,
Save the new Excel file to the same path & name each day, with Access linked
to this file.

NickHK

 

[Ian Digby]

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

 

[Jamie Collins]

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.

--