VBA to import data from another Excel file

[SaveSquirrels]

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

 

[ifthenelse]

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.

-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?

 

[SaveSquirrels]

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.

 

[ifthenelse]

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?

 

[SaveSquirrels]

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?

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

 

[SaveSquirrels]

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</code>

 

[ifthenelse]

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.

 

[DocAElstein]

.........."..............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

 

[ankit13]

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

 

[DocAElstein]

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