Release Excel damn you (1 Viewer)

vipersmind

vipersmind

it can't be!
Local time
Today, 15:30
Joined
Dec 26, 2002
Messages
82
I have looked through this Forum backwards and forwards

I am using the following code to open a .las file (essentially a text file) in excel and save it as a .txt file, then import it into access.

I cannot get rid of the instance of Excel that open during this process
The rest of the code works a treat...

I have read many posts but cannot seem to find the answer

Thanks in Advance for you help

Cress
:confused:
Code: 
Option Compare Database
Option Explicit
Private xl As Object
________________________________________________________________

Public Sub ModifyExportedExcelFileFormats(sFile As String)
On Error GoTo Err_ModifyExportedExcelFileFormats

    Dim xlApp As Excel.Application
    Dim xlSheet As Object
    Dim strSelectedFile As String
    strSelectedFile = Forms![frmGamma]![cbSelectGammaFile_Nav]
    
    Set xlApp = CreateObject("Excel.Application")
    Set xlSheet = xlApp.Workbooks.Open("C:\database\imports\Gamma\Navaids\import\" & strSelectedFile).Sheets(1)

        Workbooks.OpenText filename:= _
        "C:\database\imports\Gamma\Navaids\import\" & strSelectedFile, Origin:=437, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=True, OtherChar:=":", FieldInfo:=Array(Array(1, 1 _
        ), Array(2, 1)), TrailingMinusNumbers:=True
        ActiveWorkbook.SaveAs filename:= _
        "C:\database\imports\Gamma\Navaids\import\LasImport.txt", FileFormat:=xlText _
        , CreateBackup:=False
        
    xlApp.ActiveWorkbook.Save
    xlApp.ActiveWorkbook.Close
    xlApp.UserControl = False
    xlApp.Quit

Exit_ModifyExportedExcelFileFormats:
    Exit Sub

Err_ModifyExportedExcelFileFormats:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_ModifyExportedExcelFileFormats

End Sub
 
clerics

clerics

Registered User.
Local time
Today, 16:30
Joined
Nov 28, 2005
Messages
82
you do not need change Excel's file to text file then import to access. Let try this code :

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, strac, strxls, True, strrange

strac: table's name where u want to import to
strxls: the path of Excel's file
True/False: take or do not take the first row in Excel's file
strrange: name of sheet and range

For example :
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Winport","C:\Mydocuments\abc.xls" , True, "Sheet1" & "!A9:W65536"

For more : take a look in here to take my example if it can help you anymore .;)
http://www.access-programmers.co.uk/forums/showthread.php?t=100365
 
vipersmind

vipersmind

it can't be!
Local time
Today, 15:30
Joined
Dec 26, 2002
Messages
82
Sorry i think i may have misled you
The file extension is .Las and is not supported by Access at all and the file file is both Tab and space delimited.
Access cannot open a file that is delimited both Tab and Space
seems to only handle one or the other

Therefore I open it in Excel and save as a .txt file for Access to use.

I have attached and example of the .las file
 

Attachments

  • Sample.zip
    1.5 KB · Views: 133
Last edited:
clerics

clerics

