accdb.net 独特诠释 access 2007 的新格式 *.accdb

VB6中使用32位图标(第二版)

2012-02-06T00:11:50+08:00

VB6中使用32位图标(第二版) .
分类： Visual Basic 6.0

第一版参见：http://blog.csdn.net/Modest/archive/2008/05/06/2399774.aspx

本版添加了hIcon属性和SetFormIcon方法，顾名思义SetFormIcon就是实现vb6窗体的32位Icon应用。实现起来非常简单，发一个消息足矣。hIcon属性会根据图标索引返回图标句柄，用这个句柄还可以实现32位Icon在托盘中的美化应用。
(声明：魏滔序原创，转贴请注明出处。)

全部代码如下：

'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
'  VB6中使用32位图标(第二版)
'  Programmed by 魏滔序
'  WebSite: http://www.chenoe.com
'  Blog: http://blog.csdn.net/Modest
':::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

Option Explicit

Private Type ICONDIRENTRY
    bWidth  As Byte
    bHeight  As Byte
    bColorCount  As Byte
    bReserved  As Byte
    wPlanes  As Integer
    wBitCount  As Integer
    dwBytesInRes  As Long
    dwImageOffset  As Long
End Type

Private Type ICONDIR
    idReserved As Integer
    idType As Integer
    idCount As Integer
    idEntries() As ICONDIRENTRY
End Type

Private Declare Function CreateIconFromResourceEx Lib "user32" (presbits As Byte, ByVal dwResSize As Long, ByVal fIcon As Long, ByVal dwVer As Long, ByVal cxDesired As Long, ByVal cyDesired As Long, ByVal uFlags As Long) As Long
Private Declare Function DrawIconEx Lib "user32.dll" (ByVal hdc As Long, ByVal xLeft As Long, ByVal yTop As Long, ByVal hIcon As Long, ByVal cxWidth As Long, ByVal cyWidth As Long, ByVal istepIfAniCur As Long, ByVal hbrFlickerFreeDraw As Long, ByVal diFlags As Long) As Long
Private Declare Function DestroyIcon Lib "user32" (ByVal hIcon As Long) As Long
Private Declare Sub CopyMemory Lib "kernel32.dll" Alias "RtlMoveMemory" (ByRef Destination As Any, ByRef Source As Any, ByVal Length As Long)
Private Declare Function SendMessageLong Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long

Private m_Data() As Byte
Private m_iCount As Integer
Private m_iDir As ICONDIR

Public Property Get Count() As Long
    Count = m_iCount
End Property

Public Property Get Height(Optional ByVal Index As Long) As Long
    Height = m_iDir.idEntries(Index).bHeight
End Property

Public Property Get Width(Optional ByVal Index As Long) As Long
    Width = m_iDir.idEntries(Index).bWidth
End Property

Public Property Get Length(Optional ByVal Index As Long) As Long
    Length = m_iDir.idEntries(Index).dwBytesInRes
End Property

Public Property Get Data(Optional ByVal Index As Long) As Byte()
    Dim o As Long, l As Long, d() As Byte
    o = m_iDir.idEntries(Index).dwImageOffset
    l = m_iDir.idEntries(Index).dwBytesInRes
    ReDim d(l - 1)
    CopyMemory d(0), m_Data(o), l
    Data = d
End Property

Public Function LoadFromData(Data() As Byte) As Boolean
    Dim i As Long
    m_Data = Data
    CopyMemory m_iCount, m_Data(4), 2                      '取得图标个数
    If m_iCount > 0 Then
        ReDim m_iDir.idEntries(0 To m_iCount - 1)          '图标目录结构数据
        For i = 0 To m_iCount - 1
            CopyMemory m_iDir.idEntries(i), m_Data(6 + Len(m_iDir.idEntries(i)) * i), Len(m_iDir.idEntries(i))
        Next
        LoadFromData = True
    End If
End Function

Public Function LoadFromFile(ByVal FileName As String) As Boolean
    Dim hFile As Integer
    Dim Data() As Byte

    If Dir(FileName) = "" Then Exit Function

    hFile = FreeFile
    Open FileName For Binary As #hFile
    ReDim Data(LOF(hFile) - 1)
    Get #hFile, , Data
    Close #hFile

    LoadFromFile = LoadFromData(Data)
End Function

