Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO to get data form closed Excel
I am using ado to get data from a closed excel file. The problem is the data
I am looking for is in different columns in different excel files. I only know the colum heading (this could be in colum 10 in some files and in column 15 in other files.. I would not know the colum number before hand but I can find out the column number by using ADO before trying to read the data) I am using the follwoing code to get the data: Sub GetDataFromHBWorkbook(SourceFile As String, SourceRange As String, _ TargetRange As Range, IncludeFieldNames As Boolean) 'GetDataFromClosedWorkbook "C:\FolderName\WorkbookName.xls", "A1:B21", ActiveCell, False 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 Exit Sub InvalidInput: MsgBox "The source file or source range is invalid!", _ vbExclamation, "Get data from closed workbook" End Sub ========================== The problem is I have to give the source range in "A1:A200" format. Is there a way to pass the source range in Range(Cells(1,1),Cells(1,200)) format? OR How can I covert the Range(Cells(1,1),Cells(1,200)) fromat to Range("A1:A200) format without writing extensive code? OR Is there any other way to get data form closed workbook? I would not know the sorce range before hand. I have to search for the column with the heading name. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO to get data form closed Excel
Hi,
Convert to xlA1 address (string) using the Address property of the range object: Dim Rg As Range '... set the range MsgeBox Rg.Address(False,False,xlA1) in your case: Range(Cells(1,1),Cells(1,200)) .Address(False,False,xlA1) Regards, Sebastien "Mohan" wrote: I am using ado to get data from a closed excel file. The problem is the data I am looking for is in different columns in different excel files. I only know the colum heading (this could be in colum 10 in some files and in column 15 in other files.. I would not know the colum number before hand but I can find out the column number by using ADO before trying to read the data) I am using the follwoing code to get the data: Sub GetDataFromHBWorkbook(SourceFile As String, SourceRange As String, _ TargetRange As Range, IncludeFieldNames As Boolean) 'GetDataFromClosedWorkbook "C:\FolderName\WorkbookName.xls", "A1:B21", ActiveCell, False 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 Exit Sub InvalidInput: MsgBox "The source file or source range is invalid!", _ vbExclamation, "Get data from closed workbook" End Sub ========================== The problem is I have to give the source range in "A1:A200" format. Is there a way to pass the source range in Range(Cells(1,1),Cells(1,200)) format? OR How can I covert the Range(Cells(1,1),Cells(1,200)) fromat to Range("A1:A200) format without writing extensive code? OR Is there any other way to get data form closed workbook? I would not know the sorce range before hand. I have to search for the column with the heading name. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel form. Need data extracted to spreadsheet each time a form co | Excel Discussion (Misc queries) | |||
Create a form in excel so I can enter data using DataForm | Excel Discussion (Misc queries) | |||
How do I import data from a closed Excel Spreadsheet? | Excel Discussion (Misc queries) | |||
Trying to import data from many closed excel workbooks into one: Please Help :-) | Excel Programming | |||
Need FASTEST way to get data from a large closed Excel File | Excel Programming |