VBA code for openning Excel 2010 file

Hello experts,

I am using Access 2010, how can I use the  VBA to open a Excel 2010 file as recordset ?
Thanks a lot.


Thanks & Regards,
Patrick
Patrick3388Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
HI,

Microsoft support gives many methods

one of them

 Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=SQLOLEDB;Data Source=<server>;" & _
        "Initial Catalog=<database>;User ID=<user>;Password=<password>"

    'Import by using OPENDATASOURCE.
    strSQL = "SELECT * INTO XLImport6 FROM " & _
        "OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _
        "'Data Source=C:\test\xltest.xls;" & _
        "Extended Properties=Excel 8.0')...[Customers$]"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    'Import by using OPENROWSET and object name.
    strSQL = "SELECT * INTO XLImport7 FROM " & _
        "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
        "'Excel 8.0;Database=C:\test\xltest.xls', " & _
        "[Customers$])"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    'Import by using OPENROWSET and SELECT query.
    strSQL = "SELECT * INTO XLImport8 FROM " & _
        "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
        "'Excel 8.0;Database=C:\test\xltest.xls', " & _
        "'SELECT * FROM [Customers$]')"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    cn.Close
    Set cn = Nothing

Open in new window


Reference
https://support.microsoft.com/en-us/kb/321686

Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HuaMin ChenProblem resolverCommented:
Try
Workbooks.Open ThisWorkbook.Path & "\" & "YourfileNameHere.xls"
Activewindow.WindowState = xlMinimized
ThisWorkbook.Activate

Open in new window

0
Patrick3388Author Commented:
Thanks for reply.


Here is my code, however, when I open Excel 2010 (.xlsx) file, it returns error "External table is not in the expected format"
So I tried to save it to .xls format, it also returns with "Object variable or With block variable not set"

Any idea ?
Thanks a lot !


Private Sub cmd_FileOpen2_Click()

On Error GoTo Err_cmd_FileOpen2


Dim strFile_Path As String
Dim strSQL As String
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
   
'Prompt user for file path
Application.FileDialog(msoFileDialogOpen).Show
strFile_Path = Application.FileDialog(msoFileDialogOpen).SelectedItems.Item(1)
txt_FilePath.SetFocus
txt_FilePath.Text = strFile_Path

Set cn = New ADODB.Connection
With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=" & strFile_Path & ";" & _
     "Extended Properties=Excel 8.0; "
    .Open

End With


strSQL = "SELECT * from Template$"
rs.Open strSQL, cn, adOpenStatic, adLockReadOnly


cmd_FileOpen_Exit2:
    Exit Sub

Err_cmd_FileOpen2:
    MsgBox Error$
    Resume cmd_FileOpen_Exit2
   
End Sub
0
Patrick3388Author Commented:
Problem solved
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.