Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference named range in closed workbook
Hello,
Is there a way to retrieve all the named ranges names in a CLOSED workbook then, picking one of them, copy its content to the clipboard? Remark: without opening the wbk... Thanks Avi |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference named range in closed workbook
Hello,
Is there a way to retrieve all the named ranges names in a CLOSED workbook then, picking one of them, copy its content to the clipboard? Remark: without opening the wbk... Thanks Avi You can do this via ADODB! Here's something that'll get you started... http://www.appspro.com/conference/Da...rogramming.zip -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference named range in closed workbook
hi,
Excel 2007 +, OpenXML format xlsx and xlsm Sub Sheets_list_and_named_ranges_on_CLOSED_workbook() 'Need to activate the Microsoft ADO Ext xx reference for DLL and Security 'Need to activate the reference Microsoft ActiveX Data Objects xx Library Dim Cn As ADODB.Connection Dim oCat As ADOX.Catalog Dim oFile As String, Resultat As String Dim oSheet As ADOX.Table oFile = "C:\MyFile.xlsm" Set Cn = New ADODB.Connection Set oCat = New ADOX.Catalog '--- Connexion --- With Cn .Provider = "Microsoft.Jet.OLEDB.4.0" .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _ & oFile & ";Extended Properties=""Excel 12.0;HDR=YES;""" .Open End With Set oCat.ActiveConnection = Cn For Each oSheet In oCat.Tables Resultat = Resultat & oSheet.Name & vbCrLf Next MsgBox Resultat Set oSheet = Nothing Set oCat = Nothing Cn.Close Set Cn = Nothing End Sub isabelle Le 2014-04-22 07:35, a écrit : Hello, Is there a way to retrieve all the named ranges names in a CLOSED workbook then, picking one of them, copy its content to the clipboard? Remark: without opening the wbk... Thanks Avi |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference named range in closed workbook
Thanks It runs well but lists worksheets and named ranges How can I identify the named ranges only and more important, how can I copy the named range content to the clipboard? Avi |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference named range in closed workbook
Thanks
It runs well but lists worksheets and named ranges How can I identify the named ranges only and more important, how can I copy the named range content to the clipboard? Avi If you explore the samples in the download link I gave you, you'll see how to load the contents of a named range into a recordset. Once there you can do whatever you want with the data. Assuming you want to put it into the Clipboard for pasting somewhere, you can bypass that step and transfer the data directly to wherever... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference named range in closed workbook
Thanks I'll explore it
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference named range in closed workbook
hi Avi,
the sheets names is indicated by the symbol $, which is not the case of the named cells, so, you can replace: For Each oSheet In oCat.Tables Resultat = Resultat & oSheet.Name & vbCrLf Next by For Each oSheet In oCat.Tables If Not Right(oSheet.Name, 1) = "$" Then Resultat = Resultat & oSheet.Name & vbCrLf End If Next isabelle Le 2014-04-22 13:13, a écrit : Thanks It runs well but lists worksheets and named ranges How can I identify the named ranges only and more important, how can I copy the named range content to the clipboard? Avi |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference named range in closed workbook
Thanks all for introducing me to this ADO thing. Looks extremely promising and not so complicated as it seemed to me before
Avi |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference named range in closed workbook
Strange behavior with the provided sample sales.xls (or any other wbk)
szSQL = "SELECT * FROM [Sales$A1:E19];" Set rsData = New ADODB.Recordset rsData.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly, adCmdText Sheet1.Range("A1").CopyFromRecordset rsData If a column contains both numerical and string values, only the numerical values are copied !!! Any idea? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference named range in closed workbook
Looks as I found the solution for my own question I have to use HDR=YES;IMEX=1"; in the connection string Avi |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference named range in closed workbook
Looks as I found the solution for my own question
I have to use HDR=YES;IMEX=1"; in the connection string Avi Ok! Glad you were able to figure it out. It does take a bit of practice to catch on to the nuances related to working with text files and workbooks, being they both have/use different criteria args in their respective SQL statements. For this reason I use a wrapper function to return the data, which accepts 'sSource$' args "mdb", "wkb" or "txt" which uses a 'Select Case' construct to build the appropriate SQL statement. Also required as arg is 'sSelect$' which contains the table info for building the "SELECT FROM" part. I use it like this... <snip Set RS = GetRS(sTable, "mdb") 'database file Set RS = GetRS(sTable, "wkb") 'Excel file Set RS = GetRS(sTable, "txt") 'text file </snip The function also determines whether to use "Jet" or "Ace" depending on Excel version (though Jet is still working in all). This allows me to reuse the function for any number of recordsets I want to access during runtime without having to 'hard code' every time. Once you're familiar with how ADODB works you may want to build your own reusable function! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference named range in closed workbook
Thanks again
Now I am facing a new annoying issue: numbers are copied as "number stored as text" format Is there a straightforward solution? BTW, where can I find the wrapper function and how is it used in VBA? Avi |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference named range in closed workbook
Thanks again
Now I am facing a new annoying issue: numbers are copied as "number stored as text" format Is there a straightforward solution? You'll need to format the fields (columns) containing numbers/dates as desired per your data layout on the target sheet. BTW, where can I find the wrapper function and how is it used in VBA? This is a 'make-your-own' thingy that you configure for how you (want to) work with data. I don't have an encapsulated wrapper because I rarely use ADODB, and when I do I use SQLite. The same principles apply, though, and so you should be able to do same for your VBA projects by putting everything ADODB related in a standard module that you can import into any project as needed. My earlier example of how to use such a function was a rather simplified sample of a more complex process which is generic so I can use it with VB6 projects as well as VBA projects. This is stored in a text file from which I grab snippets on an as needed basis. (I primarily use '.dat' files for storing data so I can work with the standard VB[A] built-in I/O functions and arrays) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference named range in closed workbook
One more issue,please In the sample you provide, I use ' Query based on a sheet-level range name. 'szSQL = "SELECT * FROM [EZZ$ddd];" where EZZ is the sheet name and ddd the sheet-level range name I get an error stating that the engine can't find the object EZZ$ddd No chance also with a workbook level name Thanks again for your very valuable help |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference named range in closed workbook
One more issue,please
In the sample you provide, I use ' Query based on a sheet-level range name. 'szSQL = "SELECT * FROM [EZZ$ddd];" where EZZ is the sheet name and ddd the sheet-level range name I'll have to review the example files but I think you may need to include the punctuation Excel requires for the ref... 'Sheet1'!MyRange I get an error stating that the engine can't find the object EZZ$ddd No chance also with a workbook level name I don't recommend using workbook level names unless absolutely unavoidable! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference named range in closed workbook
Ok.., I see that the exclamation character is not required but the
apostrophes must be there if the sheetname has spaces... ['my sheet'$MyRange] ...otherwise... [MySheet$MyRange] ...should work. Perhaps there's something else causing it to fail and so can you post your entire code for the connection string + SQL statement? -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference named range in closed workbook
Hello again,
I'm completely new at using SQL from VB. I'm trying to merge 2 tables (I connect successfully to them) based on a common key. This is the code I am using but I naturally get errors. Could you help me getting to the right direction? Sub RefeWbk() Dim rsData As ADODB.Recordset 'Create the connection string. Dim oConn As New ADODB.Connection oConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=C:\Dropbox\word\VlookupPlus\VlookupTestFile .xls;" & _ "Extended Properties=""Excel 12.0;HDR=NO;IMEX=1"";" WhereTo = "A1" ' First cell in the range to paste to 'Master table Set rsData = New ADODB.Recordset rsData.Open "Select * from [DatForSAS (5)$A1:T3100]", oConn, adOpenStatic 'Lookup table Set LKdata = New ADODB.Recordset LKdata.Open "Select * from[ListFromPop (2)$A1:D410]", oConn, adOpenStatic 'Merged table Dim rs As New ADODB.Recordset Set rs = New ADODB.Recordset rs.Open "SELECT rsData.a001, LKdata.MgrLevel", oConn, adOpenStatic 'Error!!!! 'Merging query Dim cm As New ADODB.Command Set cm = New ADODB.Command With cm .ActiveConnection = oConn .CommandTimeout = 300 .CommandType = adCmdText .CommandText = "From rsData" & "INNER JOIN LKdata" & "ON rsData.EMPnum=LKdata.EMPnum" .Execute 'Error!!!! End With ActiveSheet.Range(WhereTo).CopyFromRecordset rs End Sub Thanks for your help Avi |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference named range in closed workbook
I'm surprised your code gets that far since the previous statements'
sheet names have spaces and I don't see the apostrophes! I don't use ADODB very much, and when I do it's usually with SQLite. I only refered you to that because it's the least complex route to go. I mostly use built-in VBA file I/O functions and arrays for working with data in my projects. For example, I'd 'dump' the data from sheets in your "VlookupTestFile.xls" into separate arrays and go from there. I felt going this way with you might be too complex since it also requires a fairly deep understanding of VB arrays and how to 'efficiently' work them. That said, once you get your code syntax corrected perhaps someone (like Isabelle or others) more familiar with the nuances of ADODB in VBA will chime in. Otherwise, you have to send me your files so I can step through the code to 'debug' it for you. (Hope there's no hurry, though!) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference named range in closed workbook
On Tuesday, April 22, 2014 10:18:17 PM UTC+3, isabelle wrote:
hi Avi, the sheets names is indicated by the symbol $, which is not the case of the named cells, so, you can replace: For Each oSheet In oCat.Tables Resultat = Resultat & oSheet.Name & vbCrLf Next by For Each oSheet In oCat.Tables If Not Right(oSheet.Name, 1) = "$" Then Resultat = Resultat & oSheet.Name & vbCrLf End If Next isabelle Le 2014-04-22 13:13, a écrit : Thanks It runs well but lists worksheets and named ranges How can I identify the named ranges only and more important, how can I copy the named range content to the clipboard? Avi Hello again, I was able to connect and retrieve the names, but the problem seems that the name has no use in closed workbook as I can't refer to it in any function or in other words, I can't know its range address... Any idea? Thanks a lot Avi |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference named range in closed workbook
hi Avi,
i put the range names in named_range () var Dim named_range() For Each sSheetName In oCat.Tables If Not Right(sSheetName.Name, 1) = "$" Then ReDim Preserve named_range(n) named_range(n) = sSheetName.Name n = n + 1 End If Next and i used that result in cn.Execute For i = LBound(named_range) To UBound(named_range) rw = Cells(65000, 1).End(xlUp).Row + 1 Set rs = cn.Execute("SELECT * FROM " & named_range(i)) Cells(rw + 1, 1) = named_range(i) ' modify destination cell Cells(rw + 2, 1).CopyFromRecordset rs ' modify destination cell Next the full code: '_________________________________________________ _____________________ Sub copy_cells_of_named_range_from_closed_workbook() 'Need to activate the reference Microsoft ADO ext x.x for DLL and Security 'Need to activate the reference Microsoft ActiveX Data Objects x.x Library Dim cn As ADODB.Connection Dim oCat As ADOX.Catalog Dim oFile As String, Resultat As String Dim oSheet As ADOX.Table Dim named_range() oFile = "C:\MyFile.xlsm" Set cn = New ADODB.Connection Set oCat = New ADOX.Catalog With cn .Provider = "Microsoft.Jet.OLEDB.4.0" .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _ & oFile & ";Extended Properties=""Excel 12.0;HDR=YES;""" .Open End With Set oCat.ActiveConnection = cn For Each sSheetName In oCat.Tables If Not Right(sSheetName.Name, 1) = "$" Then ReDim Preserve named_range(n) named_range(n) = sSheetName.Name n = n + 1 End If Next For i = LBound(named_range) To UBound(named_range) rw = Cells(65000, 1).End(xlUp).Row + 1 Set rs = cn.Execute("SELECT * FROM " & named_range(i)) Cells(rw + 1, 1) = named_range(i) ' modify destination cell Cells(rw + 2, 1).CopyFromRecordset rs ' modify destination cell Next Set sSheetName = Nothing Set oCat = Nothing cn.Close Set cn = Nothing End Sub '_________________________________________________ ___________________ isabelle Le 2014-05-15 11:33, a écrit : On Tuesday, April 22, 2014 10:18:17 PM UTC+3, isabelle wrote: Hello again, I was able to connect and retrieve the names, but the problem seems that the name has no use in closed workbook as I can't refer to it in any function or in other words, I can't know its range address... Any idea? Thanks a lot Avi |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference named range in closed workbook
sorry i forgot to declare
Dim i As Integer, n As Integer, rw As Long Dim sSheetName As ADOX.Table isabelle |
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference named range in closed workbook
Isabelle,
If you only knew how much easier this would be using arrays, you might never use ADODB for workbooks or text files again! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#23
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference named range in closed workbook
hi Gary,
also if you do not know in advance the name of the named ranges in the workbook? but despite this problem i am always open to learn more, please tell me isabelle Le 2014-05-17 02:08, GS a écrit : Isabelle, If you only knew how much easier this would be using arrays, you might never use ADODB for workbooks or text files again! |
#24
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference named range in closed workbook
hi Gary,
also if you do not know in advance the name of the named ranges in the workbook? This is also true for table names since named ranges are considered tables when using ADODB! but despite this problem i am always open to learn more, please tell me The point of using ADODB with workbooks or text files is so the file[s] do not open in the *user's instance* of Excel. The 'short' story for using arrays is... [Retrieving data from a workbook] You can dump data into arrays by using an automated instance of Excel via CreateObject("Excel.Application"), then open the file to access the data as desired. (This essentially duplicates what happens with ADODB's 'Open' statement) Treat each array as a separate recordset, or build new arrays (recordsets) by filtering or selecting fieldnames via Application.Index(array, RowIndex, ColIndex). Filtering the table (sheet or named range) allows dumping visible rows into an array. To select individual records: rs2 = Application.Index(rs1, RowIndex, 0) To select individual fields: rs2 = Application.Index(rs1, 0, ColIndex) Note: As you know, dumping data from a worksheet always results a 1-based 2D array. [Retrieving data from a text file] Use standard VB file I/O functions to read/write data in text files. Of course, it's important that the text file be properly configured as a data table for this to work well. That means the first line must contain only fieldnames, and the data must not be comma delimited because record data often contains commas if 'Note' or 'Text' type. (I use the pipe character) Read the file into an array rs using rs0 = Split(TextIn, vbCrLf). This results a 1D array where its UBound is the record count. You'll need to loop to create a 2D array of the data as follows. Dim vTmp, rs1(), n&, j& Redim rs1(Ubound(rs0), UBound(Split(rs0(0), "|"))) For n = LBound(rs0) To UBound(rs0) vTmp = Split(rs0(n), "|") For j = LBound(vTmp) To UBound(vTmp) rs1(n, j) = vTmp(j) Next 'j Next 'n Note that this 2D array is 0-based. Reloading it from the worksheet makes it 1-based! Your code should always use the 1-based 2D array so the sheet/array data indexes are 'in sync'! Dump the 2D array into a temp hidden worksheet for sorting/filtering. You can now work with the data same way as described for workbooks. Only difference is text file data won't have named ranges. Note: It's a good idea to name the temp sheet and 'Set' a fully qualified ref to it for use in your code. Offline assistance is available if you want to post an email address. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA code to copy named range in closed workbook | Excel Programming | |||
Workbook named range not seen by ADO when workbook closed | Excel Programming | |||
ADO - closed workbook - Named range | Excel Programming | |||
referencing a named range from a closed workbook | Excel Programming |