Ping's Memory: 十二月 2012

2012年12月28日星期五

Access中国/Office中国不打开EXCEL文件的前提下读取数据的方法之一access教程,access文章,access培训,access下载,access破解,access技巧,access源码,access实例,access数据库,access开发,access软件,ACCESS学习,access应用 - Powered by Discuz!

Access中国/Office中国不打开EXCEL文件的前提下读取数据的方法之一access教程,access文章,access培训,access下载,access破解,access技巧,access源码,access实例,access数据库,access开发,access软件,ACCESS学习,access应用 - Powered by Discuz!

比如读取D:\税金.xls中sheet1中A1:B20的数据

dim i as integer

application.screenupdating=false

for i=1 to 20

activesheet.cells(i,1).formula="='D:\[税金.xls]sheet1'!$A$" & i

activesheet.cells(i,2).formula="='D:\[税金.xls]sheet1'!$B$" & i

next i

activesheet.range("a1").currentregion.copy

activesheet.range("a1").PasteSpecial xlPasteValues

Application.CutCopyMode = False

application.screenupdating=true

运行一下后，发现比打开、读取、关闭的操作快多了，特与大家分享。

Code Modules And Code Names

Code Modules

A common mistake among new VBA programmers is that they put their code in the wrong module. When this happens, Excel can't find the code, and it can't be executed. This page describes the different types of modules in Excel VBA, and what you should and shouldn't put in each type. Much of this information is specific to Excel97 and 2000, and may not apply to Excel5 or Excel95.

In Excel VBA, there are four main types of modules:

Standard Code Modules, which contain custom macros and functions,
Workbook And Sheet Code Modules, which contain event procedures for the workbook, and worksheets and chart sheets,
User Forms, which contain code for the controls on a UserForm object,
Class Modules, which contain Property Let, Get, and Set procedures for Objects that you create.

It matters very much where you put your code.

NOTE: I must add, for the sake of accuracy, that the Sheet modules, the ThisWorkbook module, and the Userform modules are all really just different flavors of Class Modules. You can create Property Get/Let/Set procedures, and methods and functions (and events) in these classes, just as you can for "standard" class modules. Various techniques for using your forms and sheets as classes will be described in the "Advanced Form Techniques" page, coming to a server near you very soon.

Standard Code Modules, also called simply Code Modules or just Modules, are where you put most of your VBA code. Your basic macros and your custom function (User Defined Functions) should be in these modules. For the novice programmer, all your code will be in standard modules. In addition to your basic procedures, the code modules should contain any Declare statements to external functions (Windows APIs or other DLLs), and custom Data Structures defined with the Type statement.

Your workbook's VBA Project can contain as many standard code modules as you want. This makes it easy to split your procedure into different modules for organization and ease of maintenance. For example, you could put all your database procedures in a module named DataBase, and all your mathematical procedures in another module called Math. As long as a procedure isn't declared with the Private keyword, or the module isn't marked as private, you can call any procedure in any module from any other module without doing anything special.

Workbook And Sheet Modules are special modules tied directly to the Workbook object and to each Sheet object. The module for the workbook is called ThisWorkbook, and each Sheet module has the same name as the sheet that it is part of. These modules should contain the event procedures for the object, and that's all. If you put the event procedures in a standard code module, Excel won't find them, so they won't be executed. And if you put ordinary procedures in a workbook or sheet module, you won't be able to call them without fully qualifying the reference.

User Form Modules are part of the UserForm object, and contain the event procedures for the controls on that form. For example, the Click event for a command button on a UserForm is stored in that UserForm's code module. Like workbook and sheet modules, you should put only event procedures for the UserForm controls in this module.

Class Modules are used to create new objects. Class modules aren't discussed here, except to say that a class module is used to handle Application Event Procedures.

Code Names

Workbook and sheet modules have a property called CodeName, which is how the object is know internally to VBA. By default, the workbook code name is ThisWorkbook, and each sheet module is Sheet1, Sheet2, etc for Worksheets, or Chart1, Chart2, etc for ChartSheets. You can use these names in your VBA code as you would normal variables. For example

Msgbox ThisWorkbook.Nameor
Msgbox Sheet1.Name

This is useful so that you can always refer to a worksheet, for example, even if the user renames the sheet from Excel. For example, if you have a sheet called "Sheet1", both its name and code name will be Sheet1. But if the user renames the sheet to MySheet, the code

