PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:WINDOWS:5.0,6.0
OPER/SYS:WINDOWS
KEYWORDS:

======================================================================
---------------------------------------------------------------------
The information in this article applies to:

- Microsoft Visual Basic Learning, Professional, and Enterprise Editions
for Windows, versions 5.0, 6.0
on the following platforms: NT, Win95, Win98
---------------------------------------------------------------------

SUMMARY
=======

By default, the MSChart control uses colors to differentiate between data
sets. This works well on a color monitor or printer, but if a printout to a
monochrome printer is created, the colors may be hard to distinguish. Using
the code provided, you can programmatically change the MSChart control to
use patterns instead of colors.

MORE INFORMATION
================

Modifying some properties is all that needs to be done to get the MSChart
control to use patterns.

Step-by-Step Example
--------------------

1. Create a new Standard EXE project in Visual Basic. Form1 is created by
default.

2. Choose Components from the Project menu to show the Components Dialog.

3. In Visual Basic 5.0, select "Microsoft Chart Control." In Visual Basic
6.0, select "Microsoft Chart Control 6.0 (OLEDB)."

4. Place an instance of the Chart control and a CommandButton on Form1.

5. Place the following code in the General Declaration Section of Form1:

Private Sub Command1_Click()
MSChart1.Plot.SeriesCollection(1).DataPoints(-1).Brush.Style _
= VtBrushStylePattern
MSChart1.Plot.SeriesCollection(1).DataPoints(-1).Brush.Index _
= VtBrushPatternBoldDownDiagonal
MSChart1.Plot.SeriesCollection(1).DataPoints(-1).Brush.FillColor.Set _
255, 255, 255

MSChart1.Plot.SeriesCollection(2).DataPoints(-1).Brush.Style _
= VtBrushStylePattern
MSChart1.Plot.SeriesCollection(2).DataPoints(-1).Brush.Index _
= VtBrushPatternBoldHorizontal
MSChart1.Plot.SeriesCollection(2).DataPoints(-1).Brush.FillColor.Set _
255, 255, 255

MSChart1.Plot.SeriesCollection(3).DataPoints(-1).Brush.Style _
= VtBrushStylePattern
MSChart1.Plot.SeriesCollection(3).DataPoints(-1).Brush.Index _
= VtBrushPatternBoldUpDiagonal
MSChart1.Plot.SeriesCollection(3).DataPoints(-1).Brush.FillColor.Set _
255, 255, 255

MSChart1.Plot.SeriesCollection(4).DataPoints(-1).Brush.Style _
= VtBrushStylePattern
MSChart1.Plot.SeriesCollection(4).DataPoints(-1).Brush.Index _
= VtBrushPatternBoldVertical
MSChart1.Plot.SeriesCollection(4).DataPoints(-1).Brush.FillColor.Set _
255, 255, 255
End Sub

6. Run the project and click Command1 and note that the chart now uses
monochrome patterns instead of colors.
返回

PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:WINDOWS:5.0,6.0,97
OPER/SYS:WINDOWS
KEYWORDS:

======================================================================
---------------------------------------------------------------------
The information in this article applies to:

- Microsoft Visual Basic Learning, Professional, and Enterprise Editions
for Windows, versions 5.0, 6.0
- Microsoft Access 97
---------------------------------------------------------------------

SUMMARY
=======

There is no Automation method in the object model of Access that allows
Visual Basic to open a secured Access database without getting a prompt
requesting a username and password. However, it is possible to accomplish
this using the Shell command. This article demonstrates how to open a
secured Access database without getting a prompt.

MORE INFORMATION
================

There are two ways to secure a Microsoft Access database. One is to furnish
individual MDBs with passwords. Although in DAO, you can use the
OpenDatabase method to open such a database without getting a password
prompt, there is no method to do so in Access. The second method is to
provide a series of usernames and passwords to secure Access itself. The
username and password prompt can be avoided in this case by using the Shell
command and the GetObject method.

The main problem with using the Shell command to open a secured Access
database is that Access does not register itself in the running object
table until it has lost focus once. This means that until Access loses
focus, it cannot be found with a call to GetObject and automated. The
following Visual Basic code demonstrates how to launch a secured Access
database and get the running instance of Access so it can be automated.

Step by Step Example
--------------------

