Home

Creating a Macro to shift data from word to excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1068933%22%20slang%3D%22en-US%22%3ECreating%20a%20Macro%20to%20shift%20data%20from%20word%20to%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1068933%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20anyone%20know%20how%20to%20assemble%20code%20to%20create%20a%20macro%20to%20shift%20data%20from%20word%20to%20excel.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20attached%20the%20purchase%20order%20word%20document%2C%20and%20PO%20log%20spreadsheet.%20The%20part%20of%20the%20purchase%20order%20that%20would%20need%20to%20be%20populated%20is%20the%20purchase%20order%20number%2C%20supplier%2C%20item%20number%2C%20part%20number%2C%20description%2C%20revision%20number%2C%20quantity%2C%20date%20required%2C%20and%20unit%20cost%20(extended%20cost%20is%20automatically%20calculated)%2C%20this%20information%20will%20go%20from%20the%20word%20document%2C%20to%20the%20excel%20spreadsheet%20in%20the%20appropriate%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAgain%2C%20all%20I%20need%20is%20information%20the%20purchase%20order%20document%20specified%20above%2C%20to%20be%20transferred%20to%20excel%2C%20by%20way%20of%20a%20button%2C%20or%20something%20more%20efficient.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1068933%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1069166%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20Macro%20to%20shift%20data%20from%20word%20to%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1069166%22%20slang%3D%22en-US%22%3EI%20don't%20have%20a%20direct%20answer%20to%20your%20request.%20Rather%20a%20question%3A%20is%20it%20necessary%20for%20some%20reason%20that%20the%20originating%20Purchase%20Order%20has%20to%20be%20in%20Word%3F%20There%20are%20Excel%20Forms%20that%20could%20serve%20the%20purpose%20also%20(I%20think).%3CBR%20%2F%3E%3CBR%20%2F%3EIt%20also%20would%20be%20helpful%20to%20know%20how%20many%20people%20are%20involved%20in%20this%20procedure%3A%20and%20whether%20they%20are%20part%20of%20your%20organization%20or%20are%20clients...%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20general%2C%20on%20behalf%20of%20those%20who%20ARE%20able%20to%20write%20VBA%20code%20and%20the%20like%2C%20I%20think%20you'd%20be%20more%20apt%20to%20get%20good%20help%20if%20you%20describe%20the%20situation%20a%20bit%20more%20fully%2C%20so%20that%20your%20specific%20request%20can%20be%20seen%20in%20its%20larger%20context.%20This%20would%20also%20enable%20them%20to%20suggest%20alternative%20solutions.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1070523%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20Macro%20to%20shift%20data%20from%20word%20to%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1070523%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20insight%20Mathetes.%20Unfortunately%20the%20purchase%20order%20is%20a%20controlled%20document%20that%20has%20always%20been%20in%20word%20format%2C%20and%20to%20change%20to%20excel%20would%20be%20difficult%20regulatory%20wise.%20The%20main%20people%20involved%20would%20be%20me%3A%20I%20do%20purchasing%20and%20inventory%20management.%20My%20supervisors%3A%20They%20handle%20the%20ad%20hoc%20supply%20chain%20management%20for%20our%20company.%20And%20our%20receiving%20department%3A%20who%20will%20receive%20in%20orders%2C%20and%20reconcile%20received%20amounts%20from%20ordered%20amounts.%20In%20that%20case%20I%20may%20just%20record%20a%20video%20showing%20exactly%20what%20I%20mean.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1070667%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20Macro%20to%20shift%20data%20from%20word%20to%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1070667%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F480499%22%20target%3D%22_blank%22%3E%40Dexlee46%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20situation%20you%20describe%20(especially%20given%20the%20sample%20documents%20you've%20provided)%20tell%20me%20that%20anything%20like%20this%20in%20Word%2FExcel%20is%20way%20above%20my%20level%20of%20expertise%20with%20these%20tools.%26nbsp%3B%20There%20may%20be%20others%20here%20in%20this%20Forum%20who%20can%20help%3B%20but%20the%20scope%20of%20what%20you've%20described%20is%20well%20beyond%20most%20applications%26nbsp%3B%20suitable%20for%20Microsoft%20Office%20suite.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20fact%2C%20if%20I%20may%20speak%20frankly%20(as%20one%20who%20worked%20as%20Director%20of%20a%20major%20corporation's%20Hr%20and%20Payroll%20database)%20I%20think%20that%20what%20you%20have%20warrants%20a%20solution%20tailored%20for%20supply%20chain%20management.%20Word%20and%20Excel%20are%20wonderful%20programs%2C%20and%20they%20may%20have%20served%20you%20well%20when%20your%20company%20was%20smaller%2C%20but%20there%20are%20practical%20limits.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20were%20you%2C%20I'd%20be%20recommending%20that%20you%20implement%20a%20Request%20for%20Proposal%20(or%20whatever%20procedure%20you%20all%20use)%20from%20major%20suppliers%20of%20supply%20chain%20software.%20Here%20are%20several%20links%20to%20get%20you%20started.%3C%2FP%3E%3CUL%3E%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fwww.capterra.com%2Fsem-compare%2Fsupply-chain-management-software%3Fgclid%3DCjwKCAiAluLvBRASEiwAAbX3Gbz7NZz7LTWJUeha-7kxxvJt0Lrl52o8GynA1e0vsamDtN8hxnZ-CxoC7JMQAvD_BwE%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.capterra.com%2Fsem-compare%2Fsupply-chain-management-software%3Fgclid%3DCjwKCAiAluLvBRASEiwAAbX3Gbz7NZz7LTWJUeha-7kxxvJt0Lrl52o8GynA1e0vsamDtN8hxnZ-CxoC7JMQAvD_BwE%3C%2FA%3E%3C%2FLI%3E%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fpmo.selecthub.com%2Fscm-pricing-report%2F%3Fcampaignid%3D973759891%26amp%3Badgroupid%3D51015318911%26amp%3Bcreative%3D322778496693%26amp%3Bkeyword%3Dsupply%2520chain%2520software%2520companies%26amp%3Bgclid%3DCjwKCAiAluLvBRASEiwAAbX3Ga2nh5_9zcg4gqoMUthPjGHeGptt1E8TE6_PKJPWjqfsRLE5U5SfIRoCjQQQAvD_BwE%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fpmo.selecthub.com%2Fscm-pricing-report%2F%3Fcampaignid%3D973759891%26amp%3Badgroupid%3D51015318911%26amp%3Bcreative%3D322778496693%26amp%3Bkeyword%3Dsupply%2520chain%2520software%2520companies%26amp%3Bgclid%3DCjwKCAiAluLvBRASEiwAAbX3Ga2nh5_9zcg4gqoMUthPjGHeGptt1E8TE6_PKJPWjqfsRLE5U5SfIRoCjQQQAvD_BwE%3C%2FA%3E%3C%2FLI%3E%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fwww.supplychaindigital.com%2Ftechnology%2Ftop-10-supply-chain-management-software-companies%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.supplychaindigital.com%2Ftechnology%2Ftop-10-supply-chain-management-software-companies%3C%2FA%3E%3C%2FLI%3E%3CLI%3E%26nbsp%3B%3C%2FLI%3E%3C%2FUL%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1072186%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20Macro%20to%20shift%20data%20from%20word%20to%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1072186%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20are%20currently%20undergoing%20a%20ERP%20implementation.%20I%20was%20just%20looking%20for%20quick%20fix%2C%20as%20the%20system%20will%20not%20be%20implemented%20fully%20until%20sometime%20next%20year.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20input.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1072622%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20Macro%20to%20shift%20data%20from%20word%20to%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1072622%22%20slang%3D%22en-US%22%3Eget%20doc%20sheet%20converted%20to%20excel%2C%20copy%20table%20from%20doc%20and%20paste%20into%20new%20sheet%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1074949%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20Macro%20to%20shift%20data%20from%20word%20to%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1074949%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F480499%22%20target%3D%22_blank%22%3E%40Dexlee46%3C%2FA%3E%26nbsp%3Bif%20your%20doc%20contains%20bookmarks%2C%20you%20should%20be%20able%20to%20search%20for%20those%20and%20copy%2Fpaste%20with%20vba.%20Or%20something%20like%20this%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fstackoverflow.com%2Fquestions%2F3567441%2Fextract-data-from-word-document-to-an-excel-spreadsheet%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F3567441%2Fextract-data-from-word-document-to-an-excel-spreadsheet%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Dexlee46
Contributor

Hello,

 

Would anyone know how to assemble code to create a macro to shift data from word to excel. 

 

So I attached the purchase order word document, and PO log spreadsheet. The part of the purchase order that would need to be populated is the purchase order number, supplier, item number, part number, description, revision number, quantity, date required, and unit cost (extended cost is automatically calculated), this information will go from the word document, to the excel spreadsheet in the appropriate cells.

 

Again, all I need is information the purchase order document specified above, to be transferred to excel, by way of a button, or something more efficient. 

 

Thank you!

 

 

6 Replies
Highlighted
I don't have a direct answer to your request. Rather a question: is it necessary for some reason that the originating Purchase Order has to be in Word? There are Excel Forms that could serve the purpose also (I think).

It also would be helpful to know how many people are involved in this procedure: and whether they are part of your organization or are clients...

In general, on behalf of those who ARE able to write VBA code and the like, I think you'd be more apt to get good help if you describe the situation a bit more fully, so that your specific request can be seen in its larger context. This would also enable them to suggest alternative solutions.
Highlighted

@mathetes 

 

Thank you for the insight Mathetes. Unfortunately the purchase order is a controlled document that has always been in word format, and to change to excel would be difficult regulatory wise. The main people involved would be me: I do purchasing and inventory management. My supervisors: They handle the ad hoc supply chain management for our company. And our receiving department: who will receive in orders, and reconcile received amounts from ordered amounts. In that case I may just record a video showing exactly what I mean.

Highlighted

@Dexlee46 

 

The situation you describe (especially given the sample documents you've provided) tell me that anything like this in Word/Excel is way above my level of expertise with these tools.  There may be others here in this Forum who can help; but the scope of what you've described is well beyond most applications  suitable for Microsoft Office suite.

 

In fact, if I may speak frankly (as one who worked as Director of a major corporation's Hr and Payroll database) I think that what you have warrants a solution tailored for supply chain management. Word and Excel are wonderful programs, and they may have served you well when your company was smaller, but there are practical limits.

 

If I were you, I'd be recommending that you implement a Request for Proposal (or whatever procedure you all use) from major suppliers of supply chain software. Here are several links to get you started.

Highlighted

@mathetes 

 

We are currently undergoing a ERP implementation. I was just looking for quick fix, as the system will not be implemented fully until sometime next year.

 

Thank you for the input.

Highlighted
get doc sheet converted to excel, copy table from doc and paste into new sheet
Highlighted

@Dexlee46 if your doc contains bookmarks, you should be able to search for those and copy/paste with vba. Or something like this: https://stackoverflow.com/questions/3567441/extract-data-from-word-document-to-an-excel-spreadsheet

 

Related Conversations
Formula to calculate between "Closing Balances"
calof1 in Excel on
2 Replies
Userform-Transferred Data must not be editable
Mr_Buttons in Excel on
0 Replies
need urgent help for excel
talikne610 in Excel on
1 Replies
Excel no longer capable of Relative links
GoodallS in Excel on
0 Replies
Grouping by dates in Excel?
MMH11 in Excel on
0 Replies
Creating Pie charts.......
dougler2020 in Excel on
4 Replies