Solved Intermittent "Object variable or With block variable not set" error with VBA routine (1 Viewer)

[SurreyNick]

I have this bit of code which opens an existing Excel workbook from a file picker in one of my forms (txtFileName) and which then deletes all but one sheet (which is renamed) and then saves the workbook with the new name "Target.xlsx". Ultimately this will be imported into my db.

The problem is I think my code is crap. I have spent several days scouring online resources and trying various approaches to come up with something I can make work, but I have a strong suspicion I have gone about this the wrong way. The code I have here works in the first instance I run it but if I run it again without closing and reopening the db I get the error "Object variable or With block variable not set" and I can't work out how to resolve it.

My routine is going to be quite lengthy because I have a lot of data manipulation to do on the worksheet before importing it into my db and I want to get the right code and get it right at the outset before going any further.

With this in mind I would really appreciate some assistance here; (1) to tell me if there is a better approach I should adopt for this task, and (2) where the fault is in my code.

Please use code tags. ..

Private Sub btnRunMacro_Click()

Dim mySheetNames() As Variant
mySheetNames = Array("Topic & Skills Breakdown", "Statements and Marks", "Generated Feedback")

Call OpenExcelFile(Me.txtFileName)  [COLOR=rgb(65, 168, 95)]'<txtFileName is a text box file picker on my form[/COLOR] 
With ActiveWorkbook
    .SaveAs fileName:="C:\Users\Nick\Desktop\Target.xlsx"
    .Application.DisplayAlerts = False
    .Sheets(mySheetNames).Delete
    .Application.DisplayAlerts = True
    .Worksheets("Mark Sheet").Name = "Sheet1"

[COLOR=rgb(65, 168, 95)]    'add additional code to manipulate data[/COLOR]
    
    .Close SaveChanges:=True
End With

End Sub

 

[theDBguy]

Are you using error handlers in your code? I sometimes get this error if an object I'm trying to set fails to instantiate.

 

[SurreyNick]

No. I'm not. I'll give that a try right now. Thanks.

 

[theDBguy]

No. I'm not. I'll give that a try right now. Thanks.

Have you tried stepping through your code?

 

[Isaac]

1. Where does the error occur - what line?
2. Please post your code for the OpenExcelFile

Regardless of the answer to either, it is never a good idea to use any Excel VBA code that relies on Select, Selection, Active, Activate, etc.