Msgbox Worksheets("Sheet1").Name

will fail, because there is no longer a sheet named Sheet1. However, the code

Msgbox Sheet1.Name

will continue to work, because VBA still knows that worksheet by its code name of Sheet1.

You can change the code name of either the ThisWorkbook or a Sheet object. If you do this once you already have code in these modules, you can run into problems, so only do this if you 1) know what you're doing, and 2) need to do this. To change the code name of a module, select the module in the Project Explorer window, and the open the Properties Windows (F4 or from the View menu), and change the Name property. If you change the code name of the ThisWorkbook object, ThisWorkbook will continue to refer to the workbook object. For example, if you change the code name of the ThisWorkbook object to MyWorkbook, both of the following lines of code will work:

Msgbox ThisWorkbook.Name
Msgbox MyWorkbook.Name

However, if you change the code name for the Sheet1 object to MySheet, the following code will fail

Msgbox Sheet1.Name

because there is no longer a sheet object with a code name of Sheet1.

Moreover, you can change the code name of an object with a VBA procedure. However, this can lead to many problems, so again, don't do it unless you know what you're doing and you really need to do this. To change the code name of sheet with a code name of Sheet1 to NewCodeName, use

ThisWorkbook.VBProject.VBComponents("Sheet2").Name= "NewCodeName"

You can change the code name of the ThisWorkbook object to "NewWBName" with

ThisWorkbook.VBProject.VBComponents("ThisWorkbook").Name = "NewWBName"

Just to make things more complicated, when you change the code name of the ThisWorkbook object, and you're using the VBA Extensibility library procedures, the code

Msgbox ThisWorkbook.Name
will continue to work, but

Msgbox ThisWorkbook.VBProject.VBComponents("ThisWorkbook").Name

will fail, because there is no object with a code name ThisWorkbook.

In general, changing code names is not for the casual user. For more information about programming the VBA components, see Programming To The VBE.

How Do I move a Spreadsheet to a New Instance of Excel - Page 2

Sub NewInstance()
    Dim sCurFile As String
    sCurFile = ActiveWorkbook.FullName
    ActiveWorkbook.Close
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True
    objExcel.Workbooks.Open (sCurFile)
End Sub

Multiple Excel Files Open on Multiple Tabs, Instead of One Tab - Microsoft Community

Multiple Excel Files Open on Multiple Tabs, Instead of One Tab - Microsoft Community

Another option is to right-click the Excel taskbar button and to select Microsoft Office Excel 2010 from the popup menu.

If you want to view workbooks on two screens, you'll have to start two separate instances of Excel. Start Excel, then Shift+click the Excel icon on the Windows taskbar to open a second instance, and drag the new window to the other screen (if it is maximized, un-maximize the window first).

web

http://iask.sina.com.cn/u/2763276865/ish

2012年12月27日星期四

休士頓的六品烘焙館 - Helen 在德州 - Yahoo!奇摩部落格

休士頓的六品烘焙館

分類：生活情報

2010/03/13 18:50

Facebook Plurk YAHOO!

愛吃台灣式麵包的Helen, 在DALLAS地區總是很難得到滿足! 少數幾家麵包店所賣的麵包種類, 還停留在台灣20年前的老式口味. 每次我回台灣一趟, 吃了台灣的麵包後, 就益發覺得此地的麵包"乏善可陳"!

去年, 在休士頓發現了一家很棒的麵包店, "六品西點麵包", 位於百利大道上的頂好超市廣場. 麵包種類多又好吃, 生意超好, 東西絕對新鮮. 於是, 每當我們去休士頓祭拜老爸的時候, 總會順便買一大拖拉庫的麵包回來, 解解饞!

別看這家小小的店面, 每次我們光顧時, 總是顧客大排長龍, 我們在休士頓只停留一晚, 總要在第一天到達時就先去預定第二天取的麵包. 而這次, 依舊生意興隆, 但是架上麵包所剩無幾. 一問之下, 原來他們開了新店面, 中央廚房設在那裡, 烘焙好的麵包送過來老店賣. 既然如此, 我們就乾脆去新店逛逛, 看是否有較多的選擇!

新店位於越華超市廣場, 那是個嶄新的購物廣場, 看看"六品"的新門面!