Thread: NULL pointer problem Error=[0x80040204]

Hi, there
I am trying to transfer data from Access to Excel. For a same piece of code, it is ok at first run but not the following runs. This error appears. Excel behaves OK after Click “OK”. The code has problem is “excWorkbook.Close”. Any suggestions will be highly appreciated.
Thanks
Lily.

It would be helpful if you posted your code for analysis.

What are you trying to transfer to Excel. A Query? Table?

Are you using the transferspreadsheet function?

Alan

Not a table or quary yet. Only some data from Access. Please see below. Thanks.

Option Compare Database


Sub WritingToExcel()
Dim excApp As Excel.Application, excWorkbook As Excel.Workbook
Dim AccApp As Access.Application
Dim FSO As Object

Dim strCurrentExcelName As String, strProjectPath As String, myText As String
Dim DateStarted As String
Dim ProductName As String
Dim StandardName As String


'the information below is for test only, to be change after testing
TEno = "TE32"
ProductName = "SomeThing"
StandardName = "SomeName"
StandardID = 5
strProjectPath = "U:\Project\Test" & "\" & Year(Now) & "\" & TEno
DateStarted = "1 / 1 / 2011"

'##############################
On Error Resume Next
'establish Access application as an object
Set AccApp = GetObject(, "Access.Application")

'establish Excel application as an object
Set excApp = GetObject(, "Excel.Application")
Set FSO = CreateObject("scripting.filesystemobject")
Set excWorkbook = GetObject(, "Excel.Application")

    If FSO.FolderExists(strProjectPath) = False Then
            MsgBox "Folder: " & strProjectPath & " doesn't exist. Check this file please."
            Exit Sub
    End If

' continue error trap
If excApp Is Nothing Then
        ' no current Excel application
        ' open Excel if it's closed
        
        Set excApp = CreateObject("Excel.application")
        Set excWorkbook = excApp.Workbooks.Open(strCurrentExcelName)
        excApp.Visible = True
        excApp.Activate
Else
        excApp.Visible = True
        excApp.Activate
        excApp.Application.WindowState = wdWindowStateMaximize
End If


        strCurrentExcelName = strProjectPath & "\" & StandardName & "_" & ProductName & ".xlsm"
        
         If FSO.FileExists(strCurrentExcelName) = False Then
            MsgBox strCurrentExcelName & " doesn't exist. Check this file please."
            Exit Sub
         End If

On Error Resume Next   'open the file if it not open
            'open the right file
            Set excWorkbook = excApp.Workbooks.Open(strCurrentExcelName)
            excApp.Workbooks(strCurrentExcelName).Activate
            excApp.Visible = True
            excApp.Activate
      
On Error GoTo Errorhandler:
'do the bit to transfer data to Excle
            excApp.Sheets("Main").Select
            excApp.ActiveSheet.Unprotect  ' before do anything, unprotect the sheet
            
             excApp.Range("ProductName").Value = ProductName
              excApp.Range("TargetDir").Value = strProjectPath
              excApp.Range("TENo").Value = TEno
              
             excApp.ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True  'pretect the sheet again
             excApp.ActiveSheet.EnableSelection = xlNoRestrictions  'pretect the sheet
            
            excWorkbook.Save
            excWorkbook.Close ' close the document
    
        excApp.Quit ' close the Excel application

Excel_Exit:
    Set excWorkbook = Nothing
    Set excApp = Nothing
    Set FSO = Nothing
    Set AccApp = Nothing
    
Exit Sub
              
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

Errorhandler:
         Call Error_Display(Err)
         
Resume Excel_Exit
End Sub

Firstly, I am not an Excel VBA expert. I do use code in Access to facilitate its use.

Specifically, what data are you trying to move from Access to Excel? Will the data be in a table or extracted from a table to a query? This is really important to know. There are built in functions that let you do that. Additionally, if you want to automate, there are some simple export codes that can be written in Access to do that.

Perhaps, if I understood what your objective was, then I might be able to help you.

Alan

Hi, Alan
I use Access as my application front end for user to input some data, then some of the inputted data will be transfered to an Excel file, which is predefined with certain NAMES. The Excel part of the code is OK until the line "excWorkbook.Close". My guts feeling is that something in the objects for Access and Excel application are used wrong, but I don't know what. The data I am trying to transfer to Excel is stored in a table, However, in the code above, I setup some dummy data for "Product" and "Standerd", which would be extracted from a table, just want to try if the code works OK.

Could you please help. Thanks. Regards, Lily

hi,

I agree with Alan, one of the types of inbuilt functionality is likely to be suitable for your task. What is your data?