Public Property Get hIcon(Optional ByVal Index As Long) As Long
    Dim d() As Byte, l As Long, w As Long, h As Long
    d = Data(Index): l = Length(Index)
    w = Width(Index): h = Height(Index)
    hIcon = CreateIconFromResourceEx(d(0), l, 1, &H30000, w, h, 0)
End Property

Public Function Draw(ByVal hdc As Long, ByVal x As Long, ByVal y As Long, Optional ByVal Index As Long = 0) As Boolean
    Dim w As Long, h As Long
    w = Width(Index): h = Height(Index)
    Draw = DrawIconEx(hdc, x, y, hIcon(Index), w, h, 0, 0, 3) <> 0
    DestroyIcon hIcon
End Function

Public Sub SetFormIcon(ByVal Form As Form, Optional ByVal Index As Long = 0)
    SendMessageLong Form.hWnd, &H80, 0, hIcon(Index)
End Sub

Private Sub Class_Terminate()
    Erase m_Data
End Sub

SQL server 中取出分组后每组的前几条

2012-01-29T13:38:38+08:00

分组取前几

SQL server 中取出分组后每组的前几条

作者：Rex.He  来源：博客园  发布时间：2011-09-14 16:00  阅读：25 次  原文链接   [收藏]
表:T
字段:id   自动递增,
          field1   数字,
          .....
select   *   from   (
select   *,(select   count(*)   from   T   as   t2   where   t2.field1=t1.field1   and   t2.id <=t1.id)   as   aaa   from   T   as   t1)   as   t3
where   aaa <=2t2.field1=t1.field1是分组的条件，可自由添加

参考地址：

http://topic.csdn.net/t/20040819/17/3291042.html

取出数据中每个分类前5条

select ID,title,D_Picture,addtime,newstypeid,jj,content
FROM news ns
where ID in (select top 5 ID from news where newstypeid = ns.newstypeid and isActive='1' order by addtime desc)

XML Data Manipulation with ADO 2.5

2012-01-23T00:17:57+08:00

XML Data Manipulation with ADO 2.5

Ken Spencer

Code for this article: Serving0800.exe (33KB)

recently discovered some features of ActiveXÂ® Data Objects (ADO) 2.5 that make it easier to use XML and your data source in your Web applications. I was working on Internet and intranet apps that run on WindowsÂ® 2000, so I could take advantage of ADO 2.5 to make data manipulation easier. Let's take a look at the benefits of ADO 2.5 that I found in my research and how I dealt with some of the problems you may encounter.

XML Streams

ADO 2.5 includes some helpful support for XML. In this version of ADO you can save a recordset as an XML stream. This allows you to simply save the recordset, and voilï¿½â€"you have an XML stream to work with. While it's easy to create the XML stream, the downside is that you are stuck with the XML schema that ADO generates. You can't tweak it to your liking or to any other format; you can either use the standard ADO format or roll your own (more on that in a minute).
The way you save a recordset to a stream is simple. Just use the Save method of the recordset and specify the adPersistXML option, like so:


            rstCustomers.save "MyXMLFile.xml", adPersistXML

This method saves the rstCustomers recordset as an XML file. Once you have saved it, you can open the file in MicrosoftÂ® Internet Explorer, ADO, or any other application that can load an XML file and parse it. So far, so good.
When you save the recordset like this, the format is quite complex and follows the ADO XML schema I mentioned earlier. Figure 1 shows the results of saving the Titles table from an ADO recordset to an XML file. The data shown in Figure 1 is the Document Type Definition (DTD) for the XML generated by ADO. The data starts with the rs:data element shown in Figure 2.
My major complaint about the way the XML is generated has to do with its structure. Since it is generic, the schema uses generic namespaces (such as rs) to contain the actual data. Then each record is output with its data going into attributes instead of tag elements or being split into tag elements and attributes.

The Sample Apps

Now let's look at another way of using XML with ADO. The MSDNÂ® library reference for the ADO Recordset Save method shows the following example:


            Dim xDOM As New MSXML.DOMDocument
            Dim rsXML As New ADODB.Recordset
            Dim sSQL As String, sConn As String
            sSQL = "Select customerid, companyname, contactname
            FROM customers"
            sConn= & _  "Provider=Microsoft.Jet.OLEDB.4.0;
            Data Source=D:\Program Files" & _
            "\Common Files\System\msadc\samples\NWind.mdb"
            rsXML.Open sSQL, sConn
            rsXML.Save xDOM, adPersistXML

