+ Reply to Thread
Results 1 to 2 of 2

Specify data to import from other excel file

  1. #1
    Registered User
    Join Date
    02-17-2006
    Posts
    1

    Specify data to import from other excel file

    Hi! I was wondering if anyone could help me. I need to import a column from another excel file. The thing is, the macro needs to ask you the name of the file and the column (if it could possible with also a range. i.e. you input the column "D" and you only want from D3:D1200) in where the data is and copy them to the active cell.

    Until now, I have only made the code ask for the file from which the data is imported, but the range has to be programmed previously, is there any function that ask the user to give the range of cells to be copy? And the range copied in a specific column?

    What i am trying to do is that in column A I import the description of a variable number of products, in column B I import the code of the product and in column D the number of packages (all of them from the same file). After that I program another macro the calculates some values with VLOOKUP, INDEX, MATCH with other files. I am stucked up with this and can't continue, if someone can help please, it will be very thankful.

    My code is this one:

    Sub Example5()
    Dim basebook As Workbook
    Dim mybook As Workbook
    Dim sourceRange As Range
    Dim destrange As Range
    Dim SourceRcount As Long
    Dim N As Long
    Dim rnum As Long
    Dim MPath As String
    Dim SaveDriveDir As String
    Dim FName As Variant

    SaveDriveDir = CurDir
    MPath = "C:\Documents and Settings\ba7268\My Documents"
    ChDrive MPath
    ChDir MPath

    FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", _
    MultiSelect:=True)
    If IsArray(FName) Then
    Application.ScreenUpdating = False
    Set basebook = ThisWorkbook
    rnum = 1
    basebook.Worksheets(1).Cells.Clear
    'clear all cells on the first sheet

    For N = LBound(FName) To UBound(FName)
    Set mybook = Workbooks.Open(FName(N))
    Set sourceRange = mybook.Worksheets(1).Range("H1:H1500")
    SourceRcount = sourceRange.Rows.Count


    With sourceRange
    Set destrange = basebook.Worksheets(1).Cells(rnum, "A"). _
    Resize(.Rows.Count, .Columns.Count)
    End With
    destrange.Value = sourceRange.Value

    mybook.Close False
    rnum = rnum + SourceRcount
    Next
    End If
    ChDrive SaveDriveDir
    ChDir SaveDriveDir
    Application.ScreenUpdating = True
    End Sub

    Thanks!

    Daniel

  2. #2
    Toppers
    Guest

    RE: Specify data to import from other excel file

    Hi,

    Try:

    Dim srcrng as range

    ........
    Set mybook = Workbooks.Open(FName(N))
    Set srcrng = Application.InputBox("Enter source range", Type:=8)
    Set sourceRange = mybook.Worksheets(1).Range(srcrng.Address)


    HTH

    "reditz11" wrote:

    >
    > Hi! I was wondering if anyone could help me. I need to import a column
    > from another excel file. The thing is, the macro needs to ask you the
    > name of the file and the column (if it could possible with also a
    > range. i.e. you input the column "D" and you only want from D3:D1200)
    > in where the data is and copy them to the active cell.
    >
    > Until now, I have only made the code ask for the file from which the
    > data is imported, but the range has to be programmed previously, is
    > there any function that ask the user to give the range of cells to be
    > copy? And the range copied in a specific column?
    >
    > What i am trying to do is that in column A I import the description of
    > a variable number of products, in column B I import the code of the
    > product and in column D the number of packages (all of them from the
    > same file). After that I program another macro the calculates some
    > values with VLOOKUP, INDEX, MATCH with other files. I am stucked up
    > with this and can't continue, if someone can help please, it will be
    > very thankful.
    >
    > My code is this one:
    >
    > Sub Example5()
    > Dim basebook As Workbook
    > Dim mybook As Workbook
    > Dim sourceRange As Range
    > Dim destrange As Range
    > Dim SourceRcount As Long
    > Dim N As Long
    > Dim rnum As Long
    > Dim MPath As String
    > Dim SaveDriveDir As String
    > Dim FName As Variant
    >
    > SaveDriveDir = CurDir
    > MPath = "C:\Documents and Settings\ba7268\My Documents"
    > ChDrive MPath
    > ChDir MPath
    >
    > FName = Application.GetOpenFilename(filefilter:="Excel Files
    > (*.xls), *.xls", _
    > MultiSelect:=True)
    > If IsArray(FName) Then
    > Application.ScreenUpdating = False
    > Set basebook = ThisWorkbook
    > rnum = 1
    > basebook.Worksheets(1).Cells.Clear
    > 'clear all cells on the first sheet
    >
    > For N = LBound(FName) To UBound(FName)
    > Set mybook = Workbooks.Open(FName(N))
    > Set sourceRange = mybook.Worksheets(1).Range("H1:H1500")
    > SourceRcount = sourceRange.Rows.Count
    >
    >
    > With sourceRange
    > Set destrange =
    > basebook.Worksheets(1).Cells(rnum, "A"). _
    > Resize(.Rows.Count,
    > .Columns.Count)
    > End With
    > destrange.Value = sourceRange.Value
    >
    > mybook.Close False
    > rnum = rnum + SourceRcount
    > Next
    > End If
    > ChDrive SaveDriveDir
    > ChDir SaveDriveDir
    > Application.ScreenUpdating = True
    > End Sub
    >
    > Thanks!
    >
    > Daniel
    >
    >
    > --
    > reditz11
    > ------------------------------------------------------------------------
    > reditz11's Profile: http://www.excelforum.com/member.php...o&userid=31682
    > View this thread: http://www.excelforum.com/showthread...hreadid=513811
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1