Create .xlsx from recordset with r/w access in vb6

Posted on 2013-06-18
Last Modified: 2013-06-28
I am running a sql query against an access database into an ado recordset and I need to create a copy in excel that I can read preferably into the same recordset at a different time. Everything seems to work well but when I try to read in the created Excel file into the recordset I am getting an error that its a read only file. If I check the file permissions it does not indicate it is read only. I have tried changing the access permissions at write time but the problem still exists. Here are the functions to populate the database and then write the excel file. Can anyone see why this isnt working?

Function PopulateRS(pFileString As String, pTableName As String, Optional pExcel As Boolean) As ADODB.Recordset
On Error GoTo populaters_error
Dim rst As New ADODB.Recordset
Dim strSQL As String
If pExcel Then
    'pTableName doubling as SheetName as well
    strSQL = "SELECT * FROM [" & pTableName & "]"
    strSQL = "SELECT ' ' as [Status],,[Client Name],[Client Number],[Vendor Name],[VendorID], ' ' as SalesEmail FROM [" & pTableName & "] 
End If
If pExcel Then
    strDriver = "Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)"
    strDriver = "Microsoft Access Driver (*.mdb, *.accdb)"
End If
cnct32.ConnectionString = "Driver=" & strDriver & ";" & _
                          "DBQ=" & pFileString & ";" & _
                          "UID=admin;UserCommitSync=Yes;ReadOnly=False; IMEX=1;"
cnct32.CursorLocation = CursorLocationEnum.adUseClient

'create a disconnected recordset to allow updating offline to the record for logging purposes
 With rst
    .CursorLocation = adUseClient
    .LockType = adLockPessimistic
    .Source = strSQL
    Set .ActiveConnection = cnct32
 End With
rst.ActiveConnection = Nothing 'disconnect from the connection
Set cnct32 = Nothing
Set PopulateRS = rst
Set rst = Nothing
Exit Function
Public Function WriteXLS(pRS As ADODB.Recordset, psheetname As String, pFileOut As String)
On Error GoTo writexls_error
'Create a new workbook in Excel
Dim objFields As ADODB.Fields
Dim intLoop As Integer
Dim oExcel As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
'Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
oBook.Worksheets(1).Name = psheetname
Set oSheet = oBook.Worksheets(1)
'Transfer the data to Excel
oExcel.DisplayAlerts = False
Set objFields = pRS.Fields
For intLoop = 0 To (objFields.Count - 1)
    Debug.Print objFields.Item(intLoop).Name
    oExcel.Application.cells(1, intLoop + 1) = objFields.Item(intLoop).Name

' Format A1:N1 as bold, vertical/horizontal alignment = center.
With oSheet.Range("A1", "L1")
   .Font.Bold = True
   '.VerticalAlignment = xlVAlignCenter
   '.HorizontalAlignment = xlVAlignCenter
End With
oSheet.Range("A2").CopyFromRecordset pRS 'buRS

'Save the Workbook and Quit Excel
oBook.SaveAs pFileOut, Excel.XlFileFormat.xlOpenXMLWorkbook, , , , , xlShared
Set oSheet = Nothing
Set oBook = Nothing
Set oExcel = Nothing
Exit Function

End Function

Open in new window

Question by:cmrobertson
  • 4
  • 2
LVL 11

Expert Comment

ID: 39259126
Dimming oExcel as New isn't recommended, as a new instance of an Excel app is created as soon as you refer to oExcel when oExcel is uninstantiated. I expect this is causing part of your problem.

Best to do:

Dim oExcel As Excel.Application
Set oExcel = New Excel.Application
'remainder of code goes here

Also, if any object variable still points to a child object of the oExcel object, the Excel application will stay alive. So the order of setting things to nothing is important as well.

replace this:

Set oSheet = Nothing
Set oBook = Nothing
Set oExcel = Nothing
Exit Function


Set oSheet = Nothing
oBook.Close False
Set oBook = Nothing
Set oExcel = Nothing
Exit Function

Author Comment

ID: 39259189
Originally I was not using "new" I was using an object the problem still existed. Also I am running the files completely separate of one another ending the program that created the excel file and then reading it fresh and getting the error message that its read only. I can try looking at the running processes before running the 2nd instance but I am pretty confident everything is closed. You do have me curious as to dimming with new though I don't really see the difference given it is instantiated and used immediately
LVL 11

Expert Comment

ID: 39259383
Try this code:

Dim oXL As New Excel.Application
If oXL Is Nothing Then
Msgbox "Excel has not been started!"
End If

You will NEVER encounter the msgbox because oXL will never be nothing.

Now remove the New keyword and try again.
(after testing make sure you weed out the hanging Excel instances :-) )

What happens if you omit the accessmode argument form the SaveAs method?
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.


Author Comment

ID: 39264646
The accesmode was added to the SaveAs after it wouldn't work for me so it works the same with or without. I create the Excel file and shut down the program there are no stray processes open. I use ADO to open Excel and it fails because the file is readonly if I check the properties it is not readonly if I open with Excel it is also not read only.

Accepted Solution

cmrobertson earned 0 total points
ID: 39270124
I have discovered the problem. I was accidentally passing the access database name instead of the spreadsheet (same names different extensions) Yikes that's embarrasing

Author Closing Comment

ID: 39283797
I discovered an error that could not be apparent in the code sample provided but could help if someone has similar symptoms

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question