Hello all,
First, I should say that this is running in Wscript (vbscript). My code works on closed workbooks, but not on open ones.
I create a recordset on a foreign workbook that may or may not be open; and the call copyfromrecordset on a cell within a worksheet object.
When the remote worksheet is open, I can do a msgbox test and print the records in the recordset with an rs.getstring, therefore I believe the issue revolves around the copyfromrecordset object. It runs, but inserts no data into the worksheet when the remote sheet is open (and in read-only mode).
I apologize for not having any code to post at the moment, I thought I had brought it with me.
Has anyone experienced this issue?
Thank you for any help you may be able to offer.
CopyFromRecordSet Not Inserting Info Into Worksheet
-
-
-
Re: Copyfromrecordset Not Inserting Info Into Worksheet
Hello, I'm sorry, for my first post I managed to put it in the wrong category. Would a moderator please move this to the appropriate category? I'm assuming it should go in the general excel/vba help.
Thanks and apologies.
-
Re: Copyfromrecordset Not Inserting Info Into Worksheet
The code is as follows (vbscript):
Code
Display MorePrivate Sub CopySheet(path, destSheet) 'path is a string and destSheet is a worksheet Dim conn ' ADODB Connection Object Dim rs ' ADODB Recordset Object Dim colIndex ' Integer ' Create the ADODB connection and recordset objects set conn = CreateObject("ADODB.Connection") set rs = CreateObject("ADODB.Recordset") ' Open connection to excel sheet conn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _ "DriverId=790;Dbq=" & path & ";" ' Create recordset of all data on sheet 1 rs.Open "SELECT * FROM [Sheet1$]", conn ' Copy the column headers to the destination worksheet For colIndex = 0 To rs.Fields.Count - 1 destSheet.Cells(1, colIndex + 1) = rs.Fields(colIndex).Name Next ' Test debug msgbox ' msgbox rs.GetString ' Copy the data to the destination worksheet destSheet.Cells(2, 1).CopyFromRecordset rs ' Format the sheet bold and auto width of columns destSheet.Rows(1).Font.Bold = True destSheet.UsedRange.Columns.AutoFit ' Close ADODB objects rs.Close conn.Close ' Housekeeping Set rs = Nothing Set conn = Nothing End Sub
-
Re: Copyfromrecordset Not Inserting Info Into Worksheet
Moved to correct forum.
EDIT : Carl - Moved to SQL
-
Re: CopyFromRecordSet Not Inserting Info Into Worksheet
I have not seen the exact problem before but ...
There is a memory leak issue with open workbooks and ADO. You can work round it but the solutions appear to be version specific.
see http://www.ozgrid.com/forum/showthread.php?t=56748
If the recordset appears to be there maybe changing the cursor/ disconnecting the connection before you drop it into the range would help ?
-
-
Re: CopyFromRecordSet Not Inserting Info Into Worksheet
I forgot to mention that the code:
Code' Copy the column headers to the destination worksheet For colIndex = 0 To rs.Fields.Count - 1 destSheet.Cells(1, colIndex + 1) = rs.Fields(colIndex).Name Next
prints the header information every time, but not the meat of the data.
Closing the connection object before the copy will result in a runtime error.Also, while I haven't tried every permutation of cursor/lock, I've tried a few that would be intuitive alternates and none of that worked either.
It would be one thing if this simply didn't work ... but the fact that the column headers insert into the destination headers and I can msgbox the rs.toString and see the data; makes this very frustrating.
-
-
Re: CopyFromRecordSet Not Inserting Info Into Worksheet
just try to movefirst before copyfromrecordset
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!