Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Import Data into Excel (Excel 2002)

    Hi!
    The macro below opens the file I'd like to import into Excel, and then performs multiple different functions to the data. I'm wondering how to change the code to import the data from the file mentioned in the macro INTO a file I have open... the problem with my current macro is that it is in a different file with pivot tables (plus it has the macro below in it) and I'd like the data to be imported into this file as opposed to a NEW file... I'm trying to avoid a copy and paste. I hope this makes sense!?!?!
    Thanks!!
    Lana

    Sub OpenAndFormatDownload()

    Dim lngRow As Long

    Application.ScreenUpdating = False

    'Open and Import the downoaded pcard file into Excel, start with row 3 of the
    'imported data as the first two rows are not needed and would just be
    'deleted anyway
    Workbooks.OpenText Filename:= _
    "K:groupsFinance2007PcardDownloadPcard download data file", Origin:=437 _
    , StartRow:=3, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
    , Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 1 _
    ), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
    Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
    , 1), Array(16, 1), Array(17, 1)), TrailingMinusNumbers:=True

    'Sort data in order to eliminate spaces in between regular data
    'and oop data
    Rows("1:65531").Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

    'Autofit columns, left align column F, format amount column
    'with commas and decimals, change column O to be 40 characters,
    'date format column P, place cursor in cell A1 so it looks
    'nice, zoom to 75%
    Cells.Select
    Cells.EntireColumn.AutoFit
    Columns("D").Select
    Selection.HorizontalAlignment = xlLeft
    Columns("F:F").Select
    Selection.Style = "Comma"
    Columns("O:O").Select
    Selection.ColumnWidth = 40
    Columns("P:P").Select
    Selection.NumberFormat = "mm/dd/yy;@"
    Range("A1").Select
    ActiveWindow.Zoom = 75

    'Concatenate columns to create unit

    lngRow = 1
    Do While Range("A" & lngRow) <> ""
    Range("Q" & lngRow) = Range("H" & lngRow) & Range("I" & lngRow) & Range("J" & lngRow)
    lngRow = lngRow + 1
    Loop

    Application.ScreenUpdating = False

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import Data into Excel (Excel 2002)

    You must open the text file in a separate window first, then transfer the data. Excel doesn't support importing directly into an existing workbook.
    The alternative would be to parse the text file line by line, but that would probably be more work.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Import Data into Excel (Excel 2002)

    If I parsed the file, then I could perform all the formatting and calculations AFTER it gets imported into the existing file... the database is usually around 1,500 lines long... do you think it would take to long to parse it... is that what you mean by it would take too long? Or do you mean it would be faster to copy & paste? If you mean faster, I'd agree it'd be faster for me to copy & paste, but I'm creating this for another user who doesn't use Excel like I do, so copying & pasting could mess things up... I'm trying to eliminate human error!!!
    Thanks!
    L.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import Data into Excel (Excel 2002)

    Reading such a text file line by line will definitely take longer than opening it in Excel, but that doesn't need to be prohibitive.

    But why not add the copy/paste step to the macro?

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Import Data into Excel (Excel 2002)

    I'll give the copy & paste a try. I know how to code the copy & paste part, however I don't know how to paste it into the file that is OPEN (it's also the file the contains the macro). The name of the file that the data needs to be pasted into will have a different name each time I perform the action, so I'm assuming I have to incorporate this variability into the code in the PASTE area of the code. Is this possible?
    Thanks!!
    Lana

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import Data into Excel (Excel 2002)

    If the workbook into you want to paste the data is also the one that contains the macro, you can refer to it as ThisWorkbook. So you can use code similar to the following:

    Range("A1").CurrentRegion.Copy Destination:=ThisWorkbook.Worksheets(1).Range("A1" )

    This will copy the contiguous area containing A1 in the active sheet (i.e. in the imported text file) to the first worksheet in the workbook containing the macro.

  7. #7
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Import Data into Excel (Excel 2002)

    Perfect!
    Thanks Hans!
    Lana
    Get some sleep!

Posting Permissions

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