Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read a cell value without opening the file.
Is there a way to read a cell value in an excel file without actually opening
the file? Thank you, Steven |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read a cell value without opening the file.
HI !
Try an ADO macro ; first , copy this macro in a wbook ! __________________________________________________ _____________ Option Explicit Public Sub GetData(SourceFile As Variant, SourceSheet As String, _ SourceRange As String, TargetRange As Range, Header As Boolean, UseHeaderRow As Boolean) ' 30-Dec-2007, working in Excel 2000-2007 Dim rsCon As Object Dim rsData As Object Dim szConnect As String Dim szSQL As String Dim lCount As Long ' Create the connection string. If Header = False Then If Val(Application.Version) < 12 Then szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & SourceFile & ";" & _ "Extended Properties=""Excel 8.0;HDR=No"";" Else szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & SourceFile & ";" & _ "Extended Properties=""Excel 12.0;HDR=No"";" End If Else If Val(Application.Version) < 12 Then szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & SourceFile & ";" & _ "Extended Properties=""Excel 8.0;HDR=Yes"";" Else szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & SourceFile & ";" & _ "Extended Properties=""Excel 12.0;HDR=Yes"";" End If End If If SourceSheet = "" Then ' workbook level name szSQL = "SELECT * FROM " & SourceRange$ & ";" Else ' worksheet level name or range szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];" End If On Error GoTo SomethingWrong Set rsCon = CreateObject("ADODB.Connection") Set rsData = CreateObject("ADODB.Recordset") rsCon.Open szConnect rsData.Open szSQL, rsCon, 0, 1, 1 ' Check to make sure we received data and copy the data If Not rsData.EOF Then If Header = False Then TargetRange.Cells(1, 1).CopyFromRecordset rsData Else 'Add the header cell in each column if the last argument is True If UseHeaderRow Then For lCount = 0 To rsData.Fields.Count - 1 TargetRange.Cells(1, 1 + lCount).Value = _ rsData.Fields(lCount).Name Next lCount TargetRange.Cells(2, 1).CopyFromRecordset rsData Else TargetRange.Cells(1, 1).CopyFromRecordset rsData End If End If Else MsgBox "No records returned from : " & SourceFile, vbCritical End If ' Clean up our Recordset object. rsData.Close Set rsData = Nothing rsCon.Close Set rsCon = Nothing Exit Sub SomethingWrong: MsgBox "The file name, Sheet name or Range is invalid of : " & SourceFile, _ vbExclamation, "Error" On Error GoTo 0 End Sub __________________________________________________ _________________________ Then , copy and this macro , too : Sub GetData_Example3() ' In this example Header = False and UseHeaderRow can be True or False because it is not used GetData ThisWorkbook.Path & "\test.xls", "Sheet1", _ "A3:C4", Sheets("Sheet1").Range("A1"), False, False End Sub change test.xls , sheets name and range according with your needs ; see http://.www.rondebruin.nl/tips.htm |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read a cell value without opening the file.
Try this , too :
=SUM('C:\Rapoarte\[Buget.xls]Anual'!C10:C25) Make ajustaments for your needs to path ; Rapoarte is the name of folder in C:\ , Buget.xls is name of wbook , Anual is name os sheet ...etc ; |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read a cell value without opening the file.
WOW ; WOW ; WOW
Thank you very much. This basically means I can keep people from overwriting an excel file, or at least catch them if they do. What I am going to do is put a version number in the file and when the file opens it will check this other file w/o opening it for the correct verision number and I will change version numbers as needed on my most critical files. I have always worried about people overwriting files. What if the file I want to read out of has a password? Would it go on the GetData line of code? Thanks again. "ytayta555" wrote: HI ! Try an ADO macro ; first , copy this macro in a wbook ! __________________________________________________ _____________ Option Explicit Public Sub GetData(SourceFile As Variant, SourceSheet As String, _ SourceRange As String, TargetRange As Range, Header As Boolean, UseHeaderRow As Boolean) ' 30-Dec-2007, working in Excel 2000-2007 Dim rsCon As Object Dim rsData As Object Dim szConnect As String Dim szSQL As String Dim lCount As Long ' Create the connection string. If Header = False Then If Val(Application.Version) < 12 Then szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & SourceFile & ";" & _ "Extended Properties=""Excel 8.0;HDR=No"";" Else szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & SourceFile & ";" & _ "Extended Properties=""Excel 12.0;HDR=No"";" End If Else If Val(Application.Version) < 12 Then szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & SourceFile & ";" & _ "Extended Properties=""Excel 8.0;HDR=Yes"";" Else szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & SourceFile & ";" & _ "Extended Properties=""Excel 12.0;HDR=Yes"";" End If End If If SourceSheet = "" Then ' workbook level name szSQL = "SELECT * FROM " & SourceRange$ & ";" Else ' worksheet level name or range szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];" End If On Error GoTo SomethingWrong Set rsCon = CreateObject("ADODB.Connection") Set rsData = CreateObject("ADODB.Recordset") rsCon.Open szConnect rsData.Open szSQL, rsCon, 0, 1, 1 ' Check to make sure we received data and copy the data If Not rsData.EOF Then If Header = False Then TargetRange.Cells(1, 1).CopyFromRecordset rsData Else 'Add the header cell in each column if the last argument is True If UseHeaderRow Then For lCount = 0 To rsData.Fields.Count - 1 TargetRange.Cells(1, 1 + lCount).Value = _ rsData.Fields(lCount).Name Next lCount TargetRange.Cells(2, 1).CopyFromRecordset rsData Else TargetRange.Cells(1, 1).CopyFromRecordset rsData End If End If Else MsgBox "No records returned from : " & SourceFile, vbCritical End If ' Clean up our Recordset object. rsData.Close Set rsData = Nothing rsCon.Close Set rsCon = Nothing Exit Sub SomethingWrong: MsgBox "The file name, Sheet name or Range is invalid of : " & SourceFile, _ vbExclamation, "Error" On Error GoTo 0 End Sub __________________________________________________ _________________________ Then , copy and this macro , too : Sub GetData_Example3() ' In this example Header = False and UseHeaderRow can be True or False because it is not used GetData ThisWorkbook.Path & "\test.xls", "Sheet1", _ "A3:C4", Sheets("Sheet1").Range("A1"), False, False End Sub change test.xls , sheets name and range according with your needs ; see http://.www.rondebruin.nl/tips.htm |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read a cell value without opening the file.
What if the file I want to read out of has *a password? *Would it go on the
GetData line of code? I tryed and if the wbook have a password , GetData cann't do this ; actually , I don't know how and if it's posible to get data from an locked wbook . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read a cell value without opening the file.
Use my Merge add-in if all files have the same password
http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Steven" wrote in message ... WOW ; WOW ; WOW Thank you very much. This basically means I can keep people from overwriting an excel file, or at least catch them if they do. What I am going to do is put a version number in the file and when the file opens it will check this other file w/o opening it for the correct verision number and I will change version numbers as needed on my most critical files. I have always worried about people overwriting files. What if the file I want to read out of has a password? Would it go on the GetData line of code? Thanks again. "ytayta555" wrote: HI ! Try an ADO macro ; first , copy this macro in a wbook ! __________________________________________________ _____________ Option Explicit Public Sub GetData(SourceFile As Variant, SourceSheet As String, _ SourceRange As String, TargetRange As Range, Header As Boolean, UseHeaderRow As Boolean) ' 30-Dec-2007, working in Excel 2000-2007 Dim rsCon As Object Dim rsData As Object Dim szConnect As String Dim szSQL As String Dim lCount As Long ' Create the connection string. If Header = False Then If Val(Application.Version) < 12 Then szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & SourceFile & ";" & _ "Extended Properties=""Excel 8.0;HDR=No"";" Else szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & SourceFile & ";" & _ "Extended Properties=""Excel 12.0;HDR=No"";" End If Else If Val(Application.Version) < 12 Then szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & SourceFile & ";" & _ "Extended Properties=""Excel 8.0;HDR=Yes"";" Else szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & SourceFile & ";" & _ "Extended Properties=""Excel 12.0;HDR=Yes"";" End If End If If SourceSheet = "" Then ' workbook level name szSQL = "SELECT * FROM " & SourceRange$ & ";" Else ' worksheet level name or range szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];" End If On Error GoTo SomethingWrong Set rsCon = CreateObject("ADODB.Connection") Set rsData = CreateObject("ADODB.Recordset") rsCon.Open szConnect rsData.Open szSQL, rsCon, 0, 1, 1 ' Check to make sure we received data and copy the data If Not rsData.EOF Then If Header = False Then TargetRange.Cells(1, 1).CopyFromRecordset rsData Else 'Add the header cell in each column if the last argument is True If UseHeaderRow Then For lCount = 0 To rsData.Fields.Count - 1 TargetRange.Cells(1, 1 + lCount).Value = _ rsData.Fields(lCount).Name Next lCount TargetRange.Cells(2, 1).CopyFromRecordset rsData Else TargetRange.Cells(1, 1).CopyFromRecordset rsData End If End If Else MsgBox "No records returned from : " & SourceFile, vbCritical End If ' Clean up our Recordset object. rsData.Close Set rsData = Nothing rsCon.Close Set rsCon = Nothing Exit Sub SomethingWrong: MsgBox "The file name, Sheet name or Range is invalid of : " & SourceFile, _ vbExclamation, "Error" On Error GoTo 0 End Sub __________________________________________________ _________________________ Then , copy and this macro , too : Sub GetData_Example3() ' In this example Header = False and UseHeaderRow can be True or False because it is not used GetData ThisWorkbook.Path & "\test.xls", "Sheet1", _ "A3:C4", Sheets("Sheet1").Range("A1"), False, False End Sub change test.xls , sheets name and range according with your needs ; see http://.www.rondebruin.nl/tips.htm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
file keeps opening as read only | Excel Discussion (Misc queries) | |||
How do I stop a Excel 2007 file opening as read only? | Excel Discussion (Misc queries) | |||
Opening macro file as read only | Excel Programming | |||
"Unable to read file" error message when opening a Excel file that contains a PivotTable report. | Charts and Charting in Excel | |||
File always opening as read only | Excel Discussion (Misc queries) |