Object variable or With block variable not set

Question

I have this VBA code that checks all files in a folder. If the file is not a pdf then it opens it. It then loops for each paragraph in the text and if the paragraph contains some fixed symbols it exports paragraph text and values to an Access DB.

The code works 80% of the times, but other times, I get an error saying:

Object variable or With block variable not set" error.

This is my code:

Sub retrieve()
Dim wfile, strinsert, fileorigine, parastringa, parastring, myOutput, price, quantity, filename As String
Dim Myrange As Range
Dim objPara
Dim accessdb As Access.Application
Dim para As Paragraph
Dim date_created, date_last_modified As Date
Dim currfile As Document
Application.DisplayAlerts = False
Set accessdb = CreateObject("Access.Application")
With accessdb
.OpenCurrentDatabase ("C:\Users\myuser\Desktop\macro\DataBaseFr.accdb")
.Visible = True
.DoCmd.SetWarnings False


For i = 0 To 100 'This will have to be updated with a counter

If i = 0 Then ' For the first call to dir
    wfile = Dir("C:\Users\myuser\Desktop\macro\doc\")
Else
    wfile = Dir 'all calls to Dir after the first
End If

'if .pdf then skip
If Right(wfile, Len(wfile) - InStrRev(wfile, ".")) = "pdf" Then 
    Else
    Set currfile = Documents.Open("C:\Users\myuser\Desktop\macro\doc\" & wfile)
    'for each paragraph
    fileorigine = Replace(Left(currfile.Name, (InStrRev(currfile.Name, ".", -1, vbTextCompare) - 1)), "'", "")
    date_last_modified = Format(FileDateTime(currfile.FullName), "d/m/yy h:n ampm")
    date_created = currfile.BuiltInDocumentProperties("Creation Date")

    For Each para In currfile.Paragraphs
        Set objPara = para.Range
        objPara.Find.Text = "€"
        objPara.Find.Execute 
        'if the line contains €
        If objPara.Find.Found Then 
            If Left(para.Range.Text, 7) = "Sum" Then 
            'if the line contains € AND Sum
            parastringa = para.Previous.Range.Text
            parastring = parastringa
            quantity = Trim(Left(para.Range.Text, (InStrRev(para.Range.Text, "€") - 1)))
            price = Trim(Right(para.Range.Text, ((Len(para.Range.Text) - InStrRev(para.Range.Text, "€")))))
            Else 'if it is not a sum line
                parastringa = para.Range.Text
                parastring = Trim(Left(parastringa, (InStrRev(parastringa, "€") - 1)))
                price = Trim(Right(parastringa, ((Len(parastringa) - InStrRev(parastringa, "€")))))
                quantity = ""
            End If
                strinsert = " INSERT INTO Base " & "([origin], [Description],[date_created],[Datelast],[quantity], [price]) VALUES ('" & fileorigine & "', '" & parastring & "', '" & date_created & "' , '" & date_last_modified & "', '" & quantity & "' , '" & price & "');"
                CurrentDb.Execute strinsert, dbFailOnError
        Else
        End If
    Next para

    currfile.Close SaveChanges:=False
End If
i = i + 1
Next
CurrentDb.Close
End With
Application.DisplayAlerts = True

End Sub`

What could be happening and how do I fix it?

Start with proper Dim'ing of your variables - as shown for dates. — Gustav
– Gustav, Commented Jul 24, 2015 at 10:39
Do you mean that placing all Dims on the same line may cause issues? — Jack
– Jack, Commented Jul 24, 2015 at 10:43
Where is the error occurring? at what line does the code fail? — Gene
– Gene, Commented Jul 24, 2015 at 10:45
If not specifically dim'ed as shown, the variable will be dim'ed as a Variant. — Gustav
– Gustav, Commented Jul 24, 2015 at 12:17
Dim date1, date2 AS Date gives you date1 as a variant and date2 as a Date. The first one's type is not stated, so VBA makes it a variant. However Dim date1 AS Date, date2 AS Date gives you 2 Date type variables. — Rdster
– Rdster, Commented Jul 24, 2015 at 13:31

LocEngineer · Accepted Answer · 2015-07-24 12:07:15Z

1

Be careful when referencing objects, properties etc. from another application, in this case an access "CurrentDb" object from within Word VBA.

Since you are working in an With accessdb block, it should suffice to change

CurrentDb.Execute strinsert, dbFailOnError

to

.CurrentDb.Execute strinsert, dbFailOnError

and

CurrentDb.Close

to

.CurrentDb.Close

Once you're at it, also pull the final .DisplayAlerts = True up into the With block.

answered Jul 24, 2015 at 12:07

LocEngineer

2,9271 gold badge18 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jack Over a year ago

Thank you very much for your answer, I'll try as soon as I can and come back to you!

LocEngineer Over a year ago

Just as explanation: Without the dot as specifier for the accessdb object, VBA can only guess what CurrentDB is - when it guesses it is some normal Word VBA variable (which in that case of course is not set), you will get your error. With the preceding dot you make sure that VBA "knows" you are actually referring to accessdb.CurrentDB.

Collectives™ on Stack Overflow

Object variable or With block variable not set

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related