I run a macro that copies tables from a PDF file and saves them on Excel. some of the tables contain empty cells and in my analysis I need to know the number of cells that are empty. I have a function that iterates through each column to check if the value within that cell is numeric or not. the trouble is when I run this function on an empty cell it returns true. I even tried manually cheeking the cells using the Isblank() function and it returns "false". (if I try this on any cell outside the pasted range it returns "true")
I am guessing that when I copy and paste things from PDF it somehow pastes some value for the empty cells.
did anyone ever encounter a similar problem? if so, any ideas on how it can be solved?
if it is any help here is the code I use to copy and paste
'Initialize Acrobat by creating App object
Set PDFApp = CreateObject("AcroExch.App")
'Set AVDoc object
Set PDFDoc = CreateObject("AcroExch.AVDoc")
'Open the PDF
If PDFDoc.Open(PDFPath, "") = True Then
PDFDoc.BringToFront
'Maximize the document
Call PDFDoc.Maximize(True)
Set PDFPageView = PDFDoc.GetAVPageView()
'Go to the desired page
'The first page is 0
Call PDFPageView.GoTo(DisplayPage - 1)
'-------------
'ZOOM options
'-------------
'0 = AVZoomNoVary
'1 = AVZoomFitPage
'2 = AVZoomFitWidth
'3 = AVZoomFitHeight
'4 = AVZoomFitVisibleWidth
'5 = AVZoomPreferred
'Set the page view of the pdf
Call PDFPageView.ZoomTo(2, 50)
End If
Set PDFApp = Nothing
Set PDFDoc = Nothing
On Error Resume Next
'Show the adobe application
PDFApp.Show
'Set the focus to adobe acrobat pro
AppActivate "Adobe Acrobat Pro"
'Select All Data In The PDF File's Active Page
SendKeys ("^a"), True
'Right-Click Mouse
SendKeys ("+{F10}"), True
'Copy Data As Table
SendKeys ("c"), True
'Minimize Adobe Window
SendKeys ("%n"), True
'Select Next Paste Cell
Range("A" & Range("A1").SpecialCells(xlLastCell).Row).Select
'Cells(1, 1).Select
'Paste Data In This Workbook's Worksheet
ActiveSheet.Paste
There are cases where it is better to check the length of the characters inside cells instead of using the isNumeric()
, or check for errors etc...
For example try the below code
it establishes the Range used in the active worksheet then iterates through checking the length (len()) of each cell
you can look at Immediate Window CTRL+G in VBE to see which cell addresses are empty or wait until the macro finishes executing and you will be welcomed with a Message Box saying how many empty cells are within the range
Option Explicit
Sub CheckForEmptyCells()
Dim lastCol As Range
Set lastCol = ActiveSheet.Cells.Find(What:="*", After:=ActiveSheet.Cells(1, 1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)
Dim rng As Range
Set rng = Range("A1:" & lastCol.Address)
Dim cnt As Long
cnt = 0
Dim cell As Range
For Each cell In rng
If Len(cell) < 1 Then
Debug.Print cell.Address
cnt = cnt + 1
End If
Next
MsgBox "there are " & cnt & " empty cells within the range " & rng.Address
End Sub