pdf to excel macro

Question

I am starting to formulate a task to import a pdf file into excel.  I can either copy/paste into excel, or use a utility to perform this task - this gets the information into excel.  Formatting is maintained once in excel (i.e. - if a sentence spans multiple lines in the pdf, it ports into excel on multiple rows).

Once in excel, I would like to arrange the excel document so sentences remain on one row (rather than extending across multiple rows).  Because of the volume of text I'm importing, I'm thinking the best way to do this is with a macro. 

I am copying different sections of the pdf document onto separate worksheets (using the copy/paste method).  I would like the macro to be interactive (i.e. - allow me to select the number of rows I would like the macro to affect).  Once I get the limits selected, I would like to use the presence/absence of a period to direct the activity.  There is probably a dozen ways to do this, so I'm looking for advice from the macro experts on the most-elegant way (e.g. - is it better to create a "scratchpad" worksheet to copy the results to, or rearrange the rows as the macro is running?)  I've done quite a bit of programming in C, but am not so familiar with the requirements of VB coding.   

Thanks!

Answer 1

Hi,

make a copy, before you run the code below

(convert data from Active Sheet)

Sub ConvertData()

Dim N As Long

N = Cells(Rows.Count, "A").End(xlUp).Row

For i = N To 2 Step -1

If Cells(i, 1) <> "" And Cells(i - 1, 1) <> "" Then

Cells(i - 1, 1) = Cells(i - 1, 1) & " " & Cells(i, 1)

Rows(i).Delete

End If

Next

Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

Answer 2

you got it!

Answer 3

ok,

Sheet1, in  column A

a

b

c

(blank)

d

e

(blank)

q

w

e

r

t

y

(blank)

.....

expected result ?

a b c (in row1 on sheet1 ?)

d e (row2)

q w e r t y (row3)

.....

Answer 4

Thanks for your help!  I think I may not have been clear enough about my goal.  I am focusing on working on each worksheet independently.  First, I import pdf data into a worksheet.  Next I re-arrange the data.  Here's an example of what I might see, following the import operation; in column A, rows 1-3...

---

The only thing necessary

for the triumph of evil is

for good men to do nothing.

---

What I'm intending to do is put all sentence elements into one cell; Column A, row 1.

---

The only thing necessary for the triumph of evil is for good men to do nothing.

---

What I think I can use, to signify the end of a sentence, is the presence of a period.  If there is no period at the end of the row, the contents of the row would be appended to the initial row; increment row; repeat until a period is encountered.  I'm comfortable with the logic, but need to know how to go about coding it in excel.

Answer 5

Hi,

try this sample

assuming that you want to merge print areas from two different sheets.

From sheet1: A1:C10 and from sheet2: A20:B40

Sub MergePrintAreas()

Dim vRng As Variant, vSht As Variant, t As Long

Application.ScreenUpdating = False

Sheets.Add

vRng = Array("A1:C10", "A20:B40")  '<<< change range and/or add rngvSht = Array("Sheet1", "Sheet2") '<<< change names and/or add sheetst = 1

For x = 0 To UBound(vSht)

Sheets(vSht(x)).Range(vRng(x)).Copy Cells(t, 1)

t = ActiveSheet.UsedRange.Rows.Count + 1

Next

ActiveSheet.UsedRange.EntireColumn.AutoFit

Set rng = ActiveSheet.UsedRange

With ActiveSheet

.PageSetup.PrintArea = rng.Address

.PrintPreview

End With

Application.DisplayAlerts = False

ActiveSheet.Delete

Application.DisplayAlerts = True

Application.ScreenUpdating = True

End Sub