Importing Data via Macro

[acs013]

Hi,
I'm trying to write a macro for importing data, the macro will take the user to select data source window (Data > Import external data > Import data), where they can select a file they wish to import and the options in text import wizard have already been set (Delimited, file origin, delimiters, where to put the data, etc). I would like it to open automatically when the workbook is opened

After that I would like to run another Macro that I have already written, which manipulates the data

Is this possible?

[Abc456]

Hi,

Do you mean something like this?

Private Sub Workbook_Open()

    Dim DestBook As Workbook, SourceBook As Workbook
    Dim DestCell As Range
    Dim RetVal As Boolean

    Application.ScreenUpdating = False
    Set DestBook = ActiveWorkbook
    Set DestCell = ActiveCell
    RetVal = Application.Dialogs(xlDialogOpen).Show("*.txt")
    If RetVal = False Then Exit Sub
    Set SourceBook = ActiveWorkbook
  Range(Range("a1"), Range("a1").SpecialCells(xlLastCell)).Copy

    DestBook.Activate
    Sheets("YOURSHEETNAME").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlValues

MsgBox "Done."

     SourceBook.Close False
Call YOURMACROTOMANIPULATEDATA
End Sub

If you open Excel you get a dialogwindow and you can select a file and import data from that file back to Excel. You can adjust it to how you want it.

[acs013]

Thanks,
Yeah that works but is there a way to automate import wizard? Or do you need to chose the options each time (Delimiters - semicolon)?

[JBeaucaire]

If the formatting of the file(s) that the user will select from is in a standard form, you can record a macro yourself of you selecting a file, setting all the options and importing the data...you can even leave the recorder going while you activate the other macro you've already written.

Once you've recorded that, post the resulting macro here and we can help you automate the file selection process to streamline it.

Be sure to GO ADVANCED and use the # icon...click on that first and then paste the code in between the [ code ] tags that appear.

[acs013]

Hi,
I just realised that something weird happens when I import the data using the code above, it does not import the data correctly, it changes the formatting of some of the dates completely in what appears to be random way. (Dates appear in about 8 columns and all seem to be effected). A warning (error) message appears on the effected cells and asks for the dates to be convertered from 2-digit to 4-digit either 19xx or 20xx.

If I manually import the data (Data > Import external data > Import data) its works and the data is correct.

Is there another way to import data or correct this problem?