#1
|
|||
|
|||
Export values from one embedded worksheet to another embedded worksheet
Greetings from an Excel Nub.
I have an outdated Word form with an embedded excel spreadsheet. I need to move the values from the outdated form to an embedded excel spreadsheet in an updated Word form. See Old.jpg and New.jpg Part of problem is the row headings in the old form do not align or match with the row headings in the new form. I have cobbled together the following code which is working "reasonably" well: (will post in follow up because of length restrictions) Questions: 1. Curious if anyone has suggestions for improving, simplifying, or abandoning this course altogether and doing this a different way. 2. You will see, I've added several "Kill time" features. During development and testing, I would frequently get the following error messages: (See Err 1.jpg and Err 2.jpg) which are much less frequent with these hard coded delays. Anyone have ideas or suggestions as to why this happens and how to avoid completely? Thanks |
#2
|
|||
|
|||
Here is the code:
Code:
Option Explicit #If VBA7 Then Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) #Else Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) #End If Sub UpdateNewBook() Dim oILS As InlineShape Dim oApp As Excel.Application Dim lngIndex As Long Dim lngRow As Long, lngCol As Long, lngPause As Long Dim varUR Dim oRng As Excel.Range Dim oDoc1 As Document Dim oDoc2 As Document 'Define the source and target documents. 'The two documents are open. The source document is the active document. If Documents(1).Name = "Target.docm" Then Set oDoc2 = Documents(1) Else Set oDoc2 = Documents(2) End If Set oDoc1 = ActiveDocument Set oILS = oDoc1.InlineShapes(1) On Error GoTo Err_Handler 'Write the row heading column and column data to a array. With oILS .OLEFormat.DoVerb wdOLEVerbHide 'Launch Excel Set oApp = .OLEFormat.Object.Application With oApp varUR = .Workbooks(1).Worksheets(1).UsedRange.Value 'Close Excel .Quit End With End With 'Kill some time Sleep 500 Set oILS = Nothing: Set oApp = Nothing DoEvents Sleep 500 DoEvents Set oILS = oDoc2.InlineShapes(1) With oILS 'Kill some more time. For lngPause = 1 To 5 DoEvents Sleep 100 DoEvents Next lngPause .OLEFormat.DoVerb wdOLEVerbHide 'Kill some more time For lngPause = 1 To 5 DoEvents Sleep 100 DoEvents Next lngPause Set oApp = .OLEFormat.Object.Application With oApp Set oRng = .Workbooks(1).Worksheets(1).Columns(1) For lngIndex = 1 To UBound(varUR) On Error Resume Next 'Find the row heading in the target sheet that matches the row heading in the source sheet lngRow = .WorksheetFunction.Match(varUR(lngIndex, 1), oRng, 0) If Err.Number = 0 Then For lngCol = 2 To UBound(varUR, 2) 'Write the values in the correct row .Workbooks(1).Worksheets(1).Cells(lngRow, lngCol).Value = varUR(lngIndex, lngCol) Next lngCol Else Err.Clear End If Next lngIndex .Workbooks(1).Save .Quit End With End With lbl_Exit: Set oApp = Nothing Exit Sub Err_Handler: If Not oApp Is Nothing Then oApp.Quit End If MsgBox Err.Number & " - " & Err.Description End Sub |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Link UserForm checkbox to corresponding shape on one worksheet and copy to 'template' worksheet | kiwimtnbkr | Excel Programming | 23 | 10-08-2020 02:32 AM |
Export Embedded Docs inside Word | dhen21dx | Word | 2 | 03-25-2019 01:58 AM |
How can I copy a worksheet and paste values to another new workbook ? | DBenz | Excel | 1 | 01-26-2019 07:03 AM |
Export embedded .txt or .csv file from Word bookmark to Access table field | eric.okeefe | Word VBA | 4 | 08-29-2017 09:31 AM |
Create Several PowerPoint Charts From One Embedded Excel worksheet | Galapagos15 | PowerPoint | 0 | 10-30-2015 06:19 AM |