Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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.

Reply With Quote
  #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

Reply With Quote
  #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.

Reply With Quote
  #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

Reply With Quote
  #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!!!

Reply With Quote
  #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.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 11:21 PM.


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