External References

[geneandlinda]

I use the following macro to retrieve data from a file (i.e.,FileName:="C:DYNOYA1.1",)and insert the parsed results into a spreadsheet. I haven't been able to figure out how to input the file name I want to retrieve into a cell in the workbook and reference that cell in the macro. I'm currently forced to edit the macro each time I run it and change the file name manually.

For some reason I've been unable to use the linking worksheets by entering an external reference coding instructions.

Any suggestions?


Sub Macro3()
'
' Macro3 Macro
' Macro recorded 3/10/02 by Gene and Joey
'

'
Application.CutCopyMode = False
Workbooks.OpenText FileName:="C:DYNOYA1.1", Origin:=xlWindows, StartRow _
:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(5, 1), Array( _
11, 1), Array(15, 1))
Range("A1:D350").Select
Selection.Copy
ActiveWindow.WindowState = xlMinimized
Sheets("Gene1 Data").Select
ActiveWindow.ScrollColumn = 11
ActiveWindow.SmallScroll Down:=-17
Range("P5").Select
ActiveSheet.Paste
ActiveWindow.ScrollRow = 238
ActiveWindow.SmallScroll Down:=-39
End Sub

[Tom Schreiner]

Add this at the beginning of your procedure to grab a filename.

Dim FileToOpen

ChDir "C:Dyno"

FileToOpen = Application.GetOpenFilename

If Not FileToOpen Then Exit Sub

Tom