Import Excel data into a workbook

Derek_05 · May 31st, 2006, 05:55 AM

Hallo,

I want to ask how one can import data from a workbook (.xls) that is not open into another currently working workbook with visual basic. So far I have only managed to look for the file and have no idea how to import the data there after:

Private Sub cmdImportReport_Click()

Dim sDirectory As String
Dim oFSO As Object
Dim oDirectory As Object
Dim oThisFile As Object

sDirectory = "C:\Documents and Settings\Desktop"

Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oDirectory = oFSO.GetFolder(sDirectory)

For Each oThisFile In oDirectory.Files
        If oThisFile.Name = "test report.xls" Then
               MsgBox "found file" 'test
               Exit Sub
        End If
Next

Set oFSO = Nothing
Set oDirectory = Nothing
Set oThisFile = Nothing

End Sub

Any help or advise will be greatly appreciated, thank you for your help and time.

jrogers · May 31st, 2006, 06:19 AM

Is the data just on one sheet within the closed workbook, or multiple

Derek_05 · May 31st, 2006, 11:01 AM

Hi jrogers,

Its on just 1 sheet, thank you.

jrogers · May 31st, 2006, 12:32 PM

You could try the following

Code:

Private Sub cmdImportReport_Click()

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strSQL As String
    Dim StrLoc As String

    On Error GoTo ErrorTrap:

    StrLoc = "C:\Documents and Settings\Desktop\test report.xls"

    cn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & StrLoc & ";" & _
            "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""")

    strSQL = "Select * from [Sheet1$]"

    rs.Open strSQL, cn, adOpenStatic, adLockReadOnly

    Range("a1").CopyFromRecordset rs


    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

Exit Sub

ErrorTrap:
    Select Case Err
        Case -2147467259
            MsgBox "the file is not present at this location"
    End Select


End Sub

All you need to ensure is that you include a reference to "Microsoft ActiveX Data Objects 2.8 Library"

If you need the headers displayed, then this can be done as well.. let me know if this works for you.. PLease not that in the StrSql statement, between the square brackets should be the name of the sheet you are wanting to import

Derek_05 · May 31st, 2006, 01:25 PM

thats excellent jrogers, thanks very much for your time and help!!!

jrogers · May 31st, 2006, 02:02 PM

you're welcome.