I'm coding a VBA Sub that populates a new Excel workbook with data from my current Excel workbook, by using the 'Data' sheet in my current workbook as a ADODB. However, the newly-populated sheet has missing data in some cells.
All the relevant rows and columns are retrieved, with the right number of rows returned as well. The problem is that some data is missing from certain cells.
For instance, in the Data sheet, cell F5 may be:
"Matthew lives on XYZ street, right on the corner of Tesco. He has 5 dogs - 2 golden retrievers and 3 toy poodles. Matthew hates cats though - he thinks they're horrible animals that will one day take over the world and kill every single human being"
but on Sheet1 of Book1 (the results workbook), the cell will just be:
"Matthew lives on XYZ street, right on the corner of Tesco. He has 5 dogs - 2 golden retrievers and 3 toy poodles. Matthew hates cats though - he thinks they're horrible ani"
I.e., every single row and column I need is retrieved, but some of the data is incomplete.
Random note: I made that entire paragraph up on the spot.
The code I used was:
<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">Dim cnStr As String
Dim rs As ADODB.recordSet
Dim query As String
Dim fileName As String
Dim pathExcel As String
Dim pathExcelActual As String
pathExcel = Application.Workbooks("Book_of_records").Path
pathExcelActual = pathExcel + "\Book_of_records.xlsm"
fileName = pathExcelActualcnStr = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & fileName & ";" & _ "Extended Properties=Excel 12.0"
query = "SELECT user_name, user_details, user_address, user_phoneNumber, user_spouse FROM [Data$]"
Set rs = New ADODB.recordSet
rs.Open query, cnStr, adOpenUnspecified, adLockUnspecified
Dim iRow As Integer
Dim rst As ADODB.recordSet
Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim fldCount As Integer
Dim iCol As Integer
If rs.BOF And rs.EOF Then
MsgBox "No Data", vbOKOnly, "No Data"
Exit Sub
End If
rs.MoveFirst
Debug.Print rs.RecordCount
iRow = rs.RecordCount + 1
Set xlApp = New Excel.Application
Set xlWb = xlApp.Workbooks.Add
Set xlWs = xlWb.Worksheets("Sheet1")
xlApp.Visible = True
xlApp.UserControl = True
fldCount = rs.Fields.Count
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = rs.Fields(iCol - 1).Name
Next
rs.MoveFirst
xlWs.Range("A2").CopyFromRecordset rs
</code>Am I doing something wrong? Any help at all will be very much appreciated! Thanks so much
All the relevant rows and columns are retrieved, with the right number of rows returned as well. The problem is that some data is missing from certain cells.
For instance, in the Data sheet, cell F5 may be:
"Matthew lives on XYZ street, right on the corner of Tesco. He has 5 dogs - 2 golden retrievers and 3 toy poodles. Matthew hates cats though - he thinks they're horrible animals that will one day take over the world and kill every single human being"
but on Sheet1 of Book1 (the results workbook), the cell will just be:
"Matthew lives on XYZ street, right on the corner of Tesco. He has 5 dogs - 2 golden retrievers and 3 toy poodles. Matthew hates cats though - he thinks they're horrible ani"
I.e., every single row and column I need is retrieved, but some of the data is incomplete.
Random note: I made that entire paragraph up on the spot.
The code I used was:
<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">Dim cnStr As String
Dim rs As ADODB.recordSet
Dim query As String
Dim fileName As String
Dim pathExcel As String
Dim pathExcelActual As String
pathExcel = Application.Workbooks("Book_of_records").Path
pathExcelActual = pathExcel + "\Book_of_records.xlsm"
fileName = pathExcelActualcnStr = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & fileName & ";" & _ "Extended Properties=Excel 12.0"
query = "SELECT user_name, user_details, user_address, user_phoneNumber, user_spouse FROM [Data$]"
Set rs = New ADODB.recordSet
rs.Open query, cnStr, adOpenUnspecified, adLockUnspecified
Dim iRow As Integer
Dim rst As ADODB.recordSet
Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim fldCount As Integer
Dim iCol As Integer
If rs.BOF And rs.EOF Then
MsgBox "No Data", vbOKOnly, "No Data"
Exit Sub
End If
rs.MoveFirst
Debug.Print rs.RecordCount
iRow = rs.RecordCount + 1
Set xlApp = New Excel.Application
Set xlWb = xlApp.Workbooks.Add
Set xlWs = xlWb.Worksheets("Sheet1")
xlApp.Visible = True
xlApp.UserControl = True
fldCount = rs.Fields.Count
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = rs.Fields(iCol - 1).Name
Next
rs.MoveFirst
xlWs.Range("A2").CopyFromRecordset rs
</code>Am I doing something wrong? Any help at all will be very much appreciated! Thanks so much
Last edited:
