Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I can't seem to find a way to solve the following: I want to extract a certain text from a textbox using CopyTextToClipboard in a macro function, and then paste it to a predetermined cell in an excel file.
Instead of placing this text into the cell, it pastes all the lines of the text to seperate rows (overwriting all the text in the rows under the predetermined cell...).
Here is my macro code (so far), with the described part in bold text:
'***************************************************
sub export123toexcel
set oXL=CreateObject("Excel.Application")
oXL.visible=True
oXL.Workbooks.Add
Set oSH = oXL.ActiveSheet
'load table objects:
'load table object 1:
Set obj = ActiveDocument.GetSheetObject("CH_table1export")
obj.CopyTableToClipboard True
'location in excel sheet:
oSH.Range("A1:A5").Select
oSH.Paste
set obj=Nothing
'load table object 2:
Set obj = ActiveDocument.GetSheetObject("CH_table2export")
obj.CopyTableToClipboard True
'location in excel sheet:
oSH.Range("A6:A10").Select
oSH.Paste
set obj=Nothing
'load table object 3:
Set obj = ActiveDocument.GetSheetObject("CH_table3export")
obj.CopyTableToClipboard True
'location in excel sheet:
oSH.Range("A11:A15").Select
oSH.Paste
set obj=Nothing
'end load table objects
'delete superfluous title rows in excel export:
oSH.Rows("5:5").Delete
oSH.Rows("5:5").Delete
oSH.Rows("8:8").Delete
oSH.Rows("8:8").Delete
oSH.Rows("11:11").Delete
'replace certain cells with text
Set obj = ActiveDocument.GetSheetObject("TX26")
obj.CopyTextToClipboard
oSH.Range("P2:P2").Select
oSH.Pastespecial
oSH.Columns("P").ColumnWidth=40
set obj=Nothing
oSH.Cells.EntireColumn.AutoFit
'oSH.Cells.EntireRow.AutoFit
'selection in completed excel export:
oSH.Range("A2:P10").Select
'cleanup:
set oSH=Nothing
set oXL=Nothing
end sub
'*************************************************************
this results in:
As seen in the macro code, it concerns the text in column P, cell P2.
What I want to have is the following:
How can I get the macro to paste the text in this way?
Thanks in advance,
Marius