sifar786
02-14-2020, 12:22 PM
I have some aphanumeric data in an Excel Sheet of a Closed Workbook named `temp.xlsx`. I am creating an `ADODB Recordset` by an SQL Query to pull this range into my current Workbook. The problem i am facing is that some of the column headers in the Sheet are more than 64 characters in length. This results in the ADO field names getting truncated to 64 characters once i import the range into an ADO recordset.
Public Const adCmdText = 1
Public Const adOpenDynamic = 2
Public Const adUseServer = 2
Public Const adOpenStatic = 3
Public Const adUseClient = 3
Public Const adLockBatchOptimistic = 4
Public Const adTypeBinary = 1
Public Const adTypeText = 2
' Dim oStream As Object
' Dim oCmd As Object
Dim oCon As Object
Dim oRS As Object
Dim strSQL As String
Dim strFilePath As String
' Set oStream = CreateObject("ADODB.Stream")
' Set oCmd = CreateObject("ADODB.Command")
' Set xlXML = CreateObject("MSXML2.DOMDocument")
' Set XL = GetObject(, "Excel.Application")
' Set WB = XL.ActiveWorkbook
' xlXML.LoadXML Replace(WB.Sheets("Sheet1").Cells(1, 1).CurrentRegion.value(xlRangeValueMSPersistXML), "rs:name="" ", "rs:name=""") 'xlRangeValueXMLSpreadsheet
' oRS.Open xlXML, CursorType:=adOpenStatic, LockType:=adLockBatchOptimistic
Set oCon = CreateObject("ADODB.Connection")
Set oRS = CreateObject("ADODB.Recordset")
strFilePath = ThisWorkbook.Path & Application.PathSeparator & "temp1.xlsx"
With oCon
.Provider = "Microsoft.Ace.OLEDB.12.0"
.Properties("Extended Properties") = "Excel 12.0; HDR=Yes;IMEX=1;"
.CursorLocation = adUseClient
.Open strFilePath
End With
strSQL = "SELECT * FROM [Sheet1$]"
With oRS
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
.activeconnection = adoCon
.Open (strSQL)
End With
End Sub
Is there an alternative method to ensure that the full column name characters gets imported into the ADO Recordset from a CLOSED Excel Workbook i.e. overcoming the 64 character limitation of Fields?
Can an ADO.STREAM object do the job maintaining the column names OR Can an MSXML2.DOMDocument.Load File or .LoadXML do the trick?
Any help would be most appreciated.
Public Const adCmdText = 1
Public Const adOpenDynamic = 2
Public Const adUseServer = 2
Public Const adOpenStatic = 3
Public Const adUseClient = 3
Public Const adLockBatchOptimistic = 4
Public Const adTypeBinary = 1
Public Const adTypeText = 2
' Dim oStream As Object
' Dim oCmd As Object
Dim oCon As Object
Dim oRS As Object
Dim strSQL As String
Dim strFilePath As String
' Set oStream = CreateObject("ADODB.Stream")
' Set oCmd = CreateObject("ADODB.Command")
' Set xlXML = CreateObject("MSXML2.DOMDocument")
' Set XL = GetObject(, "Excel.Application")
' Set WB = XL.ActiveWorkbook
' xlXML.LoadXML Replace(WB.Sheets("Sheet1").Cells(1, 1).CurrentRegion.value(xlRangeValueMSPersistXML), "rs:name="" ", "rs:name=""") 'xlRangeValueXMLSpreadsheet
' oRS.Open xlXML, CursorType:=adOpenStatic, LockType:=adLockBatchOptimistic
Set oCon = CreateObject("ADODB.Connection")
Set oRS = CreateObject("ADODB.Recordset")
strFilePath = ThisWorkbook.Path & Application.PathSeparator & "temp1.xlsx"
With oCon
.Provider = "Microsoft.Ace.OLEDB.12.0"
.Properties("Extended Properties") = "Excel 12.0; HDR=Yes;IMEX=1;"
.CursorLocation = adUseClient
.Open strFilePath
End With
strSQL = "SELECT * FROM [Sheet1$]"
With oRS
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
.activeconnection = adoCon
.Open (strSQL)
End With
End Sub
Is there an alternative method to ensure that the full column name characters gets imported into the ADO Recordset from a CLOSED Excel Workbook i.e. overcoming the 64 character limitation of Fields?
Can an ADO.STREAM object do the job maintaining the column names OR Can an MSXML2.DOMDocument.Load File or .LoadXML do the trick?
Any help would be most appreciated.