Connection String between Excel and access

Question

This sub is run in Access to import data from an Excel file, "FileName"

This connection string/open procedure is pretty much copied from MSDN references, but there is an error.

When I step through, I receive "unrecognized database format" on the oConn.Open line.

Filename is a spreadsheet, not a database. How do I indicate this?

Public Sub Import2(FileName As Variant)
    Dim wb As Object, ws As Object
    Dim xl As Object
    Set xl = CreateObject("excel.Application")
    Dim qs As String
    Dim ValueString As String
    Dim sConn As String

    Dim oConn As Object
    Set oConn = CreateObject("adodb.connection")

    oConn.ConnectionString = "Provider=microsoft.ACE.OLEDB.12.0; Data Source=filename; Extended Properties=Excel 12.0 XML; hdr=yes" & ";"
    oConn.Open FileName

    Set wb = xl.Workbooks.Open(FileName)
    Set ws = wb.worksheets("For Export")
    data1 = ws.cells(2, 1)
    Data2 = ws.cells(2, 2)
    Data3 = ws.cells(2, 3)

    'Following lines may or may not be correct.  Working on the connection string
    'ValueString = Format(data1, "yyyy\/mm\/dd")
    'qs = "INSERT INTO MAF (FormDate) VALUES (#" & ValueString & "#)"

    DoCmd.RunSQL (qs)
    'CurrentDb.Execute qs

End Sub

Regarding your code i see that there is two different methods in together: adodb and Excel.application. If you comment this line 'oConn.Open FileName' you code work as you exspect? — bdn02
– bdn02, Commented Jan 29, 2016 at 9:30
That did it! Although i'm not sure why. I have a LOT more studying to do. Thanks! — Robert Kendall
– Robert Kendall, Commented Jan 29, 2016 at 9:34

bdn02 · Accepted Answer · 2016-01-29 07:33:52Z

1

Change Data Source=filename with something like Data Source=c:\test\db1.mdb with the path of your file

answered Jan 29, 2016 at 7:33

bdn02

1,5109 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Robert Kendall Over a year ago

I tried this but no change. It looks like the connection string is looking for a database, but I'm pulling data from a spreadsheet. Error was "Unrecognized database format"

Collectives™ on Stack Overflow

Connection String between Excel and access

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related