troubleshooting Question

VBA Can't open available Excel file

Avatar of claghorn
claghorn asked on
Microsoft Word
11 Comments1 Solution541 ViewsLast Modified:
In the following vba code I am getting "object variable or with block variable not set" error
marked by the *
"file_to_use" is being passed in from a file dialogue.
The file itself is available as I can open it outside of the app.
Can you spot anything I am missing?

Currently my only fix is to restart the application. So it works the first time only in other words.

Dim curRange(5)
Dim iCounter As Integer
Dim strReceipt_list As String
Dim removeChar As Integer
Dim xl As Excel.Application
Dim wkbk As Excel.Workbook
Dim wk As Excel.Worksheet

On Error GoTo errHandler
 
Set xl = CreateObject("Excel.Application")
Set wkbk = xl.Workbooks.Open(file_to_use)
Set wk = wkbk.Sheets(1)
   
strReceipt_list = "("

For iCounter = 0 To 4
*   curRange(iCounter) = ActiveCell.Offset(iCounter, 0).Value
Next iCounter

For iCounter = 0 To 4
   strReceipt_list = strReceipt_list + CStr((curRange(iCounter))) + ","
Next iCounter
   
strReceipt_list = strReceipt_list + "*"
strReceipt_list = Replace(strReceipt_list, ",*", ")")
   
wkbk.Close
xl.Quit
Set wk = Nothing
Set wkbk = Nothing
Set xl = Nothing
   
Exit Sub

errHandler:

   wkbk.Close
   xl.Quit
   Set wk = Nothing
   Set wkbk = Nothing
   Set xl = Nothing
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 11 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros