UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Ending Excel Process, Access 2010    
 
   
Euler
post Aug 12 2017, 01:51 PM
Post#1



Posts: 3
Joined: 29-September 16



This is an old problem and I've tried all the solutions I could find but here goes: I run the following code but Excel is still running after it's done. No, I don't have any other Excel instances running.

Sub NPI1_OLDER()
On Error GoTo OOPS
Dim xlApp As Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim fName As String
Set xlApp = New Excel.Application
fName = "H:\NPI\TestFile.csv"
'Convert file to XLSX.
If Len(Dir("H:\NPI\TestFile.xlsx")) Then Kill "H:\NPI\TestFile.xlsx"
Set wkb = Workbooks.Open(fName, Format:=6, Delimiter:=",")
wkb.SaveAs Replace(fName, ".csv", ""), xlOpenXMLWorkbook
Set wks = wkb.Worksheets(1)
xlApp.Visible = False
'Delete unneeded columns.
wks.Columns("DD:KU").EntireColumn.Delete
'Make certain columns text datatypes so there won't be any importing errors.
wks.Range("Y:Y, AA:AA, AB:AB, AG:AG, AI:AI, AJ:AJ, AU:AU").NumberFormat = "@"
'Make certain columns date datatypes so there won't be any importing errors.
wks.Range("AK:AK, AL:AL, AN:AN, AO:AO").NumberFormat = "m/d/yyyy"
'Change some headers to conform with field names.
wks.Range("Z1").FormulaR1C1 = "Provider Business Mailing Address Country Code (If out"
wks.Range("AH1").FormulaR1C1 = "Provider Business Practice Location Address Country Code (If out"
'Delete empty columns on the right.
wks.Range("LR:LR", wks.Range("LR:LR").End(xlToRight)).Delete Shift:=xlToLeft
wks.Range("A1").Select
wkb.Close SaveChanges:=True
ExitSub:
Set wks = Nothing
Set wkb = Nothing
xlApp.Quit
Set xlApp = Nothing
Exit Sub
OOPS:
MsgBox Err.Number & " " & Err.Description
GoTo ExitSub
End Sub

I've tried REMming out almost every line but Excel still persists. Any suggestions?
Go to the top of the page
 
moke123
post Aug 12 2017, 04:27 PM
Post#2



Posts: 1,135
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



see if anything here helps...excel problem in access
Go to the top of the page
 
cheekybuddha
post Aug 12 2017, 05:08 PM
Post#3


UtterAccess VIP
Posts: 9,265
Joined: 6-December 03
From: Telegraph Hill


Unqualified object reference:

CODE
Set wkb = Workbooks.Open(fName, Format:=6, Delimiter:=",")


should be:

CODE
Set wkb = xlApp.Workbooks.Open(fName, Format:=6, Delimiter:=",")


There may be more instances, but it would help if you used code tags when posting huge chunks of code.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
Euler
post Aug 13 2017, 04:44 PM
Post#4



Posts: 3
Joined: 29-September 16



Yes, I see that now. Thank you very much for spotting that for me.
Go to the top of the page
 
cheekybuddha
post Aug 13 2017, 08:21 PM
Post#5


UtterAccess VIP
Posts: 9,265
Joined: 6-December 03
From: Telegraph Hill


yw.gif

There are few things more irritating than those left-over Excel instances! And the unqualified references are a devil to spot.

I'm pleased we could help.

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    20th November 2017 - 08:40 AM