PDA

View Full Version : Solved: Excel to Word: A few issues on the Excel part of things



itipu
08-05-2008, 12:30 AM
So I have a "Folder" with an Excel report sheet - "EXAMPLE.xls". I also have a "Sample.xls" outside of that folder which is my macro. I open it up, click on a white button there and it asks to select a folder, I select "Folder" and it asks user to select which files out of that folder to transform into a Word document... the whole thing works apart from 2 things....

1) "In Module2" First I check for files in my Folder and assign listboxes their names. Unfortunately I can't figure out how to do the same thing without OPENING & closing the report file itself:

For lngFileIndex = lngLwrBnd_c To fs.FoundFiles.Count
Set wbcrnt = Excel.Workbooks.Open(fs.FoundFiles(lngFileIndex), False, _
False, Password:="foo")
UserForm.ListBox1.AddItem wbcrnt.Name
wbcrnt.Close
Next

2) "In UserForm" When I copy Range("A21:I32") from Excel to Word its just too wide and doesn't fit the page (tried landscape no luck either) how can I resize or what else can be done?

wsOne.Range("A21:I32").CopyPicture Appearance:=xlScreen, Format:=xlPicture
.TypeText Text:="Project Major Reviews & Dates"
.TypeParagraph
wrdApp.Selection.Paste


Thanks a lot for all your help!

Much appreciated as always!

Bob Phillips
08-05-2008, 01:34 AM
Point 1



For lngFileIndex = lngLwrBnd_c To fs.FoundFiles.Count
NextFile = Mid(fs.FoundFiles(lngFileIndex), InStrRev(fs.FoundFiles(lngFileIndex), "\") + 1)
UserForm.ListBox1.AddItem wbcrnt.Name
Next

itipu
08-05-2008, 04:10 PM
Thanks a lot, Part 2 I solved by pasting as DataType:=wdPasteMetafilePicture

Cheers!

Mike