Intermitant Excel Automation Error 1

[chrish47]

Hi,

I am fairly new to VBA, I know what I want to do, I am just having some problems getting there. I am creating a routine to automate inserting some data in to an Excel Spreadsheet. I have a "template" set up in Excel that contains format, layout, macros, etc. I want to find the size of the data I am going to be inserting, then resize the sheet (using ranges and macros in the Excel sheet), I have this coded and generally it seems to work good, but every other time I run this I get an error. Excel opens, but just hangs instead of inserting the rows and then inserting the data.

Here is the relevant code, up to where I am getting the error:

Function OutputJVtoExcel()

Dim objXL As Excel.Application
Dim objWKB As Excel.Workbook
Dim objSHT As Excel.Worksheet
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim rngDataRange As Range
Dim varNumRows As Integer
Dim intInsertRow As Integer
Dim intCounter As Integer
Dim calMO As String, calYR As String, calEOM As String, calMON As String, calFY As String
Dim JVDate As String, JVRef As String, JVNote As String
Const conSHEET1 = "Journal Voucher" 'name of worksheet to Update

'full path to the Use Tax JV template file
Const ConWKBK = "S:\Busoff\Autofeed\UT Rollup\Source\UT JV.xls"

Set db = CurrentDb
Set objXL = New Excel.Application
With objXL
.Visible = True
Set objWKB = .Workbooks.Open(ConWKBK)

'Populate first worksheet

'enter SQL statement for data to put into Recordset
strSQL = "SELECT OUTPUT.* FROM [OUTPUT];"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Get count of Recordset for # of Rows to insert into Template
rs.MoveLast
varNumRows = rs.RecordCount 'Count of Recordset
varNumRows = varNumRows - 1 'Subtract 1 for existing row in template
rs.MoveFirst

'Activate the Worksheet
Set objSHT = objWKB.Worksheets(conSHEET1)
With objSHT

'Set DataRange for use in Inserting Rows

*** Set rngDataRange = Range("JV_DataAll&quot

Excuse the messy code, I am more of a functional specialist than a programmer, so my syntax is rather messy (especially in the development stages). Do I need to somehow specify that Range("JV_DataAll&quot is a range defined in the Excel sheet, and if so why does it work every other time I run this?

The *** is the line that is highlighted when I get the error, and the error message is:

Runtime Error '1004':
Method 'range' of object '_Global' failed

When I click on Debug VBA highlights the line marked with *** above. I am not sure why it works one time, but the next it hangs. Here is the rest of the code from the above point on (in case I am doing something that is causing this problem):

Set rngDataRange = rngDataRange.Offset(1, 0) _
.Resize(rngDataRange.Rows.Count - 2, rngDataRange.Columns.Count)
intInsertRow = rngDataRange.Row + rngDataRange.Rows.Count
Cells(intInsertRow, rngDataRange.Column).Select

'Insert the rows
intCounter = 0
For intCounter = 1 To varNumRows
rngDataRange.Parent.Rows(intInsertRow).Insert
Next intCounter
'Insert the data into the rows that were just inserted
.Range("B10&quot.CopyFromRecordset rs
End With


'set focus back to first Sheet before saving
Set objSHT = objWKB.Worksheets(conSHEET1)
objSHT.Activate

With CurrentProject.Connection.Execute("[FD Backup]", , 0)
calYR = .Fields("CY&quot
calMO = .Fields("Month&quot
calEOM = .Fields("Days&quot
calMON = .Fields("Month#&quot
End With

calFY = Right(calYR, 2)
JVRef = "'" + calMO + calFY
JVDate = calMON + "/" + calEOM + "/" + calFY
JVNote = calMO + " " + calYR + " Use Tax Rollup Journal. Prepared by Use Tax JV Database."


With objSHT
.Range("C5&quot.Value = JVRef
.Range("C6&quot.Value = JVDate
.Range("JV_Note&quot.Value = JVNote

'save the workbook - will add routine to change name of file
GetJVDest
'Save Workbook as the current Month's JV
objWKB.SaveAs (JVDest)
'full path to saved location
objXL.Run ("AA_DoJvFeedFileProcess&quot
'call the JV Feed Process to create the feed file.
objWKB.Close SaveChanges:=False
objXL.Quit
End With


End With

Set objSHT = Nothing
Set objWKB = Nothing
Set objXL = Nothing
Set rs = Nothing
Set db = Nothing

DoCmd.OpenForm "Header"

End Function

Any help or ideas on what is causing this error would be greatly appreciated (and any comments on my code or ways to improve it would also be welcomed).

Thanks,
Chris
CWU

 

[VBslammer]

Looks like you're missing a dot:

[tt]Set rngDataRange = .Range("JV_DataAll&quot[/tt]




VBSlammer

Unemployed in Houston, Texas

 

[VBslammer]

I seen another one here:

[tt].Cells(intInsertRow, rngDataRange.Column).Select[/tt]

Microsoft has documented these kind of problems in the knowledge base, stemming from implicit references in your code. Make sure all of your object references are explicit and you should be fine.

Also, you aren't telling Excel to quit before destroying your reference:

objXl.Quit

VBSlammer

Unemployed in Houston, Texas

 

[VBslammer]

You did call quit - I missed that.

VBSlammer

Unemployed in Houston, Texas

 

[chrish47]

Thanks,

That fixed the problem. I should have known it was something small that I overlooked.

Thanks Again,
Chris