1. Open a new Standard EXE project in Visual Basic. Form1 is created by
default.

2. Choose References from the Project menu, check "Microsoft Access 8.0
Object Library," and then click OK.

3. Add a CommandButton to Form1 and put the following code into Form1's
code window:

Private Declare Sub Sleep Lib "Kernel32" (ByVal dwMS As Long)

Private Sub Command1_Click()
Dim accObj As Access.application, Msg As String
Dim application As String, dbs As String, workgroup As String
Dim user As String, password As String, cTries As Integer
Dim x

' This is the default location of Access
application = "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE"
' Use the path and name of a secured MDB on your system
dbs = "C:\TestDatabase.mdb"
' This is the default workgroup
workgroup = "C:\Windows\System\System.mdw "
user = "Admin" ' Use a valid username
password = "Mypassword" ' and correct password

x = Shell(application & " " & dbs & " /nostartup /user " & user & _
" /pwd " & password & " /wrkgrp " & workgroup, vbMinimizedFocus)

On Error GoTo WAITFORACCESS
Set accObj = GetObject(, "Access.Application")

' Turn off error handling
On Error GoTo 0

' You can now use the accObj reference to automate Access

Msg = "Access is now open. You can click on Microsoft Access "
Msg = Msg & "in the Taskbar to see that your database is open."
Msg = Msg & vbCrLf & vbCrLf & "When ready, click OK to close."
MsgBox Msg, , "Success!"

accObj.CloseCurrentDatabase
accObj.Quit

Set accObj = Nothing
MsgBox "All Done!", vbMsgBoxSetForeground

Exit Sub

WAITFORACCESS: ' <--- This line must be left-aligned.
' Access isn't registered in the Running Object Table yet, so call
' SetFocus to take focus from Access, wait half a second, and try
' again. If you try five times and fail, then something has probably
' gone wrong, so warn the user and exit.
SetFocus
If cTries < 5 Then
cTries = cTries + 1
Sleep 500 ' wait 1/2 seconds
Resume
Else
MsgBox "Access is taking too long. Process ended.", _
vbMsgBoxSetForeground
End If
End Sub

4. Run the project and click on Command1. Your secured MDB will open
without prompting you, and a message box will pause the code so that you
can verify that your database is actually open. You can then click OK to
dismiss the message box and close Access.
返回

PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:
OPER/SYS:WINDOWS
KEYWORDS:

======================================================================
---------------------------------------------------------------------
The information in this article applies to:

- Microsoft Visual Basic Professional and Enterprise Editions for
Windows, version 6.0
---------------------------------------------------------------------

SUMMARY
=======

An empty binary field formatted with rules to print text, such as "Yes" for
True and "No" for False, will return an error unless nulls are allowed.

MORE INFORMATION
================

Steps to Reproduce Behavior
---------------------------

1. Create a new standard EXE project in Visual Basic. Form1 is created by
default.

2. In the project, reference Microsoft ActiveX Data Objects 2.0 Library and
Microsoft Data Formatting Object Library.

3. Add a DataGrid to the form.

4. Add the following code and run:

Option Explicit

Private rs As ADODB.Recordset
Private fmtBooleanData As StdDataFormat

Private Sub Form_Load()
Dim i As Integer

Set rs = New ADODB.Recordset
rs.Fields.Append "Field1", adBSTR, 64
rs.Fields.Append "BooleanField", adBoolean
rs.Open

rs.AddNew
rs.Fields("Field1").Value = "Field1"
rs.Fields("BooleanField").Value = True
rs.Update

For i = 1 To 5
rs.AddNew
rs.Update
Next i
rs.MoveFirst

Set DataGrid1.DataSource = rs

' set up Boolean Formatting
Set fmtBooleanData = New StdDataFormat
fmtBooleanData.Type = fmtBoolean
fmtBooleanData.TrueValue = "Yes"
fmtBooleanData.FalseValue = "No"
fmtBooleanData.NullValue = ""

Set DataGrid1.Columns(1).DataFormat = fmtBooleanData

End Sub

When the code is run, you will see "Yes" in the 2nd column of the first
row, and #ERROR in the rest.

This occurs because OLE DB cannot determine what to return for an empty non-
nullable field, and therefore raises an error. Marking the field as
nullable by changing the line:

