Macro code to extract data from spreadsheet into 3 word documents

**nurseydiamond** · 08-08-2010, 06:53 PM

Hello all,

I have data on a spreadsheet that changes daily. I need macros to extract certain peices of data from the spreadsheet to open a word document (3 of them) which will put the data into the format of a macro code that I will be pasting into another program. I am attaching an example of the spreadsheet. I need help getting the data from the spreadsheet to the word documents in the macro formats...and the data will change every day. I will then copy the data that has populated the word documents and paste the code into the macros in the other program each day & run it. Here are what the 3 macros look like - (this is the format I need the data from the spreadsheet to populate into - I recorded one of each, but this data will change according to the data on the spreadsheet, so I want the spreadsheet to populate a word document in the following formats, so I can then paste the new data into the recorded macro & re-run it daily):

Macro 1:

Sess0.Screen.Sendkeys("REV<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("2<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("<KP 0>< KP 0>< KP 0><Keypad 4><Keypad 3><Ctrl+M>") ß this is data from column A	that needs to be extracted&put in this format -I do not need the"1Y," part of this cell.
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("H<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("<Keypad 4><Keypad 9><Keypad 1><KP 0><Ctrl+M>")	ß this is the date from column K
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("<KP 0><Keypad 1><KP 0><Keypad 2><Keypad 2><Ctrl+M>")	ßnext data from column A
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("H<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("<Keypad 4><Keypad 9><Keypad 1><KP 0><Ctrl+M>") ß date	

(etcetera for each cell with data in column A)


End Sub

Macro 2:

' This section of code contains the recorded events
	Sess0.Screen.Sendkeys("00043<Ctrl+M>") ß from column A, don’t need the “1Y” or “HS”
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("C<Ctrl+M>")	ßColumn E
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("0<Ctrl+M>")	ßColumn F
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("34<Ctrl+M>")	ßColumn G
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("Y<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("01022<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("P<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("7<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("7<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("Y<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("00245<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("C<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("0<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("6<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("Y<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("<Ctrl+[>E")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
 
(etcetera for each cell with data in column A)

	System.TimeoutValue = OldSystemTimeout
End Sub

Macro 3:

' This section of code contains the recorded events
	Sess0.Screen.Sendkeys("<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("123549876<Ctrl+M>")	ß static data
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("<KP 0><KP 0><KP 0><Keypad 4><Keypad 3><Ctrl+M>") ßdata from A
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("4910<Ctrl+M>")	ß Date
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("274<Ctrl+M>")	ß static data – will not change
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("111<Ctrl+M>")	ß static data
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("30<Ctrl+M>")	  ß static data
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("<KP 0><Keypad 1><KP 0><Keypad 2><Keypad 2><Ctrl+M>")	ß next A data
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("4910<Ctrl+M>") ß date
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("30<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("<KP 0><KP 0><Keypad 2><Keypad 4><Keypad 5><Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("41010<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("<Ctrl+M>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("30<Ctrl+M>")	
	
 (etcetera for each cell with data in column A)

	System.TimeoutValue = OldSystemTimeout
End Sub

Any help anyone can provide will be greatly appreciated - thanks in advance!!