Data preparation / Consumption report of Road Permits.

[VDS]

Dear All,

I am attaching herewith excel file contains data of Road Consumption Report (UP STATE) with two worksheets named sheet1 and sheet3_2.

Sheet1 gives the row data taken from a PDF file -A1 to A34 (Row Wise) . The field names have been defined and the value have extracted with lengthy formula. (see B6 : O6) Column Wise

I have repeated the same values in Sheet3_2 (C7 : P7) by linking sheet1.


The row data is extracted from a PDF file (as attached) by open + copy + paste for generating data for one single record (from B6: O6). There are no limit of total number of PDF files.

When a new record is created, the entire record is updated through the following macro.

Sub Macro4()

'

' Macro4 Macro

'

' Keyboard Shortcut: Ctrl+e

'

  Range("C7:P7").Select

  Selection.Copy

  Sheets("Sheet3_2").Select

  Range("C10").Select

  Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

  :=False, Transpose:=False

End Sub

Here, I find the following issues : -



a) It is slightly difficult to copy+ paste data from pdf to excel and apply macro each time. Is there any other way to do this by selecting all the pdf files and update the data in a single stretch ? All the field names are common and it is available in the PDF. Is it possible to avoid the copy + paste exercise through macro ? It will be good if something available in order to same time. The PDF is generated in 2 pages (original and second copy). But the data is exactly same.

b) The Data is to be sorted in Ascending Order as per Demand No. The Demand No having two parts ; first portion starting with D, second part coming between the brackets. Say D1504800151815 (150400235467), D1504800151815 (150400235468), like that. In fact, this is the Unique I Code generated from the UP website.

VDS

 

[Simayan]

Hi VDS,

Hope this attached code helps you in your purpose. Let me know if this is fine.

In this file you need to add your formulas & Macros which you shared earlier. I haven't added that

 

[VDS]

Simayan
Thanks for reply. This macro is fine. But helpful only for one file. Suppose a directory have more than one file this code over writes each and every pdf and result is only worksheet in the file. System ddisplsys mesaage for all the files and the lig report is.for complete nos. Is it.can be corrected ? I am unable to send the replies as little busy with my family. Dont feel.one fmore thing how to set the functions for than one work sheet

VDS

 

[Simayan]

Hi VDS,

Solved the glitch. Please check & let me know if this is fine.

 

[Simayan]

Hi VDS,

In addition to the last file shared. I have combined the entire process in two separate macros i.e.

1. Extract from PDF

2. Summarize data

Trust this will help you.

The final version attached fyr

 

[VDS]

@Simayan,

Thanks a lot. I will try this and revert.


VDS