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?
 
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
 
HuaMin ChenSystem AnalystCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.