Results 1 to 10 of 10

VBA to import data from another Excel file

This is a discussion on VBA to import data from another Excel file within the Excel Questions forums, part of the Question Forums category; Hi, Does anyone by any chance know of a VBA (or maybe help me start out to build one )that ...

  1. #1
    New Member
    Join Date
    Jul 2012
    Posts
    29

    Default VBA to import data from another Excel file

    Hi,

    Does anyone by any chance know of a VBA (or maybe help me start out to build one )that can do the following:

    I click on a button and then a selection from one sheet from another excel document is pasted in the current document.

    However these are the conditions:
    -The user should be able to specify which document should be copied from. So a pop up appears in which the user selects the document to copy from.
    -I want to preset the specified sheetname and range that should be copied from and also preset where to copy it to.

    For example user selects data.xls and then all information contained in cells from sheet1 A1:Z100 of that document will be copied into sheet2 A1:Z100 of the current document.

    I would be very grateful, thanks in advance!

    Suzan

  2. #2
    New Member
    Join Date
    Jul 2012
    Location
    NYC Metro
    Posts
    14

    Default Re: VBA to import data from another Excel file

    Quote Originally Posted by SaveSquirrels View Post
    Does anyone by any chance know of a VBA (or maybe help me start out to build one )
    By "chance" there are probably hundreds of members here who could bang out the entire thing for you within minutes (and maybe someone actually will).
    But in the meantime, I'll help you "start", since you really should use Google (or this site!) to gather information on how to construct the different elements of the process that you wish to create.

    Quote Originally Posted by SaveSquirrels View Post
    -The user should be able to specify which document should be copied from. So a pop up appears in which the user selects the document to copy from.
    specifyFile = Application.GetOpenFilename _
    (Title:="Specify the file to open", FileFilter:="Excel Files *.xls (*.xls),")
    Workbooks.Open Filename:=specifyFile

    Do you know how to implement that?

  3. #3
    New Member
    Join Date
    Jul 2012
    Posts
    29

    Default Re: VBA to import data from another Excel file

    Quote Originally Posted by ifthenelse View Post
    specifyFile = Application.GetOpenFilename _
    (Title:="Specify the file to open", FileFilter:="Excel Files *.xls (*.xls),")
    Workbooks.Open Filename:=specifyFile

    Do you know how to implement that?
    Thanks for your help! I'm very new to this and am not that bright.

    I've managed to run it by adding a new module and inserting your code between sub and end sub. It opens the selection screen but when you open a file it opens a new file.

  4. #4
    New Member
    Join Date
    Jul 2012
    Location
    NYC Metro
    Posts
    14

    Default Re: VBA to import data from another Excel file

    That piece of code opens a file that exists on your computer.

    Try this...

    specifyFile = Application.GetOpenFilename _
    (Title:="Specify the file to open", FileFilter:="Excel Files *.xls (*.xls),")
    Workbooks.Open Filename:=specifyFile
    MsgBox (ActiveWorkbook.Name)

    The message box should reveal the name of the file that you just selected to open.
    Same, yes?

  5. #5
    New Member
    Join Date
    Jul 2012
    Posts
    29

    Default Re: VBA to import data from another Excel file

    Yea it shows the message. What I was looking for was something like this code I found online. When I import a sheet it does everything perfectly but for some reason it skips D1:AG1??? Can anyone tell me how to solve this?

    Code:
    Public Sub GetData(SourceFile As Variant, SourceSheet As String, _
                       SourceRange As String, TargetRange As Range, Header As Boolean, UseHeaderRow As Boolean)
    ' 30-Dec-2007, working in Excel 2000-2007
        Dim rsCon As Object
        Dim rsData As Object
        Dim szConnect As String
        Dim szSQL As String
        Dim lCount As Long
    
        ' Create the connection string.
        If Header = False Then
            If Val(Application.Version) < 12 Then
                szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                            "Data Source=" & SourceFile & ";" & _
                            "Extended Properties=""Excel 8.0;HDR=No"";"
            Else
                szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                            "Data Source=" & SourceFile & ";" & _
                            "Extended Properties=""Excel 12.0;HDR=No"";"
            End If
        Else
            If Val(Application.Version) < 12 Then
                szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                            "Data Source=" & SourceFile & ";" & _
                            "Extended Properties=""Excel 8.0;HDR=Yes"";"
            Else
                szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                            "Data Source=" & SourceFile & ";" & _
                            "Extended Properties=""Excel 12.0;HDR=Yes"";"
            End If
        End If
    
        If SourceSheet = "" Then
            ' workbook level name
            szSQL = "SELECT * FROM " & SourceRange$ & ";"
        Else
            ' worksheet level name or range
            szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];"
        End If
    
        On Error GoTo SomethingWrong
    
        Set rsCon = CreateObject("ADODB.Connection")
        Set rsData = CreateObject("ADODB.Recordset")
    
        rsCon.Open szConnect
        rsData.Open szSQL, rsCon, 0, 1, 1
    
        ' Check to make sure we received data and copy the data
        If Not rsData.EOF Then
    
            If Header = False Then
                TargetRange.Cells(1, 1).CopyFromRecordset rsData
          
            End If
    
        Else
            MsgBox "No records returned from : " & SourceFile, vbCritical
        End If
    
        ' Clean up our Recordset object.
        rsData.Close
        Set rsData = Nothing
        rsCon.Close
        Set rsCon = Nothing
        Exit Sub
    
    SomethingWrong:
        MsgBox "The file name, Sheet name or Range is invalid of : " & SourceFile, _
               vbExclamation, "Error"
        On Error GoTo 0
    
    End Sub
    
    ' With the example below you can select one file with GetOpenFilenamewhere
    
    Sub GetData_Example4()
    
    
        SaveDriveDir = CurDir
        MyPath = "C:\"    'or use "C:\Data"
        ChDrive MyPath
        ChDir MyPath
        FName = Application.GetOpenFilename(FileFilter:="Excel Files, *.xl*")
    
        If FName = False Then
            'do nothing
        Else
            GetData FName, "Sheet1", "A1:ZZ100", Sheets("Sheet1").Range("A1"), False, False
        End If
    
        ChDrive SaveDriveDir
        ChDir SaveDriveDir
    End Sub

  6. #6
    New Member
    Join Date
    Jul 2012
    Posts
    29

    Default Re: VBA to import data from another Excel file

    Got this code from someone in another forum and it works!
    Code:
    Sub tgr()          Dim wsDest As Worksheet          Set wsDest = ActiveWorkbook.Sheets(2)     Application.ScreenUpdating = False          On Error Resume Next     With Workbooks.Open(Application.GetOpenFilename("Excel Files, *.xls*"))         .Sheets(1).Range("A1:Z100").Copy wsDest.Range("A1")         .Close False     End With     On Error GoTo 0          Application.ScreenUpdating = True     Set wsDest = Nothing      End Sub

  7. #7
    New Member
    Join Date
    Jul 2012
    Location
    NYC Metro
    Posts
    14

    Default Re: VBA to import data from another Excel file

    You're posting SQL code, which seems to have no bearing whatsoever on your original request.
    (and overkill, to say the least)

    btw....
    Yea it shows the message
    I know it shows the message. That wasn't the point.

    Good luck with your project.

  8. #8
    Board Regular
    Join Date
    May 2014
    Posts
    365

    Default Re: VBA to import data from another Excel file

    .........."..............since you really should use Google (or this site!) to gather information on how to construct the different elements of the process that you wish to create............."




    Google for this site is very good... see my signiture below
    Alan
    Last edited by DocAElstein; Jul 6th, 2014 at 07:03 AM.
    -Help keep this forum efficient! Check this first before posting a new Thread):- Do a Google ):- Try a search something like):-
    site:MrExcel.com "Write here Title or Theme of wot you are looking for"
    For Example, Type in):- site:MrExcel.com ".clear or .delete"
    And coerce a Great day.
    Using XL 2007 2010 in Vista ; XL 2003 2010 in XP

  9. #9
    New Member
    Join Date
    Aug 2014
    Posts
    14

    Default Re: VBA to import data from another Excel file

    what if I need to put a list of drop down menu inside the VBA code to select the worksheet?
    How to do that?

  10. #10
    Board Regular
    Join Date
    May 2014
    Posts
    365

    Default Re: VBA to import data from another Excel file

    Quote Originally Posted by ankit13 View Post
    what if I need to put a list of drop down menu inside the VBA code to select the worksheet?
    How to do that?
    Hi
    . I am not quite sure wot you are doing on this old (mostly dead!) thread.

    As a new member I suggest you start a new thread or at least give a lot more detail about exactly wot you want, assuming your requirement is similar to that originally in this thread
    -Help keep this forum efficient! Check this first before posting a new Thread):- Do a Google ):- Try a search something like):-
    site:MrExcel.com "Write here Title or Theme of wot you are looking for"
    For Example, Type in):- site:MrExcel.com ".clear or .delete"
    And coerce a Great day.
    Using XL 2007 2010 in Vista ; XL 2003 2010 in XP

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com