Drawing data from different filenames

[Del]

Any help would be appreciated on the following problem:

Each day I need to export a file of figures from another source and save
into an Excel file called EXPORT.xls.

I then have 3 different spreadsheets drawing various data from this one file.

I use the following formula to draw the data across:

=('\\Folder1\Folder2\[EXPORT.xls]EXPORT'!B1]

The problem I have is that tomorrow, todays file will be overwritten by the
new export file.

This isn't a huge problem as the figures are accumulated so I am getting the
correct figures, but what I would like is to save each days export file using
the following format:

ddmmyy.xls

Then when a user opens up their file they can select which 'day' to link
their spreadsheet to either by typing in the filename or by selecting it from
a list, rather than just having the most up-to-date figures, so that at any
given time they can 'look back' and view that days figures.

Hope this makes sense and again many thanks in advance for any help given.

PS All files were created using Excel 2003 but will viewed by others using
Excel 2002.

 

[Kou Vang]

If the source of the data is the same, then I would say that you could easily
write a macro, save it to a button and run it at the end of each day. Write
code to find the data from these sources, then export the file as Date(). IE:

ActiveWorkbook.SaveAs Filename:=Date() & ".xls", _
Fileformat:=xlWorkbookNormal
Debug.Print Filename
ActiveWorkbook.Close

Test these steps slowly by stepping through and looking at the Debug.Print
of the what comes out in the Immediate Window. Hope this is a good start!

 

[Del]

Hi Kou

Thanks for your reply.

Unfortunately tthe source of the data wont be the same.

I want to save the export file as that days date ie

ddmmyy.xls

010206.xls for wednesdays export file
020206.xls for yesterdays export file
030206.xls for todays export file

and so on.

I then have 3 other spreadsheets which draw data from one of the export
spreadsheets. (these 3 spreadsheets wont change their filename)

When a user opens one the 3 other spreadsheets, they can select which of the
export spreadsheets to link to by either keying in the filename in a cell or
by selecting it from a drop-down list.

ie

key in 010206 in a cell and their spreadsheet will link to/draw data from
010206.xls
key in 020206 in a cell and their spreadsheet will link to/draw data from
020206.xls

Hope this makes it a bit clearer.

many thanks

If the source of the data is the same, then I would say that you could easily
write a macro, save it to a button and run it at the end of each day. Write
code to find the data from these sources, then export the file as Date(). IE:

ActiveWorkbook.SaveAs Filename:=Date() & ".xls", _
Fileformat:=xlWorkbookNormal
Debug.Print Filename
ActiveWorkbook.Close

Test these steps slowly by stepping through and looking at the Debug.Print
of the what comes out in the Immediate Window. Hope this is a good start!

Any help would be appreciated on the following problem:

Each day I need to export a file of figures from another source and save
into an Excel file called EXPORT.xls.

I then have 3 different spreadsheets drawing various data from this one file.

I use the following formula to draw the data across:

=('\\Folder1\Folder2\[EXPORT.xls]EXPORT'!B1]

The problem I have is that tomorrow, todays file will be overwritten by the
new export file.

This isn't a huge problem as the figures are accumulated so I am getting the
correct figures, but what I would like is to save each days export file using
the following format:

ddmmyy.xls

Then when a user opens up their file they can select which 'day' to link
their spreadsheet to either by typing in the filename or by selecting it from
a list, rather than just having the most up-to-date figures, so that at any
given time they can 'look back' and view that days figures.

Hope this makes sense and again many thanks in advance for any help given.

PS All files were created using Excel 2003 but will viewed by others using
Excel 2002.

 

[Dave Peterson]

You could use =indirect() to get to the workbook that has the data. But
=indirect() won't work with closed workbooks.

So your adjusted formulas won't work if the "sending" workbooks are closed.

But you could keep a dummy "export.xls" file out there. Then open the workbook
that has links back to that dummy "export.xls" file.

Then the first thing you do is:
Edit|links|Change source
to get the correct workbook.

Another alternative...

Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed
workbook.

You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/
Look for pull.zip

Hi Kou

Thanks for your reply.

Unfortunately tthe source of the data wont be the same.

I want to save the export file as that days date ie

ddmmyy.xls

010206.xls for wednesdays export file
020206.xls for yesterdays export file
030206.xls for todays export file

and so on.

I then have 3 other spreadsheets which draw data from one of the export
spreadsheets. (these 3 spreadsheets wont change their filename)

