Turn Off Auto-Format in Macro

[Derek Johansen]

Hey everyone,

I am currently working on a macro to import data from a text file using a
deliminator. I really don't have much (if any) access to the outputting
program to make changes, so everything needs to happen on my end.

What's happening is when I pull in the dimensions of lumber, excel is seeing
things such as "3-1/2" or "7/16" and autoformatting them to be a date like
"3/1/2002" or "16-July." This is a big problem for me because I need to know
the dimensions of my lumber to accurately assign item codes and pricing.
What I thought would be an easy fix was to shut off auto format for excel
while the macro is running, and as a last line of the macro i would enable
the macro back on.

I have been doing some searching and can't find much on macros and
auto-formatting. I know there are some good programmers here, so I'm hoping
one of you knows how to do this!

Thanks in advance,

Derek

 

[Derek Johansen]

I sure can:

Sub Import()
FileName = Application.GetOpenFilename(FileFilter:="Text File (*.*),*.*")
If FileName = False Then
Exit Sub
End If
Workbooks.Add
'Import Text into sheet
Workbooks.OpenText FileName, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), _
Array(2, 2), Array(3, 2), Array(4, 1), Array(5, 1), Array(6, 1))

End Sub

I haven't done anything with it since the import, due to the errors in the
formatting! I will upload the text file I import to
www.derekjohansen.com/sample.txt so if you want to see the formatting issue
i'm talking about you can test with that.