Registered User.
Local time
Today, 16:30
Joined
Nov 28, 2005
Messages
82
Sorry friend cuz i misunderstanded what you've been doing . I'll take it more carefully (but tel the truth i've never done with file .las :()
However i'm really want to try to do it :D
Ya ..see u soon ...
Good luck
 
D

david.brent

Registered User.
Local time
Today, 08:30
Joined
Aug 25, 2004
Messages
57
After xlApp.Quit type

Set xlApp = nothing.

That should do the trick.

Take care.
 
S

SammyJ

Registered User.
Local time
Today, 07:30
Joined
Jan 9, 2006
Messages
57
I'm a little worried though about your use of WorkBooks like that. Shouldn't you be using the Excel application object you created to reference the worksheets? So xlApp.WorkSheets...

Otherwise you are referring to Access' Application object's WorkSheets property, and not the one you think you are.

So prefix your WorkSheets calls with your variable and then it should all be above board.

Setting a local variable to nothing just before it goes out of scope should by rights be pointless, but it is considered good practise. So do that too. Set the xlSheet variable to nothing just beforehand as well.

Sam.
 
S

SammyJ

Registered User.
Local time
Today, 07:30
Joined
Jan 9, 2006
Messages
57
Hey, I'm from Perth. Forget the Excel and go to the beach.
 
Matt Greatorex

Matt Greatorex

Registered User.
Local time
Today, 03:30
Joined
Jun 22, 2005
Messages
1,019
I've been through the forum and found dozens of answers to this type of problem, but I'm none the wiser. There seem to be a large variety of reponses, none of which I can make work for me.

I have code which starts with the following:

Dim appExcel As Excel.Application
Dim ExcelBook As Excel.Workbook
Dim ExcelSheet As Excel.Worksheet

Set appExcel = New Excel.Application
Set ExcelBook = appExcel.Workbooks.Open(str_Location & "Templates\" & str_Title)
Set ExcelSheet = ExcelBook.Worksheets(1)


The above code works fine and does what it's meant to. Namely, it opens an Excel template I'm using and populates it with data from the Access database.

Where the problem occurs is in trying to close the Excel process after I've finished with it. From the various responses I've found on the forum, I believe I need to use something along the lines of the following.

ExcelBook.Close
appExcel.UserControl = True
Set ExcelSheet = Nothing
Set ExcelBook = Nothing
Set appExcel = Nothing
appExcel.Application.Quit

However, the above, and numerous variations thereof, aren't working. Clearly I'm doing/not doing something wrong, but I can't work out what. I get no error messages, the Excel process just stays open until I come out of Access.

Please don't be afraid to state the obvious.
 
S

supmktg

Registered User.
Local time
Today, 02:30
Joined
Mar 25, 2002
Messages
360
I'm having the same problem with Excel.exe process still open after I'm done with the transferspreadsheet. Did you ever find the solution?

Thanks,
Sup
 
boblarson

boblarson

Smeghead
Local time
Today, 00:30
Joined
Jan 12, 2001
Messages
32,059
Try this out to start with -

1. check your task manager and make sure there are no instances of Excel.exe running when you start this process.

2. Put a breakpoint in at the point where your code creates the Excel Application and check the task manager window after each step. You may find something actually creating two instances of Excel, but of course if you are closing one object the other is still out there. I know it sounds weird, but I've had that problem before (although I can't remember the exact cause -sorry) and that helped me track it down.

Personally I don't think you need the Worksheets (I never use it and I do a fair amount of Excel coding) object as you can reference the sheet from the Workbooks object and that may be causing your headache.
 
S

supmktg

Registered User.
Local time
Today, 02:30
Joined
Mar 25, 2002
Messages
360
Thanks for the quick reply!
Adding the following code fixed the problem.

Code: 
    xlWB.Save
    xlWB.Application.Quit
    xlApp.Application.Quit
    Set xlWB = Nothing
    Set xlApp = Nothing

Thanks,
Sup
 
carlnewboult

carlnewboult

Registered User.
Local time
Today, 08:30
Joined
Sep 27, 2005
Messages
90
Hi people,

thanks for the help i now have access releasing excel but not the workbook. I am willing to bet it is something simple.

Code: 
    Dim app As Object                   'Excel interface
    Dim File As Object                  'File Name
    Dim ws As Object                    '1st Worksheet
    Dim ws2 As Object                   '2nd Worksheet
    Dim ws3 As Object                   '3rd Worksheet
    Dim ws4 As Object                   '4th Worksheet
    Dim ws5 As Object                   '5th Worksheet
    Dim ws6 As Object                   '6th Worksheet
    Dim ws7 As Object                   '7th Worksheet
    Dim ws8 As Object                   '8th Worksheet
    
    Dim Spread As Recordset             'Table Name Containing Spreadsheet Name
    Dim sp As String                    'Name Of Spreadsheet To Be Opened
    Dim sp2 As String                   'Spreadsheet Address And File Name
    
    Dim MthFileName As Recordset        'Table Name
    Dim MFN As String                   'Month File Name eg March = p03
    
    Dim CurrentYear As Recordset        'Table Name
    Dim Year As String                  'Current Year
    
    Dim MFNPreviousTable As Recordset   'Table Name
    Dim MFNPrevious As String           'Previous Month File Name eg February = p02
    
    Dim CurrentMonth As Recordset       'Table Name
    Dim month As String                 'Month In Long Format eg March
    Dim PM As String                    'Previous Month
    Dim PMY As String                   'Previous Months Year
    
    Dim SourceFile, DestinationFile     'Source Of File Name and Destintion For File Name
    Dim standardbook As String
    
    Set CurrentMonth = CurrentDb.OpenRecordset("tblCurrentMonth")
    month = CurrentMonth("CurrentMonth2")
    PM = CurrentMonth("PreviousMonth")
    PMY = CurrentMonth("PreviousMonthYear")
    
    Set CurrentYear = CurrentDb.OpenRecordset("tblCurrentYear")
    Year = CurrentYear("Yearfile")

    Set MthFileName = CurrentDb.OpenRecordset("tblMonthFileName2")
    MFN = MthFileName("StartFileName")
    
    Set MFNPreviousTable = CurrentDb.OpenRecordset("tblMonthFileNamePrevious")
    MFNPrevious = MFNPreviousTable("StartFileName")

    Set Spread = CurrentDb.OpenRecordset("tblSpreadsheetNameOpen")
    sp = Spread("Spreadsheet")
    sp2 = "F:\EOM Stats Booklets\Standard Booklet\" & PMY & "\" & MFNPrevious & " - " & PM & "\" & MFNPrevious & " - " & sp
    sp2 = sp2 & ".xls"
    
    SourceFile = "F:\EOM Stats Booklets\Standard Booklet\" & PMY & "\" & MFNPrevious & " - " & PM & "\" & MFNPrevious & " - " & "Norwich Union Enterprise.xls"
    DestinationFile = "F:\EOM Stats Booklets\Standard Booklet\" & PMY & "\" & MFNPrevious & " - " & PM & "\" & MFNPrevious & " - " & sp & ".xls"
    FileCopy SourceFile, DestinationFile
    
    Set app = CreateObject("Excel.Application")
    Set File = app.Workbooks.Open(sp2)
    Set ws = File.Worksheets("Front Sheet")
    Set ws2 = File.Worksheets("RAG Report")
    Set ws3 = File.Worksheets("Req Date - Comp Date (All)")
    Set ws4 = File.Worksheets("Req Date - Comp Date (Straight)")
    Set ws5 = File.Worksheets("Req Date - Comp Date (Exc Hold)")
    Set ws6 = File.Worksheets("Exam Type")
    Set ws7 = File.Worksheets("Data")
    Set ws8 = File.Worksheets("Top Company Turnarounds")
    
    ws.Activate  'Front Sheet
    
    Range("A7").Select
    ActiveCell.FormulaR1C1 = (sp)
    
    ws2.Activate 'RAG Report
    
    Range("A2").Select
    ActiveCell.FormulaR1C1 = (sp)
    
    Range("C7:C16").Select
    Selection.ClearContents
    
    Range("E7:E16").Select
    Selection.ClearContents
    
    Range("G7:G16").Select
    Selection.ClearContents
    
    Range("I7:I16").Select
    Selection.ClearContents
    
    Range("K7:K16").Select
    Selection.ClearContents
    
    Range("A1").Select
    
    ws3.Activate  'Req Date - Comp Date (All)
    
    Range("C31:N31").Select
    Selection.ClearContents
    
    Range("A1").Select
    
    ws4.Activate  'Req Date - Comp Date (Straight)
    
    Range("C31:N31").Select
    Selection.ClearContents
    
    Range("A1").Select
    
    ws5.Activate  'Req Date - Comp Date (Exc Hold)
    
    Range("C31:N31").Select
    Selection.ClearContents
    
    Range("A1").Select
    
    ws6.Activate  'Exam Type
    
    Range("A4:C500").Select
    Selection.ClearContents
        
    Range("A1").Select
    
    ws7.Activate  'Data Sheet
    
    Range("B3:IV3").Select
    Selection.ClearContents
    
    Range("B7:IV9").Select
    Selection.ClearContents
    
    Range("B13:IV16").Select
    Selection.ClearContents
    
    Range("B20:IV24").Select
    Selection.ClearContents
    
    Range("B28:IV31").Select
    Selection.ClearContents
    
    Range("B35:IV40").Select
    Selection.ClearContents
    
    Range("B44:IV49").Select
    Selection.ClearContents
    
    Range("B53:IV58").Select
    Selection.ClearContents
    
    Range("B63:IV65").Select
    Selection.ClearContents
    
    Range("B68:IV70").Select
    Selection.ClearContents
    
    Range("B74:IV82").Select
    Selection.ClearContents
    
    Range("B87:IV94").Select
    Selection.ClearContents
        
    Range("B99:IV100").Select
    Selection.ClearContents
    
    Range("B103:IV104").Select
    Selection.ClearContents
    
    Range("B1").Select
        
    ws8.Activate  'Top Company Turnarounds
    
    Range("A5:B40").Select
    Selection.ClearContents
    
    Range("A1").Select
    
    ws.Activate   'Front Sheet
    
    File.Save
    File.Application.Quit
    app.Application.Quit
    
    Set ws = Nothing
    Set ws2 = Nothing
    Set ws3 = Nothing
    Set ws4 = Nothing
    Set ws5 = Nothing
    Set ws6 = Nothing
    Set ws7 = Nothing
    Set ws8 = Nothing
    Set File = Nothing
    Set app = Nothing
 
boblarson

boblarson

Smeghead
Local time
Today, 00:30
Joined
Jan 12, 2001
Messages
32,059
Okay, you have a few things going on.

1. You don't need to declare more than one worksheet object. Just change the worksheet when you need it. I don't see anywhere where you would need to have multiples open at the same time.

2. In order to get things to release properly, you MUST, I repeat, you MUST refer to them explicitly. Currently you have things such as:
Code: 
...
    Range("B103:IV104").Select
    Selection.ClearContents
    
    Range("B1").Select
...
and they NEED to be referred to by:
prefacing with the worksheet object name
Code: 
ws.Range("B1").Select
Otherwise it will leave things hanging.
 
carlnewboult

carlnewboult

Registered User.
Local time
Today, 08:30
Joined
Sep 27, 2005
Messages
90
well it worked once but now it repeats the same problem.

I have found that if the code breaks then you fix and step through the program then it works fine but otherwise it does not and hangs again

Code: 
    Dim app As Object                   'Excel interface
    Dim File As Object                  'File Name
    Dim ws As Object                    '1st Worksheet
    Dim ws2 As Object                   '2nd Worksheet
    Dim ws3 As Object                   '3rd Worksheet
    Dim ws4 As Object                   '4th Worksheet
    Dim ws5 As Object                   '5th Worksheet
    Dim ws6 As Object                   '6th Worksheet
    Dim ws7 As Object                   '7th Worksheet
    Dim ws8 As Object                   '8th Worksheet
    
    Dim Spread As Recordset             'Table Name Containing Spreadsheet Name
    Dim sp As String                    'Name Of Spreadsheet To Be Opened
    Dim sp2 As String                   'Spreadsheet Address And File Name
    
    Dim MthFileName As Recordset        'Table Name
    Dim MFN As String                   'Month File Name eg March = p03
    
    Dim CurrentYear As Recordset        'Table Name
    Dim Year As String                  'Current Year
    
    Dim MFNPreviousTable As Recordset   'Table Name
    Dim MFNPrevious As String           'Previous Month File Name eg February = p02
    
    Dim CurrentMonth As Recordset       'Table Name
    Dim month As String                 'Month In Long Format eg March
    Dim PM As String                    'Previous Month
    Dim PMY As String                   'Previous Months Year
    
    Dim SourceFile, DestinationFile     'Source Of File Name and Destintion For File Name
    Dim standardbook As String
    
    Set CurrentMonth = CurrentDb.OpenRecordset("tblCurrentMonth")
    month = CurrentMonth("CurrentMonth2")
    PM = CurrentMonth("PreviousMonth")
    PMY = CurrentMonth("PreviousMonthYear")
    
    Set CurrentYear = CurrentDb.OpenRecordset("tblCurrentYear")
    Year = CurrentYear("Yearfile")

    Set MthFileName = CurrentDb.OpenRecordset("tblMonthFileName2")
    MFN = MthFileName("StartFileName")
    
    Set MFNPreviousTable = CurrentDb.OpenRecordset("tblMonthFileNamePrevious")
    MFNPrevious = MFNPreviousTable("StartFileName")

    Set Spread = CurrentDb.OpenRecordset("tblSpreadsheetNameOpen")
    sp = Spread("Spreadsheet")
    sp2 = "F:\EOM Stats Booklets\Standard Booklet\" & PMY & "\" & MFNPrevious & " - " & PM & "\" & MFNPrevious & " - " & sp
    sp2 = sp2 & ".xls"
    
    SourceFile = "F:\EOM Stats Booklets\Standard Booklet\" & PMY & "\" & MFNPrevious & " - " & PM & "\" & MFNPrevious & " - " & "Norwich Union Enterprise.xls"
    DestinationFile = "F:\EOM Stats Booklets\Standard Booklet\" & PMY & "\" & MFNPrevious & " - " & PM & "\" & MFNPrevious & " - " & sp & ".xls"
    FileCopy SourceFile, DestinationFile
    
    Set app = CreateObject("Excel.Application")
    Set File = app.Workbooks.Open(sp2)
    Set ws = File.Worksheets("Front Sheet")
    Set ws2 = File.Worksheets("RAG Report")
    Set ws3 = File.Worksheets("Req Date - Comp Date (All)")
    Set ws4 = File.Worksheets("Req Date - Comp Date (Straight)")
    Set ws5 = File.Worksheets("Req Date - Comp Date (Exc Hold)")
    Set ws6 = File.Worksheets("Exam Type")
    Set ws7 = File.Worksheets("Data")
    Set ws8 = File.Worksheets("Top Company Turnarounds")
    
    ws.Activate  'Front Sheet
    
    ws.Range("A7").Select
    ActiveCell.FormulaR1C1 = (sp)
    
    ws2.Activate 'RAG Report
    
    ws2.Range("A2").Select
    ActiveCell.FormulaR1C1 = (sp)
    
    ws2.Range("C7:C16").Select
    Selection.ClearContents
    
    ws2.Range("E7:E16").Select
    Selection.ClearContents
    
    ws2.Range("G7:G16").Select
    Selection.ClearContents
    
    ws2.Range("I7:I16").Select
    Selection.ClearContents
    
    ws2.Range("K7:K16").Select
    Selection.ClearContents
    
    ws2.Range("A1").Select
    
    ws3.Activate  'Req Date - Comp Date (All)
    
    ws3.Range("C31:N31").Select
    Selection.ClearContents
    
    ws3.Range("A1").Select
    
    ws4.Activate  'Req Date - Comp Date (Straight)
    
    ws4.Range("C31:N31").Select
    Selection.ClearContents
    
    ws4.Range("A1").Select
    
    ws5.Activate  'Req Date - Comp Date (Exc Hold)
    
    ws5.Range("C31:N31").Select
    Selection.ClearContents
    
    ws5.Range("A1").Select
    
    ws6.Activate  'Exam Type
    
    ws6.Range("A4:C500").Select
    Selection.ClearContents
        
    ws6.Range("A1").Select
    
    ws7.Activate  'Data Sheet
    
    ws7.Range("B3:IV3").Select
    Selection.ClearContents
    
    ws7.Range("B7:IV9").Select
    Selection.ClearContents
    
    ws7.Range("B13:IV16").Select
    Selection.ClearContents
    
    ws7.Range("B20:IV24").Select
    Selection.ClearContents
    
    ws7.Range("B28:IV31").Select
    Selection.ClearContents
    
    ws7.Range("B35:IV40").Select
    Selection.ClearContents
    
    ws7.Range("B44:IV49").Select
    Selection.ClearContents
    
    ws7.Range("B53:IV58").Select
    Selection.ClearContents
    
    ws7.Range("B63:IV65").Select
    Selection.ClearContents
    
    ws7.Range("B68:IV70").Select
    Selection.ClearContents
    
    ws7.Range("B74:IV82").Select
    Selection.ClearContents
    
    ws7.Range("B87:IV94").Select
    Selection.ClearContents
        
    ws7.Range("B99:IV100").Select
    Selection.ClearContents
    
    ws7.Range("B103:IV104").Select
    Selection.ClearContents
    
    ws7.Range("B1").Select
        
    ws8.Activate  'Top Company Turnarounds
    
    ws8.Range("A5:B40").Select
    Selection.ClearContents
    
    ws8.Range("A1").Select
    
    ws.Activate   'Front Sheet
    
    app.ActiveWorkbook.Save
    app.Application.Quit
    
    Set ws = Nothing
    Set ws2 = Nothing
    Set ws3 = Nothing
    Set ws4 = Nothing
    Set ws5 = Nothing
    Set ws6 = Nothing
    Set ws7 = Nothing
    Set ws8 = Nothing
    Set File = Nothing
    Set app = Nothing

Sorry to be a pain but this is really frustrating
 
boblarson

boblarson

Smeghead
Local time
Today, 00:30
Joined
Jan 12, 2001
Messages
32,059
You can't use ActiveCell and Selection by themselves. Again, I repeat, you must use those in the context of the explicit reference.
 
boblarson

boblarson

Smeghead
Local time
Today, 00:30
Joined
Jan 12, 2001
Messages
32,059
Also, I would still get rid of all of the worksheet objects and work with ONE. Also, I would not use reserved words for your objects (File is a reserved word), as is month and year.
 
carlnewboult

carlnewboult

Registered User.
Local time
Today, 08:30
Joined
Sep 27, 2005
Messages
90
just to clarify i should be placing ws infront of all activecell and selections ??
 
boblarson

boblarson

Smeghead
Local time
Today, 00:30
Joined
Jan 12, 2001
Messages
32,059
yes, but you may need to modify the syntax slightly (It's been a few months since I coded in there so I can't remember exactly how it has to say it).
 
You must log in or register to reply here.

Similar threads

N
Solved Mass Importing Txt Files
Replies
14
Views
386
ebs17
E
R
Updating Excel spreadsheet from within the Access
Replies
8
Views
332
CJ_London
C
M
Solved Converting txt file to xlsx
Replies
5
Views
375
miacino
M
L
Retrieve excel from access (vol 4) , extended format , alternative color
Replies
7
Views
566
arnelgp
arnelgp
M
Solved Access to Excel link
Replies
8
Views
741
bastanu
bastanu

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom