Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    126
    Thanked 5 Times in 5 Posts

    Macro to import Data (2007)

    I import data using the macro below

    Sub Import_Data()
    Range("Import").QueryTable.Refresh BackgroundQuery:=False
    End Sub

    The "Import Text File" then appears.

    I would like VBA code to be able to clear the name under "File name" and under "files of Type" I would like "all files" to appears.

    This would then allow me to click on the file name that I want to import

    Your assistance will be most appreciated

    Regards

    Howard

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro to import Data (2007)

    If you are going the macro route why not use GetOpenFileName to have the user select a txt file and once selected use Workbooks.OpenText method to open it with the parameters you desire...

    Steve

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    126
    Thanked 5 Times in 5 Posts

    Re: Macro to import Data (2007)

    Hi Steve

    Thanks for getting back to me.

    I have amended my code to:

    Sub Import_Data()

    Application.GetOpenFilename ( _
    "all Files(*.),*.")

    End Sub

    The imported data needs to be copied into the area named "Import"

    It would be appreciated if you could assist me

    Regards

    Howard

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro to import Data (2007)

    We need a little more to go on. After you select a file, how do you want the file parsed when it is opened. should all the imported file be copied into the range named import? If the range is not large enough do you only want whatever fits or should the range overlow to the right and down?

    It might help to attach a representative workbook with the range as desired and an representative example text file to import...

    Steve

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    126
    Thanked 5 Times in 5 Posts

    Re: Macro to import Data (2007)

    Hi Steve

    Thanks for the reply. When opening up the imported file, I use delimited and space. The entire imported file (in the eg auto.aug in the directory pull) open and copied must be copied into destination workbook to the range named "import" The destination file is very large to upload. I deleted all the data , except the range named 'Import"
    Attached Files Attached Files

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro to import Data (2007)

    The range named import is only 1 cell so I presume that is the upper left corner and it should just fill as much down and to the right?

    What if there is already data in the sheet: should it be cleared first?

    Steve

  7. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    126
    Thanked 5 Times in 5 Posts

    Re: Macro to import Data (2007)

    Hi Steve

    Thanks for the reply
    The range named import is only 1 cell , which is the upper left corner and it should just fill as much down and to the right

    The data that is already on the sheet must first be cleared

    Regards

    Howard

  8. #8
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    126
    Thanked 5 Times in 5 Posts

    Re: Macro to import Data (2007)

    HI Steve

    Have you managed to have a look at the attachments which I posted on post # 748177?

    Your assistance will be most appreciated

    Regards

    Howard

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro to import Data (2007)

    Does this work?
    Steve

    <pre>Option Explicit
    Sub Import_Data()
    Dim wkb As Workbook
    Dim rng As Range
    Dim sPathFile As String

    Set rng = ActiveWorkbook.Names("Import").RefersToRange

    sPathFile = Application.GetOpenFilename( _
    "all Files(*.*),*.*")
    Workbooks.OpenText Filename:=sPathFile, _
    Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, _
    Tab:=True, Semicolon:=False, Comma:=False, Space:=True, Other:=False
    Set wkb = ActiveWorkbook

    rng.Parent.Cells.Clear
    wkb.Worksheets(1).UsedRange.Copy rng

    Set rng = Nothing
    Set wkb = Nothing
    End Sub</pre>


  10. #10
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    126
    Thanked 5 Times in 5 Posts

    Re: Macro to import Data (2007)

    Hi Steve

    Thanks for the help

    When executing the macro a "compile error:Syntax error" appears and the following line of code is highlighted

    sPathFile = Application.GetOpenFilename( _ "all Files(*.*),*.*") Workbooks.OpenText Filename:=sPathFile, _ Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, _ Tab:=True, Semicolon:=False, Comma:=False, Space:=True, Other:=False Set wkb = ActiveWorkbook

    Your assistance is sorting this out will be most appreciated

    Regards

    Howard

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro to import Data (2007)

    It should be 3 lines of code

    sPathFile = Application.GetOpenFilename( _
    "all Files(*.*),*.*")

    Workbooks.OpenText Filename:=sPathFile, _
    Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, _
    Tab:=True, Semicolon:=False, Comma:=False, Space:=True, Other:=False

    Set wkb = ActiveWorkbook


    Steve

  12. #12
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    126
    Thanked 5 Times in 5 Posts

    Re: Macro to import Data (2007)

    Hi steve

    Thanks for the help. Macro now executes without any error. I just need one small change. The file to be opened is in the director cull. When the file opens I want it to default to this directory.

    Kindly amend macro accordingly

    Regards

    Howard

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro to import Data (2007)

    Add the line:
    <pre>ChDir "C:Pull"</pre>


    before you call the getopenfilename command.

    Steve

  14. #14
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    126
    Thanked 5 Times in 5 Posts

    Re: Macro to import Data (2007)

    <P ID="edit" class=small>(Edited by HowardC on 22-Dec-08 17:56. )</P>Hi Steve

    Have amended your macro slightly to meet with my requirements, by clearing the data in the range named import and 7 columns to the right as well as to the end range. However, when activating the macro the following error message appears after selecting the file from the pull directory and the following code is highlighted

    run time error "the text you entered is not a valid reference or a defined named"

    Application.Goto Reference:="import"

    Full code is as follows:

    Sub Import_Data1()
    Dim wkb As Workbook
    Dim rng As Range
    Dim sPathFile As String

    Set rng = ActiveWorkbook.Names("Import").RefersToRange
    ChDir "C:Pull"
    sPathFile = Application.GetOpenFilename( _
    "all Files(*.*),*.*")

    Workbooks.OpenText Filename:=sPathFile, _
    Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, _
    Tab:=True, Semicolon:=False, Comma:=False, Space:=True, Other:=False

    Set wkb = ActiveWorkbook

    Application.Goto Reference:="import"
    With Range("Import")
    .Resize(7).End(xlDown).Selection
    .ClearContents
    End With



    wkb.Worksheets(1).UsedRange.Copy rng

    Set rng = Nothing
    Set wkb = Nothing
    End Sub

    Your assistance will be most appreciated

    Regards

    Howard






    It would be appreciated if you could assist

    Regards

    Howard

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro to import Data (2007)

    I don't understand the need to "goto" the range. It just slows down the code. The code was written to not have to select any sheet or range

    I would just delete that line...

    To clear just the area you can use:
    rng.Resize(rng.End(xlDown).Row, 7).ClearContents

    [Note your code will only clear the contents of the 1 cell not all the area that may be filled in...]

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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