copying from word to excel

[namrata_p]

Hi,

I try to copy data from word documents to excel sheet and since the limit of an excel sheet is just for 65536 lines, my macros fail everytime it exceeds the limit.

Can you please help me solving this issue? i'll be very thankful.

[opus]

If you really need to read in such large files totally, I'd use a counter to check wether to Excel limit is hit or not, if yes you could start a new Excel-file or disregard the rest or .................

[namrata_p]

If you really need to read in such large files totally, I'd use a counter to check wether to Excel limit is hit or not, if yes you could start a new Excel-file or disregard the rest or .................

i know this is what i'll have to do but it selects the entire word document in one stratch. i need a solution so that we can choose certain number of pages from word document using macros.

[opus]

Try to use something like this (page 1):

Dim objPage As Page
Set objPage = ActiveDocument.ActiveWindow _
    .Panes(1).Pages.Item(1)

[namrata_p]

Try to use something like this (page 1):

Dim objPage As Page
Set objPage = ActiveDocument.ActiveWindow _
    .Panes(1).Pages.Item(1)

its not the solution for my problem. my problem is a little piculiar i can say. we know what to do but not how to. i do not know how to add a new worksheet in process of copying data from word to excel.

even if i add a new excel sheet before copying data in the workbook, it doesn't solve my purpose as i'm not able to point towards it as a extention of the previous worksheet.

[opus]

How would you save/close the actual workbook if you're not getting an error( i.e when using a small .doc file)? Poste the code you have so far!

[namrata_p]

i cant send it.. its my company's property and we are not supposed to give it to anyone....

[Ecniv]

Open an Xls file.
Read in a page (held in an array? macro decides?)
Work out how many lines/rows it needs.
Compare to the running total on the current worksheet
if space, then paste.
If not enough space,
- start a new sheet (add it in),
- change its name and
- reset the running total
- paste the data.
Repeat until no more pages to add.

[namrata_p]

Open an Xls file.
Read in a page (held in an array? macro decides?)
Work out how many lines/rows it needs.
Compare to the running total on the current worksheet
if space, then paste.
If not enough space,
- start a new sheet (add it in),
- change its name and
- reset the running total
- paste the data.
Repeat until no more pages to add.

since the word document gets data from day to day transactions and so determining how many lines are there is impossible... no one can keep counting it...

can i get a way so that i can select from a page no. in word to another page number?? is that possible not to select the entire document at a time and so the job?

[Ecniv]

Yes - in word you use ranges. so as long as you can get the start of the range you want and the end of it, you can look at only a section.

I was asking about lines because I was confirming whether each line is a row in the excel sheet. If so and each line is return charactered (paragraph in word - carriage return and linefeed I think) then you can use the paragraphs collection on the open document.

If you already know a total rows imported you can use the paragraphs collections to import only the newer lines... and also to check when to move to a new sheet.

[namrata_p]

hi..
please take it as example,

assume i've got a word document having 10 pages and i wanna read n copy page 1 & 2 to an excel sheet and after copying it i wanna copy page 5 & 6 in the other sheet how would i proceed??

if possible , using sendkeys.

[Ecniv]

ActiveDocument.ActiveWindow.Panes(1).Pages(1)

This goes to the page. But, reading through the help files, there doesn't appear to be a range object associated with this. There was a 'breaks' collection which does allow you to get to a range.

So you could :

ActiveDocument.ActiveWindow.Panes(1).Pages(1).breaks(1).range.start
ActiveDocument.ActiveWindow.Panes(1).Pages(1).breaks(ActiveDocument.ActiveWindow.Panes(1).Pages(1).breaks.count).range.end

To get the range for a page.
Then loop through that range. You'll need to get creative with how you want to get the data etc...
If you use a couple of variables as references the above shortens to:

dim pg as page

set pg = ActiveDocument.ActiveWindow.Panes(1).Pages(1)
debug.print pg.breaks(1).range.start
debug.print pg.breaks(pg.breaks.count).range.end

set pg = nothing

When you have some code post up and we can try to help.

[Siddharth Rout]

Hi

I am in the office so I am doing this from memory...

Hope this helps...

'Gives the number of pages in the word document
MaxPages = Selection.Information(wdNumberOfPagesInDocument)

'Go to the begining of the document
Selection.HomeKey Unit:=wdStory

Dim oXLApp As Excel.Application
Dim oXLSheet As Excel.Worksheet

'Declare the object variable
Set oXLApp = New Excel.Application
Set oXLBook = oXLApp.Workbooks.Add
Set oXLSheet = oXLBook.Worksheets(1)

oXLApp.Visible = True
'Show it to the user

'Check sheets in excel
exst_sheets = oXLApp.Sheets.Count

'number of sheets required in excel based on your
'requirement that you want to copy 2 pages in 1 sheet
sheets_reqd = Round(MaxPages / 2) + 1 'creating an extra sheet

'new sheets to be created if reqd
If exst_sheets < sheets_reqd Then
    new_reqd = sheets_reqd - exst_sheets
    
    For i = exst_sheets To sheets_reqd

        oXLApp.Sheets.Add

    Next i
End If

For j = 1 To sheets_reqd
    For k = 1 To MaxPages
        For Each Paragraph In ActiveDocument.Paragraphs
            Paragraph.Range.Select
            If Selection.Information(wdActiveEndAdjustedPageNumber) <> j Then Exit For
            Selection.Copy
            
            'Code here to Paste whereever you want to....
            
        Next
    Next k
Next j

Set oXLBook = Nothing
Set oXLApp = Nothing

PS: Remember to add reference to the excel Object Library. You might have to edit the above code as per your requirements...

[namrata_p]

please reply back to me... i need it urgently...