The reference says that this example will save the recordset in an XML tree in the object referenced by xDOM. This is all well and good, but the sample does not work. (I tried this on Windows 2000 Professional.) But all is not lost.
I created the following code sample, which uses the new Stream object from ADO 2.5:


            Function RetrieveAllTitlesAsXML() As Variant
            Dim stXMLStream As Stream
            Set rsAllTitles = New ADODB.Recordset
            Set stXMLStream = New Stream
            sSQL = "select * from titles"
            rsAllTitles.Open sSQL, GetDSN, _
            adOpenForwardOnly, adLockReadOnly, adCmdText
            rsAllTitles.save stXMLStream, adPersistXML
            rsAllTitles.Close
            Set rsAllTitles = Nothing
            Set RetrieveAllTitlesAsXML = stXMLStream
            End Function

The first line in this procedure dimensions the stXMLStream variable as a Stream object. It then creates a new instance of the ADO Recordset object, followed by an instance of the Stream object. Next, it sets the SQL for the Open method and executes Open to retrieve the records. The next line saves the recordset to the Stream object in XML format by specifying the stXMLStream variable as the first argument to the Save method, then specifying the adPersistXML constant as the last argument. The next two lines close the recordset and set the recordset variable to "Nothing". The last line returns the Stream object as the return variable from the function. At this point the calling application has access to the Stream object.
Now let's look at code that uses the Stream object that is returned from the RetrieveAllTitlesAsXML function:


            Dim stXMLStream As Stream
            Set objPub = New
            Publication.Title
            Set stXMLStream = objPub.RetrieveAllTitlesAsXML()
            txtNotes.Text =
            stXMLStream.ReadText()

The first line creates a variable and defines it as type Stream just like the RetrieveAllTitlesAsXML method. The next line instantiates the Title object, and the following line executes RetrieveAllTitlesAsXML and sets the return value to the stXMLStream variable. The last line extracts the XML from the Stream object by executing the ReadText method of the Stream object.
So far, you have seen how to create the Stream object, populate it with a recordset, and extract the XML from the Stream object for that recordset, as shown in the following code:


            sXML = stXMLStream.ReadText()
            xmlDoc.loadXML (sXML)

This code loads the sXML variable from the Stream object into the sXML variable. Then the LoadXML method of the MSXML.DOMDocument loads the string into the MSXML parser's document object. Now you can work with it just like any other XML. The Stream object serves as a way to move the XML from the Recordset object into the parser document object. I'll come back to the parser in a minute.
The ADO documents point out that you can save the recordset to the Response object in your ASP code. The sample code shown in the MSDN library looks like this:


            Response.Write "" _
            & vbCRLF
            rstCustomers.save Response, adPersistXML
            rstCustomers.Close

This code generates XML from the recordset and sends it down the wire to the browser (or other calling application), where you can use the MSXML parser in Internet Explorer or any other XML engine to work with the data.
Now let's get back to the MSXML parser and other ways of generating and using XML. I have written about a small XML class in past columns. I used this code to create a class called Generator (Generator.cls in XMLGen.vbp) for this column. This class is really useful for formatting XML that you use in other applications. The code in Figure 3 is from the Title class in the Publications.vbp project's RetrieveTitlesSimpleXML method, which extracts data from the Titles table in the Pubs database, and then returns the data in an XML string.
The first thing the code in Figure 3 does is create an instance of the XML utility:


            Set oXML = New Generator

It creates an instance of the ADO Recordset object, then calls the XMLDeclaration method to output the XML declaration at the start of my XML stream:


            vOutPut = oXML.XMLDeclaration()

The BeginTag method is called to create the starting Books tag:


            vOutPut = vOutPut & oXML.BeginTag("Books")

The Do...While loop processes the recordset. The Format method of the XML utility is called for each field in a record. This method takes the tag name and the value (from the recordset). It returns the properly formatted tag for that field and puts it into the vOutput variable.
The only really tricky part of this code is the call to BeginTag in the loop:


            sBeginTag = oXML.BeginTag("Book", "TitleID", rsTitle("title_id"))

The BeginTag method takes two optional parameters, an attribute name and an attribute value. That lets you format a tag and add an attribute at the same time. This results in a tag that looks something like the following:

