Access Controling Excel

[ogri]

Hi

I am currently working on an Access app which will take data in from an Excel spreadsheet. It will be stored and processed in Access and later output to Excel again.

I have got Access to read from and create Excel spreadsheets, but it is a bit untidy to close Excel as it asks whether you wish to save changes (even though there have been no changes). I am curretly using:-

Dim MyXL As Object
Set MyXL = GetObject("c:\temp\" + FlowNameToLoad)

MyXL.Application.Visible = True
MyXL.Parent.Windows(1).Visible = True

'Some code

MyXL.Application.Quit

Set MyXL = Nothing ' Release reference to the

Is there an Excel method I can use to terminate it without saving?

All the best

Keith

 

[jimmythegeek]

Try either

MyXL.Save OR
MyXL.ActiveWorkbook.Save

before the quit line.

Below, I posted a module from one of the applications I did that has good Excel code in it. It is mainly for reference sake, feel free to use it as a resource for future code.

====================
' Constants
Private Const XLS_LOCATION As String = "C:\My Documents\Spreadsheets\Vault.xlt"
Private Const XLT_LOCATION As String = "C:\Windows\Vault.xlt"
Private Const MC_START_ROW As Integer = 299
Private Const MC_END_ROW As Integer = 100
Private Const VISA_START_ROW As Integer = 999
Private Const VISA_END_ROW As Integer = 800

Public Sub populateExcel()
On Error GoTo Populate_Err
Dim db As Database
Dim rs As Recordset
Dim objXL As Object, objSheet As Object, objRange As Object
Dim strSaveAs As String, strVISA As String, strMC As String
Dim X As Integer, intRow As Integer

DoCmd.Hourglass True
Set db = CurrentDb()

' Set the SQL strings for the two recordsets that will be opened
strVISA = "SELECT [Card Style], [Start Inventory] FROM [qryworking inventory start] WHERE [Plastic Type] = 'VISA'"
strMC = "SELECT [Card Style], [Start Inventory] FROM [qryworking inventory start] WHERE [Plastic Type] = 'MC'"

' Open, and make visible the Excel Template (Vault.xlt) which resides on the desktop
Set objXL = GetObject(XLT_LOCATION)
objXL.Application.Visible = True
objXL.Parent.Windows(1).Visible = True

' Open the VISA recordset, and activate the VISA sheet in the template
Set rs = db.OpenRecordset(strVISA, dbOpenSnapshot)
Set objSheet = objXL.Worksheets("Visa&quot
objSheet.Activate
rs.MoveFirst
X = 4

' Insert the data from the VISA recordset into the VISA worksheet
Do Until rs.EOF
objXL.ActiveSheet.Cells(X, 1).Value = rs![Card Style]
objXL.ActiveSheet.Cells(X, 2).Value = rs![Start Inventory]
X = X + 1
rs.MoveNext
Loop

' Delete all unnecessary rows making the VISA worksheet only as long as it needs to be
intRow = VISA_START_ROW
With objSheet
.select
Do Until intRow = VISA_END_ROW
If .Range("A" & intRow).Value = "" Then
Set objRange = .Range("A" & intRow & ":B" & intRow & ":C" & intRow & "" & intRow & ":E" & intRow _
& ":F" & intRow & ":G" & intRow & ":H" & intRow & ":I" & intRow & ":J" & intRow _
& ":K" & intRow & ":L" & intRow & ":M" & intRow & ":N" & intRow & ":O" & intRow & "" & intRow)
objRange.Delete 'Shift:=objXLUp
End If
intRow = intRow - 1
Loop
End With
rs.Close

' Open the MC recordset, and activate the MC sheet in the template
Set rs = db.OpenRecordset(strMC, dbOpenSnapshot)
Set objSheet = objXL.Worksheets("MC&quot
objSheet.Activate
rs.MoveFirst
X = 4

' Insert the data from the MC recordset into the MC worksheet
Do Until rs.EOF
objXL.ActiveSheet.Cells(X, 1).Value = rs![Card Style]
objXL.ActiveSheet.Cells(X, 2).Value = rs![Start Inventory]
X = X + 1
rs.MoveNext
Loop

' Delete all unnecessary rows making the MC worksheet only as long as it needs to be
intRow = MC_START_ROW
With objSheet
.select
Do Until intRow = MC_END_ROW
If .Range("A" & intRow).Value = "" Then
Set objRange = .Range("A" & intRow & ":B" & intRow & ":C" & intRow & "" & intRow & ":E" & intRow _
& ":F" & intRow & ":G" & intRow & ":H" & intRow & ":I" & intRow & ":J" & intRow _
& ":K" & intRow & ":L" & intRow & ":M" & intRow & ":N" & intRow & ":O" & intRow & "" & intRow)
objRange.Delete
End If
intRow = intRow - 1
Loop
End With

' Calculate totals on spreadsheet
objXL.Application.calculate

' Set the save string, and save the spreadsheet
strSaveAs = "C:\Windows\Desktop\" & Format(DATE, "mmddyyyy&quot & ".xls"
objXL.SaveCopyAs strSaveAs

' Quit Excel
objXL.Application.DisplayAlerts = False
objXL.Application.Quit

Set objXL = Nothing
Set objSheet = Nothing
Set objRange = Nothing
Set rs = Nothing

Populate_Exit:
DoCmd.Hourglass False
Exit Sub

Populate_Err:
MsgBox Err.Number & ": " & Err.Description
GoTo Populate_Exit
End Sub
==================== Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[Quehay]

If I understand your question correctly Keith, what you need is:

Somewhere early in the Excel routine after objects are bound:
(objXLApp is the variable for the Excel.Application)

With objXLApp
.ScreenUpdating = False
.DisplayAlerts = False
End With

Then make sure that you have each of these set to True again in the exit label section of the error trap, otherwise if your code breaks for some reason you'll have these features shut off for the Excel app in general, and you don't want that! (Some properties work like toggles that persist as set in code even after the references are closed.)

 

[Quehay]

PS J the G already had this in his code--it just wasn't highlighted. . .

 

[ogri]

Hi

Thanks for that. Exactly what I wanted.

For the error trapping I am using:-

On Error Resume Next

so I can check the error codes on the following lines.

All the best

Keith

 

[Guest_imported]

I am new to vb and am writing a small vb application where in i extract records from access database and then send the records to an excel sheet and create a chart . when i try to run the program i sometimes get an error message 1004
_object not known or something like that.
and sometimes i can run the program well but not able to see the excel document after i run the program. what is the problem any help is appreciated.

thank you in advance

 

[jimmythegeek]

Use the long code posted above as a reference. You should be able to find most anything you need in that code. If you can't see excel, it's probably because you are not making it visible.

objXL.Application.Visible = True

Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[bitRAKE]

if you don't want to turn off all the other alerts you could just set that one file's 'Saved' property to TRUE. That way if the user has other files opened with changes, they wont loose anything.

objXL.ActiveWorkbook.Saved = True

Hope that helps,
bitRAKE

 

[maranello]

I am accessing an Excel spreadsheet from within an Access VB Module. When I close Excel using 'myApp.Application.quit', or any of the various other functions which are supposed to work, there are some instances where the Excel window disappears but the Excel process does not. Does anyone know why this is?

 

[ogri]

Hi

I have encountered this, but havent got round to fixing it yet. It seems to happen if Excel is already up when Access starts to use it (normally because I was debugging the code and then broke out of it).

All the best

Keith

 

[jimmythegeek]

Maranello,

Are you setting your Excel object = nothing?

MyApp.Application.Quit
Set MyApp = Nothing
Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[MarrowGenx]

Make sure you set all excel objects = nothing before you perform the myapp.application.quit. If you have any objects referencing cells or workbooks or worksheets, set them all = nothing before you quit the application. This should solve your problem.

The reason that Excel stays open is because it has lingering objects/references that it still thinks you need. It will not fully close Excel until you tell it that you dont need them anymore (i.e. object = nothing).

Hope this helps.

 

[kalvis]

Maranello, take a look at Thread 181-121256. Databaseguy addresses this issue in detail with some helpful links to Microsoft Knowledge Base.

This will probably answer the questions you have about automating Excel.

Good Luck!!!