rs.Fields.Append "BooleanField", adBoolean

to:

rs.Fields.Append "BooleanField", adBoolean, , adFldIsNullable

will result in the empty fields returned as NULLs rather than errors.
返回

PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:
OPER/SYS:WINDOWS winnt
KEYWORDS:

======================================================================
---------------------------------------------------------------------
The information in this article applies to:

- Microsoft Visual Basic for Windows Learning, Professional, and
Enterprise Editions, version 6.0
- Microsoft Visual Basic Professional and Enterprise Editions for
Windows, version 5.0
- The DAO SDK
---------------------------------------------------------------------

SUMMARY
=======

The Excel ISAM driver is limited in the sense that it does not dynamically
convert datatypes.

MORE INFORMATION
================

If there is a column in your Excel spreadsheet that contains both text and
numbers, the ISAM will not be able to correctly interpret which datatype it
should be. Please make sure that all the cells in a column are formatted to
be the same datatype. For example, you might have following data in four
columns in an Excel sheet:

male female children teens
11 cc 78 ee
22 xx 33 ff
45 uu 56 oo

If you try to read the data through ISAM driver against the whole sheet,
you will get the null values for first row. If you want to avoid this,
create named ranges; one containing only the header information and another
one containing the data information, such as:

named range 'myRange1' :
male female children teens

named range 'myRange2' :
11 cc 78 ee
22 xx 33 ff
45 uu 56 oo

Now you can connect to Excel and request information only from the
particular named range. However, in one range, one particular column can
contain only one type of data.

Creating a Range
----------------

Highlight the data. From the menu, select Insert->Name->Define->rangename.
Note that the "refers to" box below will refer to your highlighted range;
this should grow and shrink as data is inserted and deleted. To retrieve
your data, use the range name you just created for the table name in your
select statement.

Excel Steps
-----------

1. Create the Excel file, test.XLS, with following data in sheet1:

excel File : test.xls with the following entries:

male female children teens
11 cc 78 ee
22 xx 33 ff
45 uu 56 oo

2. Create the named range, myRange1 and myRange2, in the sheet containing
the appropriate data.

named range : myRange1

male female children teens

named range : myRange2

11 cc 78 ee
22 xx 33 ff
45 uu 56 oo

Visual Basic Steps
------------------

1. Create a new standard EXE project called "DAO_EXCEL."

2. Select References from the project menu and check Microsoft
DAO 3.5 Library.

3. Place a CommandButton on the form.

4. Paste the following code in the form code window:

private Sub Command_click1

im dbtmp As DAO.Database
im tblObj As DAO.TableDef
im rs As DAO.Recordset

et dbtmp = OpenDatabase_
("<complete path>\test.xls", False, True, "Excel 8.0;")

DoEvents

Set rs = dbtmp.OpenRecordset("select * from `myRange2`")
While Not rs.EOF
For x = 0 To rs.Fields.Count - 1
Debug.Print rs.Fields(x).Value
Next
rs.MoveNext
Wend
End Sub

Note the reverse apostrophe "`" while specifying the range
name(myrange2).

The following results are as expected:

11
cc
78
ee
22
xx
33
ff
45
uu
56
oo
返回

PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:WINDOWS:5.0
OPER/SYS:WINDOWS
KEYWORDS:kbVBp500 kbVBp600 kbCtrl kbcode

======================================================================
---------------------------------------------------------------------
The information in this article applies to:

- Microsoft Visual Basic Professional and Enterprise Editions for
Windows, versions 5.0, 6.0
---------------------------------------------------------------------

SUMMARY
=======

The MergeCells property determines whether cells with the same contents
should be grouped in a single cell spanning multiple rows or columns. The
different values available for MergeCells let you specify how the cell
merging should be handled. Setting MergeCells to a value other than zero
enables cell merging. To have the FlexGrid or Hierarchical FlexGrid perform
the merge, you must set the MergeCol or MergeRow property to True for each
Column or Row you want to merge.

MORE INFORMATION
================

This example uses the FlexGrid control. You can substitute the Hierarchical
FlexGrid for the FlexGrid control in this sample.

When MergeRow or MergeColumn are true, the merged row or column may not
merge correctly with a fixed column or row. The sample project in this
article demonstrates this problem. Merging works best with non-fixed
columns and rows.

