Copy & paste Xl2000 to wd200 with veryhidden sheets

[Codman]

Realizing that xlVeryHidden Sheets are difficult, if not impossible to access,I have a problem. I can copy a range from Excel to Word with the Xl sheet visible but not when very hidden. However, I can see and manually paste the copied data from the clipboard to word. Is there a way to do this without using the Selection.paste command as this seems not to work? Also how would I copy a dynamic range. One with a varying number of rows. Using IsEmpty I think, but am not sure?


With Sheet3.Range("Q1:U20&quot.Copy
End With

WordDoc.Selection.Goto What:=wdGoToBookmark, Name:="QTABLE"
With WordApp.Selection
.TypeParagraph
.Paste
End With

 

[SkipVought]

Codman,

How is your dynamic range to be defined?

Is it ALL contiguous data on a sheet from a specified reference?

Is it all data on a sheet?

Is it something else?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Codman]

Hi Skip,

The data is a sheet range five constant columns by a varying number of rows from 1 through to twenty. I have the table referenced Q1:U20. Does this help?

Codman

 

[SkipVought]

xlApp.xlWkbk.xlSht.Cells(1,"Q").CurrentRegion.Copy

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[combo]

Hi Codman,
seems that simple Paste does not work. I managed to copy from very hidden worksheet with:

[tt]Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Add
wdApp.Visible = True
Worksheets(1).Range("A1:B2&quot.Copy
wdDoc.Paragraphs(1).Range.PasteSpecial DataType:=wdPasteRTF
Set wdDoc = Nothing
Set wdApp = Nothing[/tt]

combo

 

[Codman]

Hi Skip/Combo,

I tried both approaches but could only get Combo's to work. I modified the code as follows as pasting a RTF required some post formatting in Word.

With Sheet3.Range("Q1:U20&quot.Copy
End With


WordDoc.ActiveWindow.Selection.Goto What:=wdGoToBookmark, Name:="QTABLE"
With WordApp.Selection
.TypeParagraph
.PasteSpecial DataType:=wdPasteRTF
.Tables(1).Select

WordApp.Selection.Cells.PreferredWidth = CentimetersToPoints(1.7)
End With

The intesting thing is that setting the column width works fine but only upto 1.7 point. After that I get a starnge error message Saying the remote server is not known or available. If I then go into Word and format manually, I can get the desired 2.1Cm width. Any ideas?

Pete

 

[SkipVought]

Pete,

I was coding a general solution. I assume that you have done a CreateObject for Excel, so you have established an application object and workbook. So I assume that the Sheet3 object has a workbook parent object

Sheet3.Range("Q1").CurrentRegion.Copy

will do what you need AS LONG AS the desired range is contiguous AND isolated from other data.

Skip,

http://www.TheOfficeExperts.com

 

[Codman]

Skip,

Please excuse my ignorance but I'm not sure what you mean by a worksheet parent. Surely this is the activeWorkbook? I changed code as follows to suit:-

ActiveWorkbook.Sheets3.Cells(1, Q).CurrentRegion.Copy

WordDoc.ActiveWindow.Selection.Goto What:=wdGoToBookmark, Name:="QTABLE"
With WordApp.Selection
.TypeParagraph
.PasteSpecial DataType:=wdPasteRTF
.Tables(1).Select
WordDoc.ActiveWindow.Selection.Tables(1).AutoFitBehavior (wdAutoFitWindow)
End With

I have established a name for the sheet so I could change this to ActiveWorkbook.WorkSheets("Reports&quot.Activate etc, but again this stalls on initiation of the CurrentRegion command.

The data is NOT on it's own on the sheet, but again NOt close to any other ranges and the data is contiguous. How isolated does it have to be?

Sorry for all the questions

Pete

 

[SkipVought]

ActiveWorkbook.Sheets3.Cells(1, "Q").CurrentRegion.Copy

Don't forget the quotes.

Skip,

http://www.TheOfficeExperts.com

 

[Codman]

Hi Skip,

Worked great! I should read the help file more often as well. It's there, but buried deep.

Thnaks for your help and have a great day

Pete