CopyfromRecordset: copy text only ?

[vadius]

Hello

I have managed to use ADO to copy data fron a closed bookd to my activesheet. Problem : it copies only the text, and not the dates or figures...So it' suseless. Can anyone help on this ?

Thanks


Sub TestReadDataFromWorkbook()
' fills data from a closed workbook in at the active cell.
GetDataFromClosedWorkbook "H:\P&L\YE Temp\Div P&L\P&L Report 020312.xls", "A1:Z1000", Range("A1"), False
End Sub


Sub GetDataFromClosedWorkbook(SourceFile As String, SourceRange As String, _
TargetRange As Range, IncludeFieldNames As Boolean)

'GetDataFromClosedWorkbook "C:\FolderName\WorkbookName.xls", "A1:B21", ActiveCell, False
'GetDataFromClosedWorkbook "C:\FolderName\WorkbookName.xls", "MyDataRange", Range("B3"), True

' requires a reference to the Microsoft ActiveX Data Objects library
' if SourceRange is a range reference:
' this will return data from the first worksheet in SourceFile
' if SourceRange is a defined name reference:
' this will return data from any worksheet in SourceFile
' SourceRange must include the range headers
'
Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset
Dim dbConnectionString As String
Dim TargetCell As Range, i As Integer

dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};" & _
"ReadOnly=1;DBQ=" & SourceFile
Set dbConnection = New ADODB.Connection
On Error GoTo InvalidInput
dbConnection.Open dbConnectionString ' open the database connection

Set rs = dbConnection.Execute("[" & SourceRange & "]")
Set TargetCell = TargetRange.Cells(1, 1)
If IncludeFieldNames Then
For i = 0 To rs.Fields.Count - 1
TargetCell.Offset(0, i).Formula = rs.Fields(i).Name
Next i
Set TargetCell = TargetCell.Offset(1, 0)
End If
TargetCell.CopyFromRecordset rs
rs.Close
dbConnection.Close ' close the database connection
Set TargetCell = Nothing
Set rs = Nothing
Set dbConnection = Nothing
On Error GoTo 0

Range("A1:Z1000").NumberFormat = 0#


Exit Sub
InvalidInput:
MsgBox "The source file or source range is invalid!", _
vbExclamation, "Get data from closed workbook"
End Sub

[Bob Phillips]

Can you post the other workbook?

[vadius]

The other woorkbook is just a simple blank excel sheet.

[Kenneth Hobs]

Make a mock example for us to test if you like.

Obviously, date formats or other special formats will not be copied nor formulas. Databases only know text and numbers. Dates are really just numbers.

You may have to use the brute strength method if you want formatting and formulas.

[vadius]

OK. attached the source file. I tried with a blank workbook where I just entered numbers and dates and the macro works well and copies everything. The problem comes from my original source. Attached a sample

[Kenneth Hobs]

Your data is not set up in database format. That is why you have the problem. By default, it makes a best guess for the data type for each field or column by taking the first 3 data rows or records and looks at those data types. That is problem with using Excel as a database but then don't follow database rules.

You will need another approach.

[vadius]

Ok. And there is no way to manipualte the data to put them as a database so that I can use the code after ? Thanks

[Kenneth Hobs]

Yes, you can do that but it is probably not worth the effort. I would just open it in the foreground, do what you need, and then close it.

I will be busy for a few days so I won't be able to help until then.