Can Excel Remember an Import File Format

[drewg]

like Access does?
Setting the columns on a test file csv import is getting old fast.
thanks a lot
drew

 

[texasalynn]

I setup a macro to do that. Turn on your macro recorded and walk through the steps. Then after the macro is completed it can be cleaned up and make it generic to work repeated. Post back with your code and questions, if that making it generic is not your skill set.

 

[schielrn]

It may be able to, but here is some code I have for importing a file (txt file for this specific case) and then using text to columns to delimit it and the bold-red line is where I have different columns set to text, number, date, etc...

I know this isn't exactly what you are looking for, but I have just linked it to a button and allowed people to just locate the file on the server they want to import and it does all the work for them and cleans it up nicely. I also check teh filename to make sure it has a correct beginning so they don't import something wrong on accident.

Sub ImportIJ()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
    Do
        strbook = Application.GetOpenFilename(FileFilter:="Text Files (*.txt),*.txt", Title:="Please select the IJ txt file to open")
        If Len(strbook) < 6 Then Exit Sub
        If counter > 0 Then MsgBox "You did not select the IJ File (iahs)." & vbCrLf & vbCrLf & "Please select the correct file."
        counter = counter + 1
    Loop Until UCase(Mid(strbook, InStrRev(strbook, "\") + 1, 4)) = "IAHS" Or UCase(Mid(strbook, InStrRev(strbook, "\") + 1, 2)) = "IJ" Or UCase(Mid(strbook, InStrRev(strbook, "\") + 1, 5)) = "RIAHS"
    With Sheets("IJ")
        .Range("A1", .Cells(Rows.Count, Columns.Count).Address).Delete
        With .QueryTables.Add(Connection:= _
            "TEXT;" & strbook & "", Destination:=.Range("$A$1"))
            .PreserveFormatting = True
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False
         .TextFileColumnDataTypes = Array(3, 2, 2, 1, 2, 1, 2, 2, 2)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
    For Each nm In ThisWorkbook.Names
        nm.Delete
    Next nm
    For Each cn In ThisWorkbook.Connections
        cn.Delete
    Next cn
    End With
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

If you need any help modifying it, if you want to go that route, let me know.

Note that this also deletes all connections and named ranges in the workbook to keep the file size down because we import 3-5 files into it daily, but I don't have them actually save this template file when they are done using it, so I guess its not really needed unless they were to save it.

Hope that helps.

 

[drewg]

Thank you both!
never thought of the macro recorder idea!
Also, thanks very much for the code based solution...i will try to get it to go for me too.
drew

 

[schielrn]

Thank you both!
never thought of the macro recorder idea!
Also, thanks very much for the code based solution...i will try to get it to go for me too.
drew

Most of the importing code was all from a macro recorder for me and then I cleaned it up and allowed them to select a file and so forth. So I would probably start with the recorder as texasalynn stated and then clean it up from there and add the bells and whistles you want.