Inporting files

[25jump]

I need to inport a text file. I would like it to be as automated as
possible.
I want to know if the following are possible and how they are
possible:
I start by opening excel, use an auto open marco then:
I want to text to import into the first spread sheet.(is it possible to
select the text file by clicking on it
Retitle excel document with the title of the text.

 

[Dave Peterson]

Try starting a new workbook
record a macro when you open the text file, format the sheet, apply
headers/footers/filters/whatever and when you save the file.

Then stop recording.

Save this workbook (with the macro) and rerun the macro when you want to import
the next file.

 

[25jump]

I got the marco:
Sub load_file()
'
' load_file Macro

'
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\rwhitfield\My Documents\Cap Test
Evaluation\JG02\Test Data\52551-001b_jg02 cap
retest_200507211316-0.dat" _
, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:= _
False, Comma:=False, Space:=False, Other:=False,
FieldInfo:=Array(Array _
(1, 1), Array(2, 1), Array(3, 1))
End Sub



Problems is it will always open the same file I need to make the
address change

 

[25jump]

I got the marco:
Sub load_file()
'
' load_file Macro
In need to make a marco that loads different files on the excel
spreadsheet.
I got this marco to load a spefic file but I need one that will open a
select or clicked on file. any help would be great.


Workbooks.OpenText Filename:= _
"C:\Documents and Settings\rwhitfield\My Documents\Cap Test
Evaluation\JG02\Test Data\52551-001b_jg02 cap
retest_200507211316-0.dat" _
, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier
_
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:= _
False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array
_
(1, 1), Array(2, 1), Array(3, 1))
End Sub

 

[Dave Peterson]

This may get you over the next hump:

Option Explicit
Sub load_file()

Dim myFileName As Variant
Dim CurFolder As String
Dim CheckHereFirst As String

CurFolder = CurDir

CheckHereFirst = "C:\Documents and Settings\rwhitfield" & _
"\My Documents\Cap Test Evaluation\JG02\Test Data"

On Error Resume Next
ChDrive CheckHereFirst
ChDir CheckHereFirst
On Error GoTo 0

myFileName = Application.GetOpenFilename(filefilter:="Dat Files,*.dat")

ChDrive CurFolder
ChDir CurFolder

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Workbooks.OpenText Filename:=myFileName, _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _
Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1))


End Sub


I like to put a giant button from the forms toolbar on the first worksheet of
that workbook that contains the macro code. Then I assign the macro to that
button.

I'll even add instructions to another worksheet -- just in case there are things
that have to be done first (like create the correct .dat file).