Redirecting

[tishoo]

Anyone know how to create a file which when opened by Excel 2003 causes it
to redirect itself and open a second (csv) file? I know how to get a web
browser to do this using http-equiv="REFRESH" but cannot do it within Excel.

Many thanks

T

 

[Dave Peterson]

You can have a macro that in the first workbook that opens the .CSV file and
then closes itself:

Option Explicit
Sub auto_Open()
Workbooks.Open Filename:="C:\my documents\excel\book1.csv"
ThisWorkbook.Close savechanges:=False
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[tishoo]

You can have a macro that in the first workbook that opens the .CSV file
and
then closes itself:

Thanks for the response but what you suggest doesn't fit the bill.
I won't have access to the workbook once it's distributed and I want to keep
changing the destination file.

 

[Dave Peterson]

You could have the macro reads a text file to get the name and then open that
file.

 

[tishoo]

You could have the macro reads a text file to get the name and then open
that
file.

that could work. Any idea how I might do that?

 

[Dave Peterson]

Option Explicit
Sub Auto_Open()

Dim TextLine As String
Open "C:\myfile.txt" For Input As #1
Line Input #1, TextLine
Close #1

Workbooks.Open Filename:=TextLine
ThisWorkbook.Close savechanges:=False

End Sub

You should add some validity checks to it.

C:\myfile.txt
is a plain old text file that contains one line of data--the full name of the
..csv file.

 

[tishoo]

C:\myfile.txt
is a plain old text file that contains one line of data--the full name of
the
.csv file.

Thanks - works great if c:\myfile.txt is a local file. Doesn't work if it is
a network file - like http://www.mywebspace.com/myfile.txt

Unfortunately needs to be network for users to access it.

 

[Dave Peterson]

Maybe you can put it on a common network share. I don't speak the http stuff.

 

[Dave Peterson]

Ps. You may want to give all the requirements in your original posts. Then
someone who actually knows how to help may jump in.

 

[tishoo]

Ps. You may want to give all the requirements in your original posts.
Then
someone who actually knows how to help may jump in.

Apologies. I took it for granted that it would have to be a network file.
You're right though - I should have been more detailed.

I think the only way to do this would be a minor variant on your first
ingenious suggestion. If I program the distributed sheet1.xls to open a
second on the network (www.myswebspace.com/sheet2.xls) which I can alter at
will. Sheet2.xls can, as you suggested, be programmed to open
www.mywebspace.com/data.csv file and then close itself.

My only problem then is how to auto-switch back to sheet1.xls once that
procedure is complete. Users don't want to see the csv file - it just needs
to be open.

Any ideas gratefully received.

T

 

[tishoo]

I should add for completeness I have no idea what directory the users will
be installing sheet1.xls in.

 

[Dave Peterson]

Workbooks("Sheet1.xls").activate

Might do it.

Apologies. I took it for granted that it would have to be a network file.
You're right though - I should have been more detailed.

I think the only way to do this would be a minor variant on your first
ingenious suggestion. If I program the distributed sheet1.xls to open a
second on the network (www.myswebspace.com/sheet2.xls) which I can alter at
will. Sheet2.xls can, as you suggested, be programmed to open
www.mywebspace.com/data.csv file and then close itself.

My only problem then is how to auto-switch back to sheet1.xls once that
procedure is complete. Users don't want to see the csv file - it just needs
to be open.

Any ideas gratefully received.

T

 

[tishoo]

Workbooks("Sheet1.xls").activate

Might do it.

No but I did crack it with your help. I did

Private Sub Workbook_Open()
Workbooks.Open Filename:="http://www.mywebspace.com/data.csv"
ActiveWindow.WindowState = xlMinimized
ActiveWindow.WindowState = xlMinimized
ActiveWindow.WindowState = xlMaximized
ThisWorkbook.Close savechanges:=False


End Sub

Thanks for your assistance. I'm most grateful.

Best

A