Opening Excel files from Access 2003

G

greebo

Registered User.
Local time
Today, 16:03
Joined
Apr 17, 2009
Messages
17
I have an app which validates and imports excel files into Access with a module that checks the Tab names then sets certain columns to "General" format.

The first file works ok then the second call of the module gives Run-time error 91 - "Object variable or With block variable not set"

Module:
------------------
Option Compare Database
Option Explicit

Function fncXLTabCheck1(frmM As Form, strPath As String, strImpFile As String, strType As String)
Dim appExcel As Object
Dim Wks As Worksheet
Set appExcel = CreateObject("Excel.Application")
appExcel.Workbooks.Open strPath & strImpFile, UpdateLinks:=0
For Each Wks In appExcel.Worksheets
If Trim(UCase(Wks.Name)) = "MONTH" Then
Wks.Name = "xxMonth-" & Format(Now(), "yyyymmddhhnn")
End If
Next
For Each Wks In appExcel.Worksheets
If Trim(UCase(Wks.CodeName)) = "MONTH" Then
Wks.Name = "Month"
frmM!intSheet = frmM!intSheet + 1
If strType = "OCEAN" Then
Wks.Columns("M:N").Select
Selection.NumberFormat = "General"
ElseIf strType = "AIR" Then
Wks.Columns("L").Select
Selection.NumberFormat = "General"
Wks.Columns("N").Select
Selection.NumberFormat = "General"
End If
End If
Next
appExcel.ActiveWorkbook.Close SaveChanges:=True
' set appExcel = nothing 'makes no difference
End Function
------------------

Setting appExcel = nothing makes no difference to the problem

Possible problem is brainfade, but I would appreciate any help!
Thanks
 
Think you need xlapp.Quit. also do not make duplicate postings
 
Accessing programs from windows 2003 is very simple but it is also obsolete because from 2007 excel is better and easier to use.
 
sorry rickysymo, but after 25 years in computing, I have decided to retire rather than learn Office 2007.

Thanks Dcracke, I have changed the end of the module to:

appExcel.ActiveWorkbook.Close SaveChanges:=True
appExcel.Quit
Set appExcel = Nothing

But I am still getting problems

Should I change appExcel to a public variable and set it when I open the database then close it just before the Quit?
 
Last edited:
greebo,

When you encounter the Error 91, click on the Debug button. Then copy the line of code that is yellow-highlighted and paste it here so that we may see at which point the error is triggered.
 
rickysymo said:
Accessing programs from windows 2003 is very simple but it is also obsolete because from 2007 excel is better and easier to use.
Click to expand...
I'm sorry...isn't that supposed to be Windows XP, NT, 2000, or VISTA? Might be wrong, but I was under the impression that ALL programs are accessed through windows. Doesn't a computer need an OS to operate anyway?
 
Your problem is that you are not referring to an instantiated object with your code like:

Selection.NumberFormat = "General"

When you do that, Access instantiates another Excel Application object which then interferes with things. If you look in your Task Manager I think you will find extra instances of Excel open. These only go away if you close Access or End them from the Task Manager.

What you need is to refer to SELECTION with one of your objects (your appExcel object):

appExcel.Selection.NumberFormat = "General"


So find all the places where you just refer to SELECTION and change it to appExcel.Selection...etc.

and you should be fine.
 
Thanks to all for your help!

I was getting the error message at [Selection.NumberFormat = "General"] and I couldn't work out a way around it.

based on Smeghead's solution, I change the code to [Wks.Columns("M:N").NumberFormat = "0"] and now it works quite happily.

The problem started with long numbers being imported into Access as scientific format. Reformatting the excel columns to "General" work for some files but not others, and now I have changed to number "0" format, all the files seem to import correctly.

Again, thanks for the advice
Greebo
 
I know you have created a work around, but I wanted to tell you why your original code was failing ...

You are running this code from ACCESS, also, many times an Excel process remains in Memory with manipulating the Excel app model. With that, the Selection object does not seem to know how to imply its parent Excel application object as it can do inside of the Excel environment. The way to remedy this is to explicitly declare it's parent.

In addition, in working with Excel in this manner, I have found it very common to have left over Excel.exe processes running in the Task Manager, so ... Click Here for procedure named KillProcess() which will take care of that situation. I recommend running PRIOR TO your call to fncXLTabCheck1(), or with in the fncXLTabCheck1() procedure, prior to CreateObject().

