Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
| Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old May 31st, 2006, 05:55 AM
Authorized User
 
Join Date: Dec 2005
Location: London, , United Kingdom.
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default Import Excel data into a workbook

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.

  #2 (permalink)  
Old May 31st, 2006, 06:19 AM
Authorized User
 
Join Date: Mar 2006
Location: London, , United Kingdom.
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #3 (permalink)  
Old May 31st, 2006, 11:01 AM
Authorized User
 
Join Date: Dec 2005
Location: London, , United Kingdom.
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi jrogers,

Its on just 1 sheet, thank you.

  #4 (permalink)  
Old May 31st, 2006, 12:32 PM
Authorized User
 
Join Date: Mar 2006
Location: London, , United Kingdom.
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #5 (permalink)  
Old May 31st, 2006, 01:25 PM
Authorized User
 
Join Date: Dec 2005
Location: London, , United Kingdom.
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #6 (permalink)  
Old May 31st, 2006, 02:02 PM
Authorized User
 
Join Date: Mar 2006
Location: London, , United Kingdom.
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
Default

you're welcome.



Similar Threads
Thread Thread Starter Forum Replies Last Post
import data from excel darkhalf Access 5 February 14th, 2008 11:48 AM
Unable to import from an Excel Workbook donevco Access 7 April 27th, 2007 09:37 AM
How can we import the data from Ms Excel balesh ASP.NET 1.0 and 1.1 Professional 1 June 8th, 2006 02:15 PM
How to delete data from MS Excel Workbook database swadhinm ASP.NET 1.x and 2.0 Application Design 0 June 9th, 2005 12:49 AM
Excel Data Import bmains ASP.NET 1.x and 2.0 Application Design 4 April 14th, 2004 02:12 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.