For example, ActiveWorkbook. This could produce totally unexpected/dangerous results (or err) if your user has another Excel workbook open on their desktop at the same time. What you probably should do (if you really need to separate the opening of an Excel file into another function, which I probably wouldn't do), is return an Excel.Workbook object from the function.

In your case I think I probably wouldn't separate it into another function, that way you can more easily clearly see & control the flow of objects you are referencing

 

[SurreyNick]

The error seems to be occurring on this line:

.SaveAs fileName:="C:\Users\Nick\Desktop\Target.xlsx"

Sorry, I don't understand what you mean by "2. Please post your code for the OpenExcelFile". The code I posted is the entire code I have for the routine.

I'm missing something important aren't I?

 

[Isaac]

Sorry, I don't understand what you mean by "2. Please post your code for the OpenExcelFile". The code I posted is the entire code I have for the routine.

Call OpenExcelFile(Me.txtFileName)

Search your vba project for Sub OpenExcelFile( or Function OpenExcelFile(

 

[Isaac]

Or change the whole thing to:

Private Sub btnRunMacro_Click()

Dim mySheetNames() As Variant
mySheetNames = Array("Topic & Skills Breakdown", "Statements and Marks", "Generated Feedback")

'*******added:
dim appExcel as excel.application, wb as excel.workbook
set appExcel=new excel.application
appExcel.visible=true 'helps to see what's going on if this code errs out
'*******

set wb = appExcel.workbooks.open(Me.txtFileName)
wb.SaveAs fileName:="C:\Users\Nick\Desktop\Target.xlsx"
appExcel.DisplayAlerts = False
wb.Sheets(mySheetNames).Delete
appExcel.DisplayAlerts = True
wb.Worksheets("Mark Sheet").Name = "Sheet1"
wb.Close SaveChanges:=True

End Sub

(I assumed you are using early binding/references to Excel, since you were able to use ActiveWorkbook with no other qualifications? If so that code should work).

 

[SurreyNick]

Thanks I'll have a proper study of this. In the interim, having worked out from your and @theDBguy comments that I had made a fundamental error in my coding I did the following which seems to be working. I may have to amend it but do you think I'm on the right track?

Private Sub btnRunMacro_Click()

'Set up object variables to refer to Excel and objects
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet

'declare sheets to be deleted
Dim mySheetNames() As Variant
mySheetNames = Array("Topic & Skills Breakdown", "Statements and Marks", "Generated Feedback")

'Open an instance of Excel, open the workbook
Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(Me.txtFileName)

'Make sure everything is visible on the screen
Xl.Visible = True
XlBook.Windows(1).Visible = True

'Define the topmost sheet in the Workbook as XLSheet
Set XlSheet = XlBook.Worksheets("Mark Sheet")

'Delete the unwanted worksheets
XlBook.Application.DisplayAlerts = False
XlBook.Sheets(mySheetNames).Delete
XlBook.Application.DisplayAlerts = True
XlBook.Worksheets("Mark Sheet").Name = "Sheet1"

'Save the workbook with a new name
XlBook.SaveAs fileName:="C:\Users\Nick\Desktop\Target.xlsx"

'Clean up and end with worksheet visible on the screen
Set Xl = Nothing
Set XlBook = Nothing
Set XlSheet = Nothing

End Sub

 

[SurreyNick]

If, with your help (thank you), I am now on the right track, can I assume I can continue to work on the open workbook without having to close and reopen it, and is it now just a matter of recording an Excel macro of the further editing and pasting it before the Clean up and end statements?

 

[Isaac]

Honestly I would recommend the code I posted--but mainly because I know it works and there are a couple of things in your code that I am unsure about (but doesn't necessarily mean they are 'wrong') - then a couple things I would recommend changing, IF you use your last code posted.

not sure about:
Set XlBook = GetObject(Me.txtFileName) (just don't ever use this so not sure)
XlBook.Windows(1).Visible = True (after having used GetObject)

Also, you need to explicitly Quit the excel application and Close the workbook - not just set them to nothing.

 

[Isaac]

I thought you wanted to save and close the workbook? now i'm confused. The code I posted should work precisely according to the intent demonstrated by your original code..

 

[SurreyNick]

OK. Thanks, I will adopt your code. I'd prefer to use something you have confidence in

Yes, I will want to save and close the workbook before importing the data, I simply hadn't added that to the revised code I posted. Lazy of me and caused confusion - sorry. I was totally focused on ensuring I have code that would properly open the workbook so I can work on it and in my haste to get your feedback I missed it off.

It's no excuse but in my defense the code is far from complete. I am primarily concerned that I am starting out correctly and you have solved that for me. Thanks

Are you able to comment on my questions about whether I can continue to work on the open workbook without having to close and reopen it, and is it now just a matter of recording an Excel macro of the further editing and pasting it before the Clean up and end statements?

Thanks.

 

[Isaac]

Are you able to comment on my questions about whether I can continue to work on the open workbook without having to close and reopen it, and is it now just a matter of recording an Excel macro of the further editing and pasting it before the Clean up and end statements?

Ok so you are wondering if you can:

- Run the code I posted, except comment out the lines that CLOSE the wb or QUIT the excel app
- When the code finishes, manually work on the Excel workbook, including record a macro to generate some more code

?

Yes, I think so ... You may want to just add a line
after:
appExcel.visible=true 'helps to see what's going on if this code errs out
add:
appExcel.UserControl=true

 

[SurreyNick]

Yes, that's exactly what I was hoping I can do.

Having recorded the macro I want to paste the code into the routine after XlBook.SaveAs fileName:="C:\Users\Nick\Desktop\Target.xlsx", and then finish off with the clean up, save and close statements.

This is a routine that is going to be used on a regular basis to import data from some of my son's colleagues who, for the time-being at least, want to continue using the old legacy spreadsheets rather than the new db, which makes sense until the new db has been thoroughly road tested. My son would however like to get all the data from the whole Biology department into the db and this process is how I was proposing to do it, because it's a standardised spreadsheet that is currently used.

 

[Isaac]

Having recorded the macro I want to paste the code into the routine

Remember to check out the macro-recorded code, then change everything that it produces (which relies on Select, Selection, Selection.Offset, Activate etc) to proper code referencing things. I recently posted a really good couple paragraphs on examples of doing this but I can't find them now. I'm sure they're all over the net tho

 

[SurreyNick]

Thanks, I'll do that, and thanks for your help on this issue. I'm confident I can take it from here
Nick

 

[Isaac]

Cool, anytime, and best of luck with the project!

 

[SurreyNick]

Thanks @Isaac I have successfully completed this part of the exercise. I have got rid of all extraneous data in the original spreadsheet and am left with a nice clean table of records to transpose into my db. Very grateful to you.

Now I just need to get it into Access and have posted a new thread hoping to get some initial guidance on the correct method of doing that.

Really enjoying the exercise