Data Connection to TXT File

  • Thread starter Thread starter MJS
  • Start date Start date
M

MJS

I want to create a spreadsheet with multiple worksheets. For each worksheet,
I want to indicate it's source of data is an external text file. Every time
I open the spreadsheet, I want the data from each text file to be reloaded
into the spreadsheet (the number of rows in the text file may change so the
number of rows populated with data in the worksheet will change).

I'm using 2007 and have been playing with data connections but I'm clearly
missing something because nothing is happening.

Help! Can someone tell me if I can even do this and if so, give me step by
step instructions for doing this correctly because I'm obviously doing
something wrong.

M.
 
I presume you're using the 'From Text' button in the Get External Data
section of the Data ribbon? Make sure you click the Properties button after
selecting your text file and tick the box to refresh the data on opening.
There are also other options under the properties which you may want to set
to affect how your data is refreshed. When you re-open your spreadsheet you
may see a warning box that data connections have been disabled just below the
ribbons, click the button to enable them and your data will refresh.

Andrea Jones
www.stratatraining.co.uk
www.wrekinpublishing.com
 
That seems to be working now. It wasn't before. I'm sure I was just doing
something slightly wrong. I have a couple more questions, if you can help
again.

1) How do I force the formatting of the spreadsheet I'm loading text data
into to retain its formatting. Currently, when the text is loaded, the
column widths adjust down to the largest value in a given column. I don't
want to have to add a bunch of spaces on the end of a column of data to
eliminate this column adjustment.
2) Why can I not establish a connection with a file that initially has no
data in it? The plan is that I will be writing data to a text file from a
web page then launching the excel spreadsheet from the web app. So I wanted
to create my spreadsheet template, my text files (which would be initially be
empty) then create the data connections. But it seems I can only do this if
the file actually has something in them. I suppose I can put data in them,
create the connections, then delete data back out....

Your help is much appreciated!

M.
 
Oh, I have a third question.

Let's say one of the columns of my spreadsheet is supposed to be a link to a
jpg file. Since I'll be writing the name of the file into a text file then
loading it into the spreadsheet when it opens, is there some way to format
the column in which jpgs will be listed so that it will be treated as a link
(not text) when the spreadsheet is loaded?

M.
 
Back
Top