By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,950 Members | 1,032 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,950 IT Pros & Developers. It's quick & easy.

Transfer array to excel worksheet using vb in ms access

P: 5
This is one of those Access is not closing Excel problems.
The first time through the code works fine. The second time there is a problem.
After lots of combinations, I finally determined that if I take out the line that copies the temp array to cells in a worksheet, Access will close the Excel file. If the line is there, Excel remains open and blocks more runs of the same procedure. If I close Access, Excel gets closed. Looks like somehow Access is calling another copy of Excel. Is there a way to keep the array processing and copy in the Excel that is open? I saw other web articles about explicit references, but I do not know how to make an explicit reference to TempArray to get it to stay in the open copy of Excel.
Private Sub cmdExcelTest3_Click()
On Error GoTo Err_cmdExcelTest3_Click
'This procedure transfers an array to a spreadsheet

'Setup the workbook variables
Dim objExcel As Excel.Application
Dim objListBook As Excel.Workbook
Dim objListSheet As Excel.Worksheet
Dim FileInfoRange As Excel.Range
'Setup the range variables
Dim CellsDown As Long, CellsAcross As Integer
Dim r As Long, c As Integer 'r=row, c=column
'Setup the temp array variable
Dim TempArray() As String
'Create the Excel object
Set objExcel = New Excel.Application
objExcel.Visible = True
'Setup the workbook and worksheet
With objExcel
Set objListBook = .Workbooks.Add
On Error Resume Next
Set objListSheet = objListBook.Worksheets.Add
objListSheet.Name = "Source Files"
End With 'for creating workbook and worksheet
'Set the dimensions of the range
CellsDown = 20
CellsAcross = 6
'Redimension temp array
ReDim TempArray(1 To CellsDown, 1 To CellsAcross)
'Activate the workbook and worksheet
On Error GoTo Err_cmdExcelTest3_Click
'Fill the temp array
Dim intValue_r As Integer 'number generator
Dim intValue_c As Integer
r = 1 'set the starting value
c = 1 'set the starting value
For r = 1 To CellsDown
intValue_r = intValue_r + 1
For c = 1 To CellsAcross
intValue_c = intValue_c + 1
TempArray(r, c) = intValue_r + intValue_c
Next c
Next r
'Transfer temp array to worksheet via FileInfoRange
'This is the problem line.
'FileInfoRange.Value = TempArray
objExcel.Application.ActiveSheet.Range(Cells(3, 1), _
Cells(CellsDown, CellsAcross)).Value = TempArray
'Save the workbook
'Create the filename
strListofFiles = "List of Source Files.xls"
'Save the workbook to the filename
objListBook.SaveAs strListofFiles
On Error GoTo Err_cmdExcelTest3_Click
'Clear the object variables.
Erase TempArray()
Set FileInfoRange = Nothing
Set objListSheet = Nothing
Set objListBook = Nothing
objExcel.DisplayAlerts = True
Set objExcel = Nothing
Exit Sub
'Clear the variables
Set ListSheet = Nothing
Set ListBook = Nothing
Set objExcel = Nothing
Erase TempArray()
MsgBox Err.Description
Resume Exit_cmdExcelTest3_Click
End Sub

May 12 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.