The EndTag method is called twice, once to end the Book tag each time through the loop and once to end the Books parent tag at the end of the procedure. Then the procedure returns the entire string as its return value.
The XMLParser code is shown in Figure 4. The Parse method is not used in this application; it is simply left over from previous projects.
The Visual Basic-created form in Figure 5 is used to test the XML functionality that I have been discussing. To test the RetrieveTitlesSimpleXML method, click the Retrieve XML into MSXML button. This button calls the RetrieveTitlesSimple-XML method in Title, then uses the MSXML parser to read the XML stream.

Figure 5 Testing the XML

The code from the cmdXMLIntoMSXML event is shown in Figure 6. Most of the declarations at the beginning of the procedure define variables that will be used with the MSXML parser. The first two set statements instantiate the Title class and the DOMDocu-ment class:


            Set objPub = New Publication.Title
            Set xmlDoc = New MSXML.DOMDocument

Then the RetrieveTitlesSimpleXML method is executed and returns the XML stream into the sXML variable:


            sXML = objPub.RetrieveTitlesSimpleXML()

The next line of code loads the sNodeToFind variable with the value Book:


            sNodeToFind = "Book"

If you look back at Figure 3 you'll see that I created Book as the element tag for each title I retrieve.
Next, the code sets the async property of the MSXML DOMDocument object to False. If this is not set to False, the XML will be loaded asynchronously and you must check the readyState property to determine when the XML has been loaded.


            xmlDoc.async = False

Then the LoadXML method is called to actually load the XML from my string (sXML):


            xmlDoc.loadXML (sXML)

Now you can work with the XML document through the methods of the MSXML DOMDocument object. The next line of code finds the Book tags and returns an object representing a node list for all of those tags:


            Set objNodeList = xmlDoc.getElementsByTagName(sNodeToFind)

The For...Next loop loops through all of the tags in the node list:


            For i = 0 To (objNodeList.length - 1)

The NextNode method returns a Node object for the next tag in the list:


            Set objNode = objNodeList.nextNode

The Attributes method extracts the attributes for the current node and places them into a NodeMap object:


            Set objNodeMap = objNode.Attributes

Now you can finally get to the attribute. The following code extracts the attribute named TitleID and sets it in a Node object:


            Set objNamedItem = objNodeMap.getNamedItem("TitleID")

The next lines of code extract the attribute text and the text from the node:


            txtNotes = txtNotes & objNamedItem.Text & "  -  "
            txtNotes = txtNotes & objNode.Text & vbCrLf

The last few lines of code in the procedure clean up the loop and destroy the objects. When you run this procedure, the results look like Figure 7.

Figure 7 The Results

Conclusion

      It can be difficult to decide if you should use XML in your project. First, you must figure out where it really makes sense to use XML. It does not solve every problem, and it is not suitable for every instance of an application that you build. It is also sometimes difficult to figure out how to work with XML. I am not a big fan of creating extra work for myself, so I always create classes like Generator to standardize the work for me. That makes my life easy because it provides a tool to work with and simplifies my interface to the technology.
      You can experiment with my sample application to create other ways of working with XML. The Simple Stream button will pull data from the database and put it into XML, then load the XML into Form2. Form2 calls the MSXML parser to parse the XML and load the data into the form. The sample code is really designed to explore different ways of working with XML using ADO and the MSXML parser. It's handy because you can cut and paste that code into your components or ASP script to add functionality to your own applications.
      There is also lots more to look forward to with XML. The BizTalkâ„¢ framework and other initiatives make it vital to understand XML and how it works so you can communicate with other applications.

