Simple VBA crashes Access 2007, why?

[headly]

vba to open excel, transfer to spreadsheet, run an autoopen macro, and return
to access causes access 2007 on windows Xp to hang/crash with a white screen
and not responding in the title bar; Any idea where/why it is wrong? TIA

Private Sub Button___Social_Policy_Click()

'Get the path to the database
Dim vPath As Variant
vPath = Application.CurrentProject.Path

'Get the path to local user my documents folder
Dim vMyDocsPath As Variant
vMyDocsPath = Environ$("USERPROFILE") & "\My Documents\"

'If Directory exists, Mkdir causes an error - ignore it
On Error Resume Next

'Create a new folder inside my documents
MkDir vMyDocsPath & "\Staff Functional Reports\"
ChDir vMyDocsPath & "\Staff Functional Reports\"

'Before get the record date - test to see if a value was chosen
If Me.Record_Date___Social_Policy.Value Is Not Null Then

'Get the record date value - Used to name the file
Dim vRecordDate As Variant, vMonth As Variant, vDay As Variant, vYear As
Variant
vRecordDate = Me.Record_Date___Social_Policy.Value
'vRecordDate is not usable format i.e. 11/1/09; Need to convert
'to MMM-DD-YYYY format to use in filename
vMonth = Left(vRecordDate, InStr(1, vRecordDate, "/") - 1)
vYear = Right(vRecordDate, 4)
vDay = Mid(vRecordDate, InStr(1, vRecordDate, "/") + 1, Len(vRecordDate)
- Len(vYear) - Len(vMonth) - 2)

'run the query
DoCmd.OpenQuery ("Functional Report - Social Policy")

'Hide the form
Me.Visible = False

'Export to excel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"Functional Report - Social Policy", "Functional Report - Social Policy
" & vMonth & "-" & vDay & "-" & vYear

'Start Excel and Open the file
Dim objXL As Object, x As Variant
Set objXL = CreateObject("Excel.Application")
With objXL.Application
.Visible = True
'Open the Workbook
.Workbooks.Open vMyDocsPath & "\Staff Functional Reports\" & _
"Functional Report - Social Policy " & vMonth & "-" & vDay & "-"
& vYear
'In Excel, use the database path, vpath to open the xl formatting
macro
.Workbooks.Open vPath & "\" & "VBA Development - Report Formatting -
DIST.xlsm"
.ActiveWorkbook.RunAutoMacros
End With

'Wait 30 seconds, then force close the VBA Development - Report
Formatting - DIST.xlsm macro
Dim vPauseTime As Variant, vStartTime As Variant
vPauseTime = 30
vStartTime = Timer
Do While Time < vStartTime + vPauseTime
'Do Nothing, just wait
Loop

'Now force close the XL Macro workbook for other users
With objXL.appliction
.Workbooks.Close vPath & "\" & "VBA Development - Report Formatting
- DIST.xlsm"
End With

Set objXL = Nothing

'Show the form
Me.Visible = True

ElseIf Me.Record_Date___Social_Policy.Value = "" Or
Me.Record_Date___Social_Policy.Value Is Null Then

MsgBox "Please choose a record date before running the report . . .",
vbInformation + vbOKOnly, "Date Required"
Me.Record_Date___Social_Policy.SetFocus
Exit Sub

End If

 

[Stefan Hoffmann]

hi,

vba to open excel, transfer to spreadsheet, run an autoopen macro, and return
to access causes access 2007 on windows Xp to hang/crash with a white screen
and not responding in the title bar; Any idea where/why it is wrong? TIA

First of all, it's not simple. What can you do?

1. Don't declare your variables as Variant, use the appropriate type
instead.

2. Use an error handler:

Private Sub Test()

On Local Error GoTo LocalError

'your code

Exit Sub

LocalError:
MsgBox Err.Description

End Sub

3. Declare all variables before any other code, directly after the error
handler.

4. Do not use With blocks.

5. For testing purposes use early binding of all your Excel objects.

6. Where exactly does it crash? Is it caused by the RunAutoMacros? Why
do you need to call it? Especially the delay in Excel may cause the
havoc. Try removing it.


mfG
--> stefan <--

 

[vanderghast]

On Vista ? Have you applied SP1 (http://support.microsoft.com/kb/942378) ?
Even so, it can also be a problem of security (time out not having the
required rights to access the folders) Pure guess from my part, but have you
tried to bring the Excel file in the same directory than your Access
application (just for testing, to see if the bug is still there)? or in a
trusted location (and hard code the full path, NO mapped drive) ? Again,
that is pure guess from my part.


Vanderghast, Access MVP