retrieve data into a spreadsheet from a copy of that spreadsheetwhich is saved as its own xls file

N

Norbert

Hi,
second try to explain what I'm trying to do here.
For the last couple of years we are entering our Production
Specifications into an Excel spreadsheet (PRODUCTION TICKET.xls).
The last step we take after everything is filled in, is copying the
sheet and saving it (values only) under the quality number (e.g.: 4788.xls).

For certain reasons, we need now to get the data back into the original
spreadsheet (it could be on a separate sheet, but it must be in the
original file: PRODUCTION TICKET.xls).

On sheet PRODUCTION TICKET.xls/Prod.ticket, a consecutive code number is
given (entered manually) to every new specification (cell A16). A makro
makes a copy of that particular sheet (Paste special - values) and that
new spreadsheet then is saved under the number in A16, e.g.: 4788.xls).

I thought, I could have a copy of that original sheet PRODUCTION
TICKET.xls/Prod.ticket (in the same xls-file), with certain formulas,
which bring back the values from e.g.: 4788.xls, by me entering the
number 4788 into cell A16 of that copy of sheet PRODUCTION
TICKET.xls/Prod.ticket.

Is that possible??? Do I have to have formulas or rather VBA code.

The formulae should basically look like e.g.:
=[4788.xls]Prod.ticket!$AD$24, but the [4788.xls] should be linked to
cell A16, because it will change all the time.


Norbert
 
B

Bernie Deitrick

Norbert,

It would be easy to use a macro to do this, if you write your formulas in
the required cells in a particular way.

For example, in every cell where you want to pull data, use a formula like

="='[" & A16 & ".xls]Prod.ticket'!$AD$24"

This will return a string that looks like a formula, but isn't. With 4788
in cell A16, your cell will show

='[4788.xls]Prod.ticket'!$AD$24

(Note the two single quotes that you did not originally include...)

Then run this macro, which finds those formulas, converts the string to a
real formula, and then converts that formula to a value.

Sub ConvertToFormula()

Dim rngC As Range
For Each rngC In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
If Left(rngC.Formula, 6) = "=""='[""" Then
rngC.Formula = rngC.Value
rngC.Value = rngC.Value
End If
Next rngC

ActiveWorkbook.SaveAs Application.GetSaveAsFilename

End Sub


The macro will prompt you to save the file under a different name, since all
the linking formulas will be destroyed.


HTH,
Bernie
MS Excel MVP
 
R

ryguy7272

I'm not sure I understand what you want. But...take a look at this:
http://www.rondebruin.nl/tips.htm

Look under the section titled 'Copy/Paste/Merge examples'. See if there's
something in there that may help.

If you still need help, post back with specific questions.
 
N

Norbert

Bernie, thanks for trying to help me.
this formula didn't work, I could figure out it was because of the first
apostroph:

="='["& A16& ".xls]Prod.ticket'!$AD$24"

So, I changed it to:

="=["& A16& ".xls]Prod.ticket'!$AD$24"

I also changed the address in which my code nr. is entered:

="=["& 'Planning tickets'!$F$1& ".xls]Prod.ticket'!$AD$24"
which shows me in the cell the following:
=[7488.xls]Prod.ticket'!$AD$24 which sounds alright.

Once I start the makro, it hangs in your loop but not changing anything on the sheet.

If Left(rngC.Formula, 6) = "=""=[""" Then (here I also took out the apostroph!)
rngC.Formula = rngC.Value
rngC.Value = rngC.Value
End If

Can you see something strange?




Norbert,

It would be easy to use a macro to do this, if you write your formulas in
the required cells in a particular way.

For example, in every cell where you want to pull data, use a formula like

="='["& A16& ".xls]Prod.ticket'!$AD$24"

This will return a string that looks like a formula, but isn't. With 4788
in cell A16, your cell will show

='[4788.xls]Prod.ticket'!$AD$24

(Note the two single quotes that you did not originally include...)

Then run this macro, which finds those formulas, converts the string to a
real formula, and then converts that formula to a value.

Sub ConvertToFormula()

Dim rngC As Range
For Each rngC In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
If Left(rngC.Formula, 6) = "=""='[""" Then
rngC.Formula = rngC.Value
rngC.Value = rngC.Value
End If
Next rngC

ActiveWorkbook.SaveAs Application.GetSaveAsFilename

End Sub


The macro will prompt you to save the file under a different name, since all
the linking formulas will be destroyed.


HTH,
Bernie
MS Excel MVP


