Data Update (From one workbook to another)

eddy · Mar 24th, 2002, 08:16 AM

I need to update costs prices in a workbook from a data export from Sage Accounts.
I can save from Sage to an Excel workbook in the following format:-
A B
1 P/No Price
2 SL6002 £2.05
3 RT1005 £1.30
4 GR2005 £3.70

My Master Workbook has several columns including P/No and Price
I need to update the Price in this master Workbook from the saved Sage export data.
The common data in both workbooks is the P/no.
I need to have both workbooks open and then push a macro button to
automaticaly update the cost prices in the Master Workbook.
Can someone let me have the code for doing this?
Thanks Ted

Tom Schreiner · Mar 24th, 2002, 09:26 AM

Please give some more information...

Does the update replace the cost of certain P/No or is the new record simply appended to the end of the list?

When you are requesting code, give as much information as you possibly can. It will save you alot of time.

If you can edit your own code then disreguard the following:

Especially important in this case.
What is the name of your Master Workbook.
Include the full path.
What is the name of the sheet where the updates will take place?
What is the first row of data for P/No
Which column is P/No located?
Which column is Price located?

From what I can gather, you would want the price updated if the P/No already exists.
If the P/No is new then it would be added to the list and then sorted alphabetically?

Tom

[ This Message was edited by: TsTom on 2002-03-24 08:31 ]

eddy · Mar 24th, 2002, 11:20 AM

Hi Tom ...

Thanks for the offer of help. Answers as follows :-

Please give some more information...

Does the update replace the cost of certain P/No or is the new record simply appended to the end of the list?

I only need to update the items that have a
P/No already, so its just updating existing
P/No and prices. If the output dump from Sage contains P/Nos not aleady in the Master Workbook then these can be ignored so no append neccessary.

When you are requesting code, give as much information as you possibly can. It will save you alot of time.

OK point taken

If you can edit your own code then disreguard the following:

I can edit the code , no problem.

Especially important in this case.
What is the name of your Master Workbook.

Dont know yet but lets say c:Mast.xls
Include the full path.

Dont know yet but lets say c:Mast.xls

What is the name of the sheet where the updates will take place?

Say c:update.xls

What is the first row of data for P/No

Say C18

Which column is P/No located?

Say C

Which column is Price located?

Say E

From what I can gather, you would want the price updated if the P/No already exists.

Yes

If the P/No is new then it would be added to the list and then sorted alphabetically?

No, it can be ignored.

Hope this helps Tom

Rgds Ted

Tom Schreiner · Mar 24th, 2002, 02:37 PM

No problem
I have guests over.
Post if you found a solution from someone else. If not, I will send it to you tonight.

nisht · Mar 24th, 2002, 10:32 PM

Friend,
May following code work for you
Sub assignvalue()

Dim assign As String
Application.ScreenUpdating = False

Workbooks.Open Filename:="c:my documentsCallingFileName.xls"

Workbooks("CallingFileName").Activate
Worksheets("sheet1").Select
assign = Range("a9").Value
Workbooks("nameofyourbook").Activate
Worksheets("Sheet1").Select
Range(b1).Value = assign
Workbooks("CallingFileName").Close

Application.ScreenUpdating = True

End Sub

If you have difficulty contact me on

nisht@pexcel.com

nishith desai

Tom Schreiner · Mar 25th, 2002, 03:50 AM

I emailed the sheets to you.
If you did not get them, let me know...
Tom

eddy · Mar 26th, 2002, 02:27 AM

Thanks all for your help.
Ed

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 24th, 2002, 08:16 AM	#1
eddy Board Regular Join Date: Mar 2002 Posts: 473	I need to update costs prices in a workbook from a data export from Sage Accounts. I can save from Sage to an Excel workbook in the following format:- A B 1 P/No Price 2 SL6002 £2.05 3 RT1005 £1.30 4 GR2005 £3.70 My Master Workbook has several columns including P/No and Price I need to update the Price in this master Workbook from the saved Sage export data. The common data in both workbooks is the P/no. I need to have both workbooks open and then push a macro button to automaticaly update the cost prices in the Master Workbook. Can someone let me have the code for doing this? Thanks Ted

Mar 24th, 2002, 09:26 AM	#2
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Please give some more information... Does the update replace the cost of certain P/No or is the new record simply appended to the end of the list? When you are requesting code, give as much information as you possibly can. It will save you alot of time. If you can edit your own code then disreguard the following: Especially important in this case. What is the name of your Master Workbook. Include the full path. What is the name of the sheet where the updates will take place? What is the first row of data for P/No Which column is P/No located? Which column is Price located? From what I can gather, you would want the price updated if the P/No already exists. If the P/No is new then it would be added to the list and then sorted alphabetically? Tom [ This Message was edited by: TsTom on 2002-03-24 08:31 ]

Mar 24th, 2002, 11:20 AM	#3
eddy Board Regular Join Date: Mar 2002 Posts: 473	Hi Tom ... Thanks for the offer of help. Answers as follows :- Please give some more information... Does the update replace the cost of certain P/No or is the new record simply appended to the end of the list? I only need to update the items that have a P/No already, so its just updating existing P/No and prices. If the output dump from Sage contains P/Nos not aleady in the Master Workbook then these can be ignored so no append neccessary. When you are requesting code, give as much information as you possibly can. It will save you alot of time. OK point taken If you can edit your own code then disreguard the following: I can edit the code , no problem. Especially important in this case. What is the name of your Master Workbook. Dont know yet but lets say c:Mast.xls Include the full path. Dont know yet but lets say c:Mast.xls What is the name of the sheet where the updates will take place? Say c:update.xls What is the first row of data for P/No Say C18 Which column is P/No located? Say C Which column is Price located? Say E From what I can gather, you would want the price updated if the P/No already exists. Yes If the P/No is new then it would be added to the list and then sorted alphabetically? No, it can be ignored. Hope this helps Tom Rgds Ted

Mar 24th, 2002, 02:37 PM	#4
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	No problem I have guests over. Post if you found a solution from someone else. If not, I will send it to you tonight.

Mar 24th, 2002, 10:32 PM	#5
nisht Board Regular Join Date: Feb 2002 Location: Ahmedabad Gujarat Posts: 303	Friend, May following code work for you Sub assignvalue() Dim assign As String Application.ScreenUpdating = False Workbooks.Open Filename:="c:my documentsCallingFileName.xls" Workbooks("CallingFileName").Activate Worksheets("sheet1").Select assign = Range("a9").Value Workbooks("nameofyourbook").Activate Worksheets("Sheet1").Select Range(b1).Value = assign Workbooks("CallingFileName").Close Application.ScreenUpdating = True End Sub If you have difficulty contact me on nisht@pexcel.com nishith desai

Mar 25th, 2002, 03:50 AM	#6
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	I emailed the sheets to you. If you did not get them, let me know... Tom

Mar 26th, 2002, 02:27 AM	#7
eddy Board Regular Join Date: Mar 2002 Posts: 473	Thanks all for your help. Ed