When a user opens one the 3 other spreadsheets, they can select which of the
export spreadsheets to link to by either keying in the filename in a cell or
by selecting it from a drop-down list.

ie

key in 010206 in a cell and their spreadsheet will link to/draw data from
010206.xls
key in 020206 in a cell and their spreadsheet will link to/draw data from
020206.xls

Hope this makes it a bit clearer.

many thanks

If the source of the data is the same, then I would say that you could easily
write a macro, save it to a button and run it at the end of each day. Write
code to find the data from these sources, then export the file as Date(). IE:

ActiveWorkbook.SaveAs Filename:=Date() & ".xls", _
Fileformat:=xlWorkbookNormal
Debug.Print Filename
ActiveWorkbook.Close

Test these steps slowly by stepping through and looking at the Debug.Print
of the what comes out in the Immediate Window. Hope this is a good start!

Any help would be appreciated on the following problem:

Each day I need to export a file of figures from another source and save
into an Excel file called EXPORT.xls.

I then have 3 different spreadsheets drawing various data from this one file.

I use the following formula to draw the data across:

=('\\Folder1\Folder2\[EXPORT.xls]EXPORT'!B1]

The problem I have is that tomorrow, todays file will be overwritten by the
new export file.

This isn't a huge problem as the figures are accumulated so I am getting the
correct figures, but what I would like is to save each days export file using
the following format:

ddmmyy.xls

Then when a user opens up their file they can select which 'day' to link
their spreadsheet to either by typing in the filename or by selecting it from
a list, rather than just having the most up-to-date figures, so that at any
given time they can 'look back' and view that days figures.

Hope this makes sense and again many thanks in advance for any help given.

PS All files were created using Excel 2003 but will viewed by others using
Excel 2002.

 

[Del]

Hi Dave

Thanks for your reply.

I am just back from a few days off and haven't had a chance to look at this
yet.

Will give it a go in the next day or 2 and let you know how I get on.

Thanks again.

You could use =indirect() to get to the workbook that has the data. But
=indirect() won't work with closed workbooks.

So your adjusted formulas won't work if the "sending" workbooks are closed.

But you could keep a dummy "export.xls" file out there. Then open the workbook
that has links back to that dummy "export.xls" file.

Then the first thing you do is:
Edit|links|Change source
to get the correct workbook.

Another alternative...

Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed
workbook.

You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/
Look for pull.zip

Hi Kou

Thanks for your reply.

Unfortunately tthe source of the data wont be the same.

I want to save the export file as that days date ie

ddmmyy.xls

010206.xls for wednesdays export file
020206.xls for yesterdays export file
030206.xls for todays export file

and so on.

I then have 3 other spreadsheets which draw data from one of the export
spreadsheets. (these 3 spreadsheets wont change their filename)

When a user opens one the 3 other spreadsheets, they can select which of the
export spreadsheets to link to by either keying in the filename in a cell or
by selecting it from a drop-down list.

ie

key in 010206 in a cell and their spreadsheet will link to/draw data from
010206.xls
key in 020206 in a cell and their spreadsheet will link to/draw data from
020206.xls

Hope this makes it a bit clearer.

many thanks

If the source of the data is the same, then I would say that you could easily
write a macro, save it to a button and run it at the end of each day. Write
code to find the data from these sources, then export the file as Date(). IE:

ActiveWorkbook.SaveAs Filename:=Date() & ".xls", _
Fileformat:=xlWorkbookNormal
Debug.Print Filename
ActiveWorkbook.Close

Test these steps slowly by stepping through and looking at the Debug.Print
of the what comes out in the Immediate Window. Hope this is a good start!

:

Any help would be appreciated on the following problem:

Each day I need to export a file of figures from another source and save
into an Excel file called EXPORT.xls.

I then have 3 different spreadsheets drawing various data from this one file.

I use the following formula to draw the data across:

=('\\Folder1\Folder2\[EXPORT.xls]EXPORT'!B1]

The problem I have is that tomorrow, todays file will be overwritten by the
new export file.

This isn't a huge problem as the figures are accumulated so I am getting the
correct figures, but what I would like is to save each days export file using
the following format:

ddmmyy.xls

Then when a user opens up their file they can select which 'day' to link
their spreadsheet to either by typing in the filename or by selecting it from
a list, rather than just having the most up-to-date figures, so that at any
given time they can 'look back' and view that days figures.

Hope this makes sense and again many thanks in advance for any help given.

PS All files were created using Excel 2003 but will viewed by others using
Excel 2002.