opening a specific file from Macro

[sosteffo]

I have a macro which opens a word doc, and imports the files into
specific format i want, i.e delimted widths etc.

Problem is i want to have the macro setup so i can specific the name o
the file i want to open/run macro on

 

[Don Guillett]

As always, pls post your code (not a workbook) for comments

 

[Frank Kabel]

Hi
without posting your code only a general hint: you may have a look at
the GetOpenFilename method tol select a file (see the help file for
more information)

 

[Tom Ogilvy]

use

fName = Application.GetOpenFileName


this will allow you to select the file (it does not open it). Then you can
use the fully qualified filename held in fname to open the file.

See help on getopenfilename for additional arguments and options.

 

[sosteffo]

This is my macro, i want to replace the file name with a field that as
the file name, i.e enter the name in a form/field.


Workbooks.OpenText Filename:="S:\WORK\spool\249391.RTF"
Origin:=xlWindows _
, StartRow:=1, DataType:=xlFixedWidth
FieldInfo:=Array(Array(0, 1), Array( _
12, 1), Array(19, 1), Array(54, 1), Array(81, 1), Array(108
1), Array(113, 1), Array(128, 1 _
), Array(142, 1), Array(156, 1), Array(246, 1))
ActiveWindow.SmallScroll Down:=9
Rows("1:24").Select
Range("A24").Activate
Selection.Delete Shift:=xlUp
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft
Columns("I:I").Select
Selection.Delete Shift:=xlToLeft
Cells.Select
Cells.EntireColumn.AutoFit
ActiveWindow.Zoom = 7

 

[sosteffo]

sorry i have just tried without any luck,

could you show me how my code would look after inserting the
"getopenfilename" - i keep getting errors


all i want it to do is prompt/ask for the filename??

 

[Frank Kabel]

Hi
if you may post your code it would be easier to add these statements

 

[sosteffo]

i need to change the file name/location to an optional field......

Workbooks.OpenText Filename:="S:\WORK\spool\249391.RTF"
Origin:=xlWindows _
, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1)
Array( _
12, 2), Array(19, 2), Array(54, 2), Array(81, 2), Array(108, 2)
Array(113, 1), Array(128, 1 _
), Array(142, 1), Array(156, 1), Array(246, 1))
ActiveWindow.SmallScroll Down:=9
Rows("1:24").Select
Range("A24").Activate
Selection.Delete Shift:=xlUp
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft
Columns("I:I").Select
Selection.Delete Shift:=xlToLeft
Cells.Select
Cells.EntireColumn.AutoFit
ActiveWindow.Zoom = 7

 

[Frank Kabel]

Hi
try

sub foo()
dim fname
fName = Application.GetOpenFileName
Workbooks.OpenText Filename:=fname,
Origin:=xlWindows _
, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1),
Array( _
12, 2), Array(19, 2), Array(54, 2), Array(81, 2), Array(108, 2),
Array(113, 1), Array(128, 1 _
), Array(142, 1), Array(156, 1), Array(246, 1))
'..... your existing code

 

[sosteffo]

sorry, does fname stand for something, this doesn't appear to work, whe
i copy into VB the whole thing is highlighed in red.

im close to giving up....

 

[Frank Kabel]

Hi
hat is highlighted in red?.
fname is just a variable (you can replace this variable name with any
of your choice). I just replaced in your existing code the 'hardcoded'
filename with this variabel

You may also check that you remove linebreaks caused by the newsreader

 

[sosteffo]

thanks for the help,


i simply copied your post into the Excel VB (macro) screen,

http://sourcepost.sytes.net/sourceview.aspx?source_id=11987

i must be missing something as it wont work,

from Workbooks.Opentext Filename .........the rest is highlighted re
(indicating an error

 

[josnah]

Hi,

When u just copy the code over, the line breaks are all messed up.

You should ensure that the line continuation char *" _"* is at the en
of each line