Ken Spencer works for the 32X Tech Corporation (http://www.32X.com), which produces a line of high-quality developer courseware. Ken also spends much of his time consulting or teaching private courses.

From the August 2000 issue of MSDN Magazine

http://msdn.microsoft.com/en-us/magazine/bb985051.aspx

高效SQL——合并多个字段值或多条记录

2012-01-14T21:34:37+08:00

高效SQL——合并多个字段值或多条记录

为什么说是高效呢?因为摒弃了游标和函数的途径，而采用变量的方式来保存值。也就是说避免了游标和函数自身的缺点。

　　高效SQL——从无主键表中合并字段值

以下是引用片段：
　　create table #T1
　　(
　　A varchar(10),
　　B varchar(20)
　　)
　　insert into #T1 values ('aa','1')
　　insert into #T1 values ('aa','9a')
　　insert into #T1 values ('bb','1')
　　insert into #T1 values ('bb','10')
　　insert into #T1 values ('bb','16')
　　insert into #T1 values ('aa','16')
　　insert into #T1 values ('aa','17')
　　insert into #T1 values ('aa','30')
　　insert into #T1 values ('bb','6df')
　　insert into #T1 values ('aa','5')
　　insert into #T1 values ('aa','8')
　　insert into #T1 values ('aa','ed')

　　所要的结果：

　　aa 1,9a,16,17,30,5,8,ed

　　bb 1,10,16,6df

　　解决方法：

以下是引用片段：
　　declare @c varchar(1024)
　　set @c=''
　　declare @x char(10),@y char(10)
　　set @x=''
　　set @y=''
　　select @y=@x,@x=x.a,@c=@c + (case @x when @y then ',' else ';' + x.a + ':' end)
　　+x.d from (select b as d,a from #t1) as x order by x.a
　　set @c=substring(@c,2,len(@c)-1)
　　select @c

　　为什么说是高效呢?

　　因为摒弃了游标和函数的途径，而采用变量的方式来保存值

　　也就是说避免了游标和函数自身的缺点

　　如果是简单地把字段值合并的sql语句：

　　select c1||c2||c3 as name from table

　　中间可以添加字符常量和数字，如

　　select c1||'test'||c2||c3 as name from table

　　select c1||2||c2||c3 as name from table

　　如何用一条SQL语句，将多条记录(一个字段)合并为一个?

　　例如：

　　table字段为：tableID(nchar)

　　查询结果为不确定的多条：

　　tableID

　　……

　　如何用一条SQL语句将这些记录合并为一个字段，值为：'T1T2T3……'

以下是引用片段：
　　create table t
　　(tableid nchar(30))
　　insert t
　　select 'T1' union all
　　select 'T2' union all
　　select 'T3' union all
　　select 'T4' union all
　　select 'T5' union all
　　select 'T6'
　　go
　　create function f_he()
　　returns @t table(col varchar(50))
　　as
　　begin
　　declare @sql varchar(50)
　　set @sql=''
　　select @sql=@sql+ltrim(rtrim(tableid)) from t
　　insert @t values (@sql)
　　return
　　end
　　go
　　select * from t
　　select * from dbo.f_he()
　　drop function f_he
　　drop table t
　　col
　　--------------------------------------------------
　　T1T2T3T4T5T6
　　(所影响的行数为 1 行)

NTSVC.OCX控件使用帮助，VB可以用NTSVC.OCX控件将应用程序做成Service服务

2012-01-01T18:12:14+08:00

NTSVC.OCX控件使用帮助，VB可以用NTSVC.OCX控件将应用程序做成Service服务2007年02月12日星期一上午 10:56使用这个控件注册成Service服务的时候有个需要注意的，如果我们不使用/i或者/u参数，那么建立的Service服务会因为超时而不能启动。所以在注册Service服务的时候，必须带/i或/u参数。

1. 引用控件
选择“工程”－“引用”－“Microsoft NT Service Control”，如果没有，请先将NTSVC.OCX拷贝到%System32%/下，然后再引用对话框中选择浏览，添加该控件。

2. 主要代码
Private Sub Form_Load()
    On Error GoTo ServiceError
    '安装Service服务
    If Command = "/i" Then
        NTService.Interactive = True
        If NTService.Install Then
            NTService.SaveSetting "Parameters", "TimerInterval", "300"
            MsgBox NTService.DisplayName & ": installed successfully"
        Else
            MsgBox NTService.DisplayName & ": failed to install"
        End If
        End
    '删除Service服务
    ElseIf Command = "/u" Then
        If NTService.Uninstall Then
            MsgBox NTService.DisplayName & ": uninstalled successfully"
        Else
            MsgBox NTService.DisplayName & ": failed to uninstall"
        End If
        End
    End If
    Timer.Interval = CInt(NTService.GetSetting("Parameters", "TimerInterval", "300"))
    NTService.ControlsAccepted = svcCtrlPauseContinue
    NTService.StartService
    Exit Sub
ServiceError:
    Call NTService.LogEvent(svcMessageError, svcEventError, "[" & Err.Number & "] " & Err.Description)
End Sub

'Unload the Service
Private Sub Form_Unload(Cancel As Integer)
    If Not StopService Then
        If MsgBox("Are you sure you want to unload the service?..." & vbCrLf & "the service will be stopped", vbQuestion + vbYesNo, "Stop Service") = vbYes Then
            NTService.StopService
            Label1.Caption = "Stopping"
            Cancel = True
        Else
            Cancel = True
        End If
    End If
End Sub

Private Sub NTService_Continue(Success As Boolean)
    On Error GoTo ServiceError
    Timer.Enabled = True
    Success = True
    NTService.LogEvent svcEventInformation, svcMessageInfo, "Service continued"
    Exit Sub
ServiceError:
    NTService.LogEvent svcMessageError, svcEventError, "[" & Err.Number & "] " & Err.Description
End Sub

Private Sub NTService_Control(ByVal mEvent As Long)
    On Error GoTo ServiceError
    Exit Sub
ServiceError:
    NTService.LogEvent svcMessageError, svcEventError, "[" & Err.Number & "] " & Err.Description
End Sub

Private Sub NTService_Pause(Success As Boolean)
    On Error GoTo ServiceError
    Timer.Enabled = False
    NTService.LogEvent svcEventError, svcMessageError, "Service paused"
    Success = True
    Exit Sub
ServiceError:
    NTService.LogEvent svcMessageError, svcEventError, "[" & Err.Number & "] " & Err.Description
End Sub

Private Sub NTService_Start(Success As Boolean)
    On Error GoTo ServiceError
    Success = True
    Exit Sub
ServiceError:
    NTService.LogEvent svcMessageError, svcEventError, "[" & Err.Number & "] " & Err.Description
End Sub

Private Sub NTService_Stop()
    On Error GoTo ServiceError
    StopService = True
    Unload Me
ServiceError:
    NTService.LogEvent svcMessageError, svcEventError, "[" & Err.Number & "] " & Err.Description
End Sub

3. 如果是有其他的控件触发Service服务的Install和Uninstall,可以采用Shell或者WinExec来处理。
先声明函数
Public Declare Function WinExec Lib "kernel32" (ByVal lpCmdLine As String, ByVal nCmdShow As Long) As Long
Public Const SW_HIDE = 0
使用，比如用CheckBox触发
a.安装
    Call WinExec(App.EXEName & " /i", SW_HIDE)

b.卸载
    Call WinExec(App.EXEName & " /u", SW_HIDE)

erp往来调整

2011-12-28T10:31:46+08:00

点击下载此文件

关于Excel的外部引用是否能全部被列出

2011-12-17T18:18:38+08:00

关于Excel的外部引用是否能全部被列出
微软的说法是不可以。
http://office.microsoft.com/zh-cn/excel-help/HP010205611.aspx
点击下载此文件

Excel用Api做定时器

2011-12-17T16:21:25+08:00

用Api做定时器
点击下载此文件

关于新报表系统设计问题

2011-12-16T16:43:20+08:00

关于新报表系统设计问题

1、建立公式汇总表。任何被更改的公式都要被记录下来。

难点：
如果公式有外部引用，那么对应的外部工作簿文件名变更该怎么办？
外部工作簿文件名、sheet名同时变更该怎么办？
外部工作簿文件名不变sheet名变怎么办？
插入单元格导致公式中的相对引用变怎么办？
--------- 针对上面所有的问题，干脆就不要考虑工作簿变动问题了，只要变动了，就断开连接，让用户重新确认公式？

公式引用的当前工作簿中的sheet名变更怎么办？-------另外建立一个表格，把公式在表格中再写一遍，sheet名变，这个公式会自动变，判断公式是否变更，也到这个表里面去比对一下。

excel 外部引用问题

2011-12-16T01:38:39+08:00

http://office.microsoft.com/zh-cn/excel-help/HP010205611.aspx


  查找工作簿中的外部引用（链接）
适用于: Microsoft Office Excel 2007

打印全部显示全部隐藏
在查找目标（目标文件:链接或嵌入对象所插入的文件。源文件包括用来创建对象的信息。当更改目标文件中的信息后，不更新源文件中的信息。）工作簿中使用的外部引用（又称为链接）时，不存在自动的方式。但是，在关闭工作簿时，外部引用会使用方括号 [ ] 将源（源文件:该文件中包含创建链接对象或嵌入对象所用的信息。更新源文件中的信息时，还可以同时更新目标文件中的链接对象。）工作簿的名称括起，例如 =SUM([Budget.xls]Annual!C10:C25)，因此可以使用这些字符来定位外部引用。

要查找目标工作簿中的所有外部引用，需要在单元格、名称、对象（例如文本框或形状）、图表标题和图表数据系列中进行查找。

您要做什么？

--------------------------------------------------------------------------------

查找单元格中使用的外部引用
查找名称中使用的外部引用
查找文本框或形状等对象中使用的外部引用
查找图表标题中使用的外部引用
查找图表数据系列中使用的外部引用

--------------------------------------------------------------------------------
查找单元格中使用的外部引用
1.关闭所有源工作簿，然后打开目标工作簿。
2.在“开始”选项卡上的“编辑”组中，单击“查找和选择”，然后单击“查找”。

将显示“查找和替换”对话框。

3.单击“选项”。
4.在“查找内容”框中，输入 [。
5.在“范围”框中，单击“工作簿”。
6.在“查找范围”框中，单击“公式”。
7.单击“查找全部”。
8.在显示的列表框的“公式”列中，查找包含 [ 的公式。
提示   单击“公式”列标题，以便对该列进行排序并将所有外部引用分组在一起。

9.要选择包含外部引用的单元格，请在列表框中选择该行。
返回页首

查找名称中使用的外部引用
1.关闭所有源工作簿，然后打开目标工作簿。
2.在“公式”选项卡上的“定义的名称”组中，单击“名称管理器”。
3.检查列表中的每一项，并在“引用位置”列中查找外部引用。外部引用包含对其他工作簿的引用，例如 [Budget.xls]。
提示   单击“引用位置”列标题，以便对该列进行排序并将所有外部引用分组在一起。

返回页首

查找文本框或形状等对象中使用的外部引用
1.关闭所有源工作簿，然后打开目标工作簿。
2.在“开始”选项卡上的“编辑”组中，单击“查找和选择”旁边的箭头，然后单击“定位条件”。

3.单击“对象”，然后单击“确定”。
4.按 Tab 键，然后在编辑栏（编辑栏:位于 Excel 窗口顶部的条形区域，用于输入或编辑单元格或图表中的值或公式。编辑栏中显示了存储于活动单元格中的常量值或公式。）  中查找对其他工作簿的引用，例如 [Budget.xls]。对象的名称将显示在“名称”框中。
5.对于工作表中的每个对象，请重复步骤 4。
6.对于工作簿中的每个工作表，请重复步骤 2 到步骤 5。
返回页首

查找图表标题中使用的外部引用
1.关闭所有源工作簿，然后打开目标工作簿。
2.在要检查的图表上单击图表标题。
3.在编辑栏（编辑栏:位于 Excel 窗口顶部的条形区域，用于输入或编辑单元格或图表中的值或公式。编辑栏中显示了存储于活动单元格中的常量值或公式。）  中，查找对其他工作簿的引用，例如 [Budget.xls]。
4.对于工作簿中具有图表标题的每个图表，请重复步骤 2 和步骤 3。
返回页首

查找图表数据系列中使用的外部引用
1.关闭所有源工作簿，然后打开目标工作簿。
2.选择要检查的图表。
3.在“布局”选项卡上的“当前选择”组中，单击“图表元素”框旁边的箭头，然后单击要检查的数据系列（数据系列:在图表中绘制的相关数据点，这些数据源自数据表的行或列。图表中的每个数据系列具有唯一的颜色或图案并且在图表的图例中表示。可以在图表中绘制一个或多个数据系列。饼图只有一个数据系列。）。

4.在编辑栏（编辑栏:位于 Excel 窗口顶部的条形区域，用于输入或编辑单元格或图表中的值或公式。编辑栏中显示了存储于活动单元格中的常量值或公式。）  中，查找对其他工作簿的引用，例如 SERIES 函数中的 [Budget.xls]。
5.对于图表中的每个数据系列，请重复步骤 3。
6.对于工作簿中的每个图表，请重复步骤 2 到步骤 5。
返回页首

accdb.net 独特诠释 access 2007 的新格式 *.accdb

VB6中使用32位图标(第二版)

SQL server 中 取出分组后每组的前几条

XML Data Manipulation with ADO 2.5

XML Streams

The Sample Apps

Conclusion

高效SQL——合并多个字段值或多条记录

高效SQL——合并多个字段值或多条记录

NTSVC.OCX控件使用帮助，VB可以用NTSVC.OCX控件将应用程序做成Service服务

erp往来调整

关于Excel的外部引用是否能全部被列出

Excel用Api做定时器

关于新报表系统设计问题

excel 外部引用问题

SQL server 中取出分组后每组的前几条