Here is the implementation of my suggestions ...
Code: 
Function fncXLTabCheck1(frmM As Form, strPath As String, strImpFile As String, strType As String)
    
    Dim appExcel As Excel.Application
    Dim Wks As Excel.Worksheet
    
    'un-mercifully remove ALL excel processes from memory
    KillProcess "excel.exe"
    
    'Open excel
    Set appExcel = CreateObject("Excel.Application")
    appExcel.Workbooks.Open strPath & strImpFile, UpdateLinks:=0
    
    'rename the sheets
    For Each Wks In appExcel.Worksheets
        If Trim(UCase(Wks.Name)) = "MONTH" Then
            Wks.Name = "xxMonth-" & Format(Now(), "yyyymmddhhnn")
        End If
    Next
    
    'apply formating
    For Each Wks In appExcel.Worksheets
        If Trim(UCase(Wks.CodeName)) = "MONTH" Then
            Wks.Name = "Month"
            frmM!intSheet = frmM!intSheet + 1
            If strType = "OCEAN" Then
                Wks.Columns("M:N").Select
                appExcel.Selection.NumberFormat = "General"
            ElseIf strType = "AIR" Then
                Wks.Columns("L").Select
                appExcel.Selection.NumberFormat = "General"
                Wks.Columns("N").Select
                appExcel.Selection.NumberFormat = "General"
            End If
        End If
    Next
    
    'cleanup
    appExcel.ActiveWorkbook.Close SaveChanges:=True
    appExcel.Quit
    Set appExcel = Nothing
    
End Function
 
... Oh ...

Even though I indicated in my code to use the Selection object, MOST often it is DISCOURAGED to do so! ... you can usually work directly with stuff more efficiently...

In other words .. your 'work around' is actually better code than the 'fix' I provided to fix your error ...

In other words, this:

Wks.Columns("M:N").NumberFormat = "General"

Is better coding than this:

Wks.Columns("M:N").Select
appExcel.Selection.NumberFormat = "General"

....

Just wanted to post that since my initial response was just on why it seems the error occurred ...
 
Thanks for the compliment!

I will definitely use the 'KillProcess' and cleanup code

Thanks for your help

Greebo
 
DA

Can you please elaborate more on what the KillProcess does. Does it actually close all instances of Excel applications from task manager?

David
 
Hello DCrake ...

>> Can you please elaborate more on what the KillProcess does <<

I found the code on the internet as I was dealing with a pesky persistent Excel instance. The project I was working on was a contract job so thorough code analysis was not possible, so I implemented this code and it was like magic! I have looked at it, but honestly I have not taken the time to fully understand it. In brief, the code loops through all the active processes that are running in the task manager, then if the process matches the passed argument, that process is killed.


>> Does it actually close all instances of Excel applications from task manager? <<

Yes ... every one ... with out mercy or warning!! ... And it works any .exe, not just Excel.
 
Last edited:
Personally, I think I'd rather just make sure that I'm not using any code that spawns an unwelcome instance of Excel. Killing all Excel instances could be a bit painful to some users who might take issue with you killing their process when they might have gone through a lot of trouble to build something and haven't saved it.
 
>> Personally, I think I'd rather just make sure that I'm not using any code that spawns an unwelcome instance of Excel. <<

You are correct. My first exposure/use to the KillProcess code was in an enviroment that only used Excel as a reporting tool. The user could not even open Excel on there own. Also, as I indicated, time/money ruled the roost, thus the quick and dirty prevailed! ...

I have since implemented the in other Inherited Access apps, again with no time or money to properly rid the app of creating ghost instances. So, KillProcess() was my friend again. To avoid disgruntled folks, I would inform the user that an Excel process was found and was about to be toasted, so they should save it if it is visible and there own!.

It worked out very well.
 
ajetrumpet said:
I'm sorry...isn't that supposed to be Windows XP, NT, 2000, or VISTA? Might be wrong, but I was under the impression that ALL programs are accessed through windows. Doesn't a computer need an OS to operate anyway?
Click to expand...

i think it would be logical to assume the OP meant "office" 2003...
 
You must log in or register to reply here.

Similar threads

D
Solved Formatting Excel File from Access
Replies
5
Views
1,020
Eugene-LS
Eugene-LS
Jason Lee Hayes
Solved Open Excel From Access & Update Range
Replies
12
Views
1,883
Gasman
Gasman
B
Solved VBA code which exports data from Access to Excel and then loops through the Excel file
Replies
0
Views
722
BlueFllame
B
A
Import Excel Files
Replies
10
Views
1,042
CJ_London
C
A
Using Access to automate a Pivot Table in Excel
2
Replies
21
Views
4,366
Gasman
Gasman

Users who are viewing this thread

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