This sample demonstrates merging with MergeCells set to FlexMergeFree. This
allows merging on any row or column that has the MergeRow or MergeCol
property set to True.

Steps to Create Sample Project
------------------------------

1. Create a new Standard EXE project. Form1 is created by default.

2. Click Components on the Projects menu to open the Components dialog
box. On the Controls tab, select Microsoft FlexGrid Control and click
OK.

3. Place a FlexGrid control and four CommandButtons on Form1.

4. Add the following code to code window for Form1:

Option Explicit
Dim iFixedRows As Integer
Dim iFixedCols As Integer

Private Sub Command1_Click()
iFixedCols = iFixedCols + 1
If iFixedCols > MSFlexGrid1.Cols - 1 Then
iFixedCols = MSFlexGrid1.Cols - 1
MsgBox "No more columns to fix"
Else
MSFlexGrid1.FixedCols = iFixedCols
End If
End Sub

Private Sub Command2_Click()
iFixedCols = iFixedCols - 1
If iFixedCols < 0 Then
iFixedCols = 0
MsgBox "No more Columns to unfix"
Else
MSFlexGrid1.FixedCols = iFixedCols
End If
End Sub

Private Sub Command3_Click()
iFixedRows = iFixedRows + 1
If iFixedRows > MSFlexGrid1.Rows - 1 Then
iFixedRows = MSFlexGrid1.Rows - 1
MsgBox "No more rows to fix"
Else
MSFlexGrid1.FixedRows = iFixedRows
End If
End Sub

Private Sub Command4_Click()
iFixedRows = iFixedRows - 1
If iFixedRows < 0 Then
iFixedRows = 0
MsgBox "No more rows to unfix"
Else
MSFlexGrid1.FixedRows = iFixedRows
End If
End Sub

Private Sub Form_Load()
Command1.Caption = "Increment fixed Columns"
Command2.Caption = "Decrease fixed Columns"
Command3.Caption = "Increment fixed Rows"
Command4.Caption = "Decrease fixed Rows"

FillFlexGrid 'call sub that puts data into grid

iFixedRows = MSFlexGrid1.FixedRows
iFixedCols = MSFlexGrid1.FixedCols
End Sub

Private Sub FillFlexGrid()
Dim sEntry As String
With MSFlexGrid1
.Cols = 5 'set the number of columns
' Set FixedRows to 0 so you can use AddItem. You can't
' use AddItem with a FixedRow. You have to use the Text property
.FixedRows = 0
.MergeCells = flexMergeFree 'Set the MergeCells property.
.Font.Name = "fixedsys"
.Font.Size = 8
.Row = 0
.Col = 0
.ColWidth(0) = 1750

' Add some data to the grid using AddItem.
sEntry = "Region" & vbTab & "Region" & vbTab & "Employee"
.AddItem sEntry, 0
sEntry = "Northwest" & vbTab & "Bats" & vbTab & "Bats"
.AddItem sEntry, 1
sEntry = "Northwest" & vbTab & "Whahoos"
.AddItem sEntry, 2
sEntry = "Northwest" & vbTab & "Whahoos"
.AddItem sEntry, 3
sEntry = "Northwest" & vbTab & "Sharks"
.AddItem sEntry, 4
sEntry = "Southeast" & vbTab & "Panthers"
.AddItem sEntry, 5
sEntry = "Southeast" & vbTab & "Panthers"
.AddItem sEntry, 6
sEntry = "Southeast" & vbTab & "Desktops"
.AddItem sEntry, 7
sEntry = "Southeast" & vbTab & "Desktops"
.AddItem sEntry, 8
sEntry = "Mid-Atlantic" & vbTab & "VSx7"
.AddItem sEntry, 9
sEntry = "Mid-Atlantic" & vbTab & "VSx7"
.AddItem sEntry, 10

' Set the rows and columns you want to merge.
.MergeCol(0) = True 'First Column in grid
.MergeCol(1) = True 'Second Column in grid

.MergeRow(0) = True 'First Row in Grid
.MergeRow(1) = True 'Second Row in Grid
' Set the fixed columns and rows.
.FixedCols = 1
.FixedRows = 1
End With
End Sub

5. Save and run the project. Use the CommandButtons to add or remove
fixed columns or rows to see how they affect a merged column or row.
返回

Back to top