Hi,
second try to explain what I'm trying to do here.
For the last couple of years we are entering our Production Specifications
into an Excel spreadsheet (PRODUCTION TICKET.xls).
The last step we take after everything is filled in, is copying the sheet
and saving it (values only) under the quality number (e.g.: 4788.xls).

For certain reasons, we need now to get the data back into the original
spreadsheet (it could be on a separate sheet, but it must be in the
original file: PRODUCTION TICKET.xls).

On sheet PRODUCTION TICKET.xls/Prod.ticket, a consecutive code number is
given (entered manually) to every new specification (cell A16). A makro
makes a copy of that particular sheet (Paste special - values) and that
new spreadsheet then is saved under the number in A16, e.g.: 4788.xls).

I thought, I could have a copy of that original sheet PRODUCTION
TICKET.xls/Prod.ticket (in the same xls-file), with certain formulas,
which bring back the values from e.g.: 4788.xls, by me entering the number
4788 into cell A16 of that copy of sheet PRODUCTION
TICKET.xls/Prod.ticket.

Is that possible??? Do I have to have formulas or rather VBA code.

The formulae should basically look like e.g.:
=[4788.xls]Prod.ticket!$AD$24, but the [4788.xls] should be linked to cell
A16, because it will change all the time.


Norbert
Click to expand...
Click to expand...
 
N

Norbert

Hi Ryan,

if I had formulas in my spreadsheet to retrieve data from a certain
spreadsheet (which is not open), like this:
=[7488.xls]Prod.ticket'!$AD$24"

How do I change the number 7488 to the content of cell A16 e.g: 7265?
So, I enter 7265 into cell A16 and I want the formulas to bring up the
values which are saved in 7265.xls

Norbert
 
D

Don Guillett

Use an edit/replace macro tied to a worksheet_change event restricted to
that cell.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Norbert said:
Hi Ryan,

if I had formulas in my spreadsheet to retrieve data from a certain
spreadsheet (which is not open), like this:
=[7488.xls]Prod.ticket'!$AD$24"

How do I change the number 7488 to the content of cell A16 e.g: 7265?
So, I enter 7265 into cell A16 and I want the formulas to bring up the
values which are saved in 7265.xls

Norbert

I'm not sure I understand what you want. But...take a look at this:
http://www.rondebruin.nl/tips.htm

Look under the section titled 'Copy/Paste/Merge examples'. See if
there's
something in there that may help.

If you still need help, post back with specific questions.
Click to expand...
Click to expand...
 
N

Norbert

Hi Don,
I actually don't know how to do that. I can record a macro and do some
small changes but thats it.
Can't you give me a bit more details, please?
 
N

Norbert

I got only so far:

all my formulae have an entry [0000.xls]
I want to find/replace the 0000 with the content of cell A16, which
obviously changes.
That is my problem! The macro records exactly the content of A16 at the
moment I record it.
How do I have to change my code?

Range("A16").Select
ActiveCell.FormulaR1C1 = "4788"
Cells.Replace What:="0000", Replacement:="9725", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
 
D

Don Guillett

Try it this way. Right click sheet tab>view code>insert all of this>fire the
second macro one time
'========
Public whatfile
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Or _
Target.Address <> Range("h1").Address Then Exit Sub
MsgBox "Old file name was " & whatfile
Cells.Replace What:=whatfile, Replacement:=CStr(Target), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
whatfile = CStr(Target)
MsgBox "New file name is " & whatfile
End Sub
'==========
Sub setwhatfileFIRSTtimeONLY()
whatfile = "0000"
MsgBox whatfile
End Sub
'=========
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Norbert said:
I got only so far:

all my formulae have an entry [0000.xls]
I want to find/replace the 0000 with the content of cell A16, which
obviously changes.
That is my problem! The macro records exactly the content of A16 at the
moment I record it.
How do I have to change my code?

Range("A16").Select
ActiveCell.FormulaR1C1 = "4788"
Cells.Replace What:="0000", Replacement:="9725", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


Hi Don,
I actually don't know how to do that. I can record a macro and do some
small changes but thats it.
Can't you give me a bit more details, please?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

formula needed to extract data from another file 4
Unprotect sheet when Saving As 2
reading data from .xls file 1
Search , then copy and paste from multiple worksheets. VBA help 1
Lookup data from another excel files 1
Auto copy cell data from source sheet to another wrkbook sheet 2
Streamlining repetitive report 9
copy data from a list 1

Top