Solved

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

Posted on 2013-06-18
8
1,232 Views
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 & "]"
Else
    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)"
Else
    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
cnct32.Open

  
'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
    .Open
 End With
rst.ActiveConnection = Nothing 'disconnect from the connection
cnct32.Close
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
Next

' 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
oExcel.ActiveSheet.Columns("A:L").AutoFit

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

End Function

Open in new window

0
Comment
Question by:cmrobertson
  • 4
  • 2
8 Comments
 
LVL 11

Expert Comment

by:jkpieterse
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:

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

with:

Set oSheet = Nothing
oBook.Close False
Set oBook = Nothing
oExcel.Quit
Set oExcel = Nothing
Exit Function
0
 
LVL 7

Author Comment

by:cmrobertson
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
0
 
LVL 11

Expert Comment

by:jkpieterse
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?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 7

Author Comment

by:cmrobertson
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.
0
 
LVL 7

Accepted Solution

by:
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
0
 
LVL 7

Author Closing Comment

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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

706 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now