Automate Text File Import into Excel 1

[shelby55]

Hello

I am using Excel 2003. I have many .txt files to import into Excel with specific column data types. I used the macro recording and got the following:

Sub ImportRegrouped()
Workbooks.OpenText Filename:= _
        "E:\My Documents\MyFile.txt"_
        , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), _
        Array(2, 2), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 2), Array(7, 2), Array(8, 2), _
        Array(9, 2), Array(10, 4), Array(11, 1), Array(12, 4), Array(13, 1), Array(14, 1), Array(15 _
        , 2), Array(16, 2), Array(17, 2), Array(18, 2), Array(19, 2), Array(20, 2), Array(21, 2), _
        Array(22, 2), Array(23, 2), Array(24, 2), Array(25, 2), Array(26, 1), Array(27, 2), Array( _
        28, 1), Array(29, 1), Array(30, 1), Array(31, 2), Array(32, 2), Array(33, 2), Array(34, 2), _
        Array(35, 2), Array(36, 2), Array(37, 2), Array(38, 2), Array(39, 1), Array(40, 1), Array( _
        41, 1), Array(42, 2), Array(43, 2), Array(44, 1), Array(45, 1), Array(46, 2), Array(47, 2), _
        Array(48, 2), Array(49, 2), Array(50, 2), Array(51, 2), Array(52, 2), Array(53, 2), Array( _
        54, 2), Array(55, 2), Array(56, 2), Array(57, 2), Array(58, 2), Array(59, 2), Array(60, 2), _
        Array(61, 2), Array(62, 2), Array(63, 2), Array(64, 2), Array(65, 2), Array(66, 2), Array( _
        67, 2)), TrailingMinusNumbers:=True
End Sub

The files aren't named the same but have the same structure. How would I now take this and create a button to run the macro but prompt the user for the proper folder/file to import?

Thanks very much.

 

[SkipVought]

hi,

Sub ImportRegrouped()
    Dim fileToOpen
    
    fileToOpen = Application _
        .GetOpenFilename("Excel Files (*.xls*), *.xls*")
        
    If fileToOpen <> False Then
        Workbooks.OpenText FileName:= _
                fileToOpen, _
                Origin:=437, _
                StartRow:=1, _
                DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, _
                ConsecutiveDelimiter:=False, _
                Tab:=True, _
                Semicolon:=False, _
                Comma:=False, _
                Space:=False, _
                Other:=False, _
                FieldInfo:=Array( _
                    Array(1, 2), Array(2, 2), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2), Array(10, 4), _
                    Array(11, 1), Array(12, 4), Array(13, 1), Array(14, 1), Array(15, 2), Array(16, 2), Array(17, 2), Array(18, 2), Array(19, 2), Array(20, 2), _
                    Array(21, 2), Array(22, 2), Array(23, 2), Array(24, 2), Array(25, 2), Array(26, 1), Array(27, 2), Array(28, 1), Array(29, 1), Array(30, 1), _
                    Array(31, 2), Array(32, 2), Array(33, 2), Array(34, 2), Array(35, 2), Array(36, 2), Array(37, 2), Array(38, 2), Array(39, 1), Array(40, 1), _
                    Array(41, 1), Array(42, 2), Array(43, 2), Array(44, 1), Array(45, 1), Array(46, 2), Array(47, 2), Array(48, 2), Array(49, 2), Array(50, 2), _
                    Array(51, 2), Array(52, 2), Array(53, 2), Array(54, 2), Array(55, 2), Array(56, 2), Array(57, 2), Array(58, 2), Array(59, 2), Array(60, 2), _
                    Array(61, 2), Array(62, 2), Array(63, 2), Array(64, 2), Array(65, 2), Array(66, 2), Array(67, 2)), _
                TrailingMinusNumbers:=True
    End If
    
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Thanks Skip...you make it look so easy!! I am assuming I can just add to a button on my Excel toolbar and go from there?

 

[SkipVought]

Yes.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Sorry but I opened a worksheet, added the code then selected toolbar, customize, macro, and assigned as custom addition to the toolbar. I assigned the macro to the icon, changed the name and everything worked great.

But once I leave Excel, the macro leaves with the worksheet. How can I make this a permanent custom button on the toolbar? Thanks.

 

[SkipVought]

Put the macro in your Pesonal Workbook. Then assign the tool button's event to the macro in your Personal Workbook.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Thanks Skip!!

 

[shelby55]

Hi Skip

Sorry I must be really stupid but I can't find how to do that....I used the macro already to get the code I showed you. I'm going into an Excel worksheet in VBA and copying the code into there to then put on the toolbar. Doing it that way, how can I save it to my Personal Workbook?

Thanks.

 

[SkipVought]

Toggle to the VB Editor.

ctr+R to view the Project Explorer.

In the PE, you will see a Personal.xls* IF YOU HAVE ONE.

If you have one, paste the macro code into any module in your personal workbook.

Otherwise, tobble back to the sheet, start the macro recorder, select PERSONAL.XLS* in the Store macro in" dropdown. thi the OK button, select another cell, stop the macro recorder, toggle to the VB Editor and proceed with instruction if you had a personal workbook.

SAVE YOUR PERSONAL WORKBOOK.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Got it, thanks Skip!!

 

[shelby55]

Hi Skip

I figured out the macro part but I just noticed something: when I import the file from text there are some number fields that I identify to come in as text. So a doctor number with leading zeros would still have 5 characters i.e. 00050. This is how it works when I import manually but using the macro it imports as a number field.

How do I control data type in the cell? Thanks.

 

[shelby55]

Hi

I thought I'd provide code to see if it helps in determining what the problems are with my macro.

I used what you told me to do the same for other import files. The only difference between this file and the one originally provided is that semi-colon:=true.

Sub Import_AcuteDx()

Dim fileToOpen
    
    fileToOpen = Application _
        .GetOpenFilename

        If fileToOpen <> False Then
        Workbooks.OpenText Filename:= _
                fileToOpen, _
                Origin:=437, _
                StartRow:=1, _
                DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, _
                ConsecutiveDelimiter:=False, _
                Tab:=True, _
                Semicolon:=True, _
                Comma:=False, _
                Space:=False, _
                Other:=False, _
                FieldInfo:=Array( _
        Array(1, 2), Array(2, 2), Array(3, 2),_
        Array(4, 5), Array(5, 2), Array(6, 2),_
        Array(7, 2), Array(8, 1), _
        Array(9, 1)), TrailingMinusNumbers:=True
    End If
End Sub

But as mentioned, one of the columns that is supposed to be text isn't importing as such though the original file I posted is working fine without any issues.

Thanks for any assistance.

 

[SkipVought]

L'd suspect that this file has a different column format.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

Which means what, Skip? Can I fix this? Thanks.

 

[SkipVought]

Well first you'll want to verify.

Then, if it IS different, find out WHY or just 'fix' the import spec.

If its NOT different then there are other problems.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}