In saying that, here's an attempted modification of your code.
Note, this is untested because I don't have access to a VBE at the moment, and it may also cause some errors, if I I've been too zealous in my commenting out unnecessary/incorrect (?) code. It may give you some ideas. I have reworded the code to always use a new instance of excel, while this may not be as memory efficient, I can say that it won't interfere with any existing work that you may be doing in excel (in parellel to running the Accss code).

Option Compare Database

Sub WritingToExcel()
Dim excApp As Excel.Application, excWorkbook As Excel.Workbook
'Dim AccApp As Access.Application
'Dim FSO As Object

Dim strCurrentExcelName As String, strProjectPath As String, myText As String
Dim DateStarted As String
Dim ProductName As String
Dim StandardName As String


'the information below is for test only, to be change after testing
'RB: if these remain static in your main code, I recommend changing them to Constants.
TEno = "TE32"
ProductName = "SomeThing"
StandardName = "SomeName"
StandardID = 5
strProjectPath = "U:\Project\Test" & "\" & Year(Now) & "\" & TEno
DateStarted = "1 / 1 / 2011"

'##############################
'RB, removed to prevent masking of errors... On Error Resume Next
'establish Access application as an object. 'RB: if the code is being used in Access "I think" it can be changed from...
'Set AccApp = GetObject(, "Access.Application")
to
Set AccApp = application
'RB: I don't think the above variable is even used, is it?

'establish Excel application as an object
'Set excApp = GetObject(, "Excel.Application")
'Set FSO = CreateObject("scripting.filesystemobject")
'Set excWorkbook = GetObject(, "Excel.Application")
'RB: instead of the above, try this...
Set excApp = new excel.application


If FileFolderExists(strProjectPath) Then
'carry on...
else
MsgBox "Folder: " & strProjectPath & " doesn't exist. Check this folder please."
Exit Sub
End If

'RB: removed due to the above use of "new excel application"
'' continue error trap
''If excApp Is Nothing Then
'' no current Excel application
'' open Excel if it's closed
''Set excApp = CreateObject("Excel.application")

strCurrentExcelName = strProjectPath & "\" & StandardName & "_" & ProductName & ".xlsm"

If FileFolderExists(strCurrentExcelName) Then
'carry on
else
MsgBox strCurrentExcelName & " doesn't exist. Check this file please."
Exit Sub
End If



with excApp
.Visible = True
.Activate
.WindowState = wdWindowStateMaximize
'RB: remove error masking... On Error Resume Next 'open the file if it not open
'open the right file
Set excWorkbook = .Workbooks.Open(strCurrentExcelName)
end with


On Error GoTo Errorhandler:
'do the bit to transfer data to Excle
with excWorkbook
with .Sheets("Main")
.Select
.Unprotect ' before do anything, unprotect the sheet
end with

.Range("ProductName").Value = ProductName
.Range("TargetDir").Value = strProjectPath
.Range("TENo").Value = TEno

with .Sheets("Main")
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True 'pretect the sheet again
.EnableSelection = xlNoRestrictions 'pretect the sheet
end with

.Close save:=true ' save & close the document
end with

Excel_Exit:
Set excWorkbook = Nothing
excApp.Quit ' close the Excel application
Set excApp = Nothing
'Set FSO = Nothing
'Set AccApp = Nothing

Exit Sub

'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

Errorhandler:
Call Error_Display(Err)

Resume Excel_Exit
End Sub

'RB: helper function...

Public Function FileFolderExists(strFullPath As String) As Boolean
'Author       : Ken Puls (www.excelguru.ca)
'sourced from: http://www.excelguru.ca/node/30
'Macro Purpose: Check if a file or folder exists

    On Error GoTo EarlyExit
    If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True
    
EarlyExit:
    On Error GoTo 0
End Function

I have my fingers crossed...

hth
Rob

Sorry, I just read your last post properly.

Here's a link to a copyfromrecordset example that can pull data from an Access table, this would need some changes made, but you may be able to merge it with my suggestions...
http://www.exceltip.com/st/Import_da...Excel/428.html

hth
Rob

Hi, Rob
Thanks a lot. It works! Additionally, there is no error when the Excel files manipulated through Access is re-opened. Everything works perfectly. Regards, Lily.

hi Lily,

Thanks for the feedback, adding to my rep' and marking the thread as Solved :-)

Rob

hi Lily,

I'm sorry, this isn't really an answer but it may help.

When I read your last post I thought, "oh, that's easy, it's because you have some excel object references that aren't explicitly defined all the way up to the xlApp reference". However, then I went back to re-read your code to get the exact line where that would be occurring & I couldn't see any object (eg range/worksheet/workbook) references that didn't have explicit referencing to the xlApp level. So... I think it will relate to your use of "GetObject". The following link may help you understand more about how Getobject works: http://www.excelforum.com/excel-prog...utomation.html


hth
Rob