Glitch in my code Please Help

[John64]

I have the following code and it works about half of the time. It grabs an excel file from access and edits it, then I will write another part to import the excel data to a table. There seems to be a problem with this code not actually completely closing the excel file. It will often not load after I run this code. Sometimes I get a runtime error 9 and it will highlight the part that I have put in red below. I am not sure this is even needed it may be redundant but if I take out the activate workbook part the activate sheet part below it gets flagged by the debugger. Any help would be appreciated. I am really new to this. Thanks

Sub UpdateBaker()

'Set wb = Workbooks.Open(FileName:="C:\Documents and Settings\john.turrin\Desktop\Anext.xlsm")
'Application.ScreenUpdating = False
Dim appXL As Excel.Application
Dim wbk As Excel.Workbook
Set appXL = New Excel.Application
Set wbk = appXL.Workbooks.Open("C:\Documents and Settings\john.turrin\Desktop\Anext.xlsm")
Workbooks("Anext.xlsm").Activate
Sheets("Sheet1").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = "Well#"
Range("B2").Select
ActiveCell.FormulaR1C1 = "MCF"
Range("C2").Select
ActiveCell.FormulaR1C1 = "Psi T"
Range("D2").Select
ActiveCell.FormulaR1C1 = "Psi C"
Range("E2").Select
ActiveCell.FormulaR1C1 = "Date"
Range("F2").Select
ActiveCell.FormulaR1C1 = "Date Uploaded"
Range("E3").Select
ActiveCell.FormulaR1C1 = "1/1/1980"
Range("E4").Select
ActiveCell.FormulaR1C1 = "1/1/1980"
Range("E5").Select
ActiveCell.FormulaR1C1 = "1/1/1980"
Range("E3:E5").Select
Selection.AutoFill Destination:=Range("E3:E886")
Range("E3:E886").Select
Range("F3").Select
ActiveCell.Value = Date
Range("F4").Select
ActiveCell.Value = Date
Range("F5").Select
ActiveCell.Value = Date
Range("F3:F5").Select
Selection.AutoFill Destination:=Range("F3:F886")
Range("E3:F3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "m/d/yyyy"
Range("H2").Select

wbk.Close
appXL.Quit
Set appXL = Nothing
Set wbk = Nothing
End Sub

 

[KenHigg]

I suspect that your code failed to close the application object and it is trying to open another with the same name. The only fix I know of is to restart windows. I am sure there is another way but I have not figured out what the code is. The problem is that the object is enumerated and the instance is hard to track down.

The long term fix is to make sure you close the object in your error trapping code...

Hope all of that made sense.

Edit: I meant to say workbook object instead of application object

 

[SOS]

The reason why you are having problems is that you are using non-instantiated references. In other words you are using things like

Range("F4").Select
ActiveCell.Value = Date


instead of

wbk.Range("F4").Select
appXL.ActiveCell.Value = Date

You need to use your instantiated objects to do anything, otherwise Access opens a HIDDEN excel object and does it for you and then it will keep that Excel application object open until you close Access or close it by using the Task Manager.

Also, you don't need to select the cells before assigning a value. You can get rid of most of the selects by using something like:

wbk.Range("B2").Formula = "=MCF"

And don't use the FormulaR1C1 but use Formula instead and you can just give it the formula. If you use the R1C1 method then it is a pain and you have to use different type of Referencing.

 

[John64]

SOS, you appear to be exactly right with what is going on. I would prefer to not have instances of excel running and accessing files after the code has run. I have been trying to get the instantiated commands figured out. Is there a book or reference that list these commands? Currently I can't get the wbk.Range command to work. It isn't listed as a command in the vba editor from access from what I can tell. I may also need to set something up before that command is accepted. I am not sure. Anyway, I am still struggling and any help is appreciated.

Dim appXL As Excel.Application
Dim wbk As Excel.Workbook
Set appXL = New Excel.Application
Set wbk = appXL.Workbooks.Open("C:\Documents and Settings\john.turrin\Desktop\Anext.xlsm")

wbk.Sheets("Sheet1").Select
wbk.Range("A2").Formula = "Well#"

 

[SOS]

wbk.Range("A2").Formula = "Well#"

is not a valid formula.

wbk.Range("A2").Formula = "='Well#'"

would be (formulas need the equals sign). If you just want a value then you would need wbk.Range("A2").Value = Whatever
What is Well#? Is it a title? If so then you would just use:

wbk.Range("A2").Value = "Well#"

 

[John64]

It is supposed to be a title. It still doesn't want to accept it. There must be something else going on. Not sure what to do. I am now getting runtime error 438 when it gets to this command.

 

[SOS]

. Not sure what to do. I am now getting runtime error 438 when it gets to this command.

Can you post the database?

 

[John64]

Here are the files I am trying to get to work. I cut a lot of forms and stuff out of the DB and just left the table and the macro I am trying to get to work. The macro is called UpBaker and the Baker Production table will ultimately be the one getting updated. I have tested the command below and it works well but the titles of the excel sheet need to be exact with the table and the dates that I need still need to be put into the excel sheet before it will work for me. Thank You

DoCmd.TransferSpreadsheet acImport, 8, "BakerTest", "C:\Documents and Settings\john.turrin\Desktop\ANext.xlsm", True, "Sheet1!A2886"

 

[SOS]

I'll have to look at it when I get home in a couple of hours. I don't have 2007 here at work but I do have it at home.

 

[John64]

Well, I think I have it all working now. If anyone is having a similar problem, here is the recap. When you run the commands I listed at the top of the page it opens Excel temporarily and never seems to close it. This causes problems if you need to run additional code or access the excel file afterward. You can close the "EXCEL.exe" process from the task manager and get things back to normal if you don't feel like restarting. SOS, is correct in that you need to run "instantiated" commands to prevent this. The excel macro commands I was trying weren't working becasue they need a sheet reference in them as well. They should have looked like this:

wbk.Sheets("Sheet1").Range("A2").Value = "Well"
wbk.Sheets("Sheet1").Range("B2").Value = "Whatever"
wbk.Sheets("Sheet1").Range("C2").Value = "Be Creative"

Thanks to all for the help