判断EXCEL中是否存在有某个指定名字的Cell: Sub FindTheName(strName As String) Dim rng As Range
For i = 1 To ActiveWorkbook.Names.Count If UCase(ActiveWorkbook.Names(i).Name) = UCase(strName) Then Set rng = ActiveWorkbook.Names(strName).RefersToRange MsgBox "找到名字为" & strName & "的区域,位置为" & rng.Address Exit For End If Next Set rng = Nothing End Sub
如果不存在合并单元格的话,我访问每一个Cell可以走循环 for i=1 to 10 for j=1 to 10 s=sheet.Cells(i, j) next j next i 但如果存在合并单元格的话,就不能这样访问了。
如下,一个将Grid的资料导出到excel的例子。 Public Sub ToExcel(mGrid As TDBGrid) Dim ColCount, i, k As Integer Dim xlApp As New Excel.Application, xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet, sRange As String
ColCount = mGrid.Columns.Count
xlApp.visible = False MdifrmMain.DlgMain.ShowSave If MdifrmMain.DlgMain.FileName = "" Then Exit Sub If Dir$(MdifrmMain.DlgMain.FileName) <> "" Then Kill MdifrmMain.DlgMain.FileName End If Set xlBook = xlApp.Workbooks.Add Set xlSheet = xlBook.Worksheets(3) xlSheet.visible = xlSheetHidden Set xlSheet = xlBook.Worksheets(2) xlSheet.visible = xlSheetHidden Set xlSheet = xlBook.Worksheets(1) xlSheet.Name = "资料" VB.Screen.MousePointer = vbHourglass xlSheet.Range(xlSheet.Cells(1, 1), xlSheet.Cells(1, ColCount)).Merge xlSheet.Range(xlSheet.Cells(2, 1), xlSheet.Cells(2, ColCount)).Font.Size = 10
For i = 0 To ColCount - 1 xlSheet.Columns(i + 1).ColumnWidth = mGrid.Columns(i).Width / 120 If mGrid.Columns(i).visible = True Then xlSheet.Cells(2, i + 1) = mGrid.Columns(i).Caption End If Next
mGrid.MoveFirst i = 0 While Not mGrid.EOF xlSheet.Range(xlSheet.Cells(i + 3, 1), xlSheet.Cells(i + 3, ColCount)).Font.Size = 10 For k = 0 To ColCount - 1 If Not IsNull(mGrid.Columns(k).Value) Then If mGrid.Columns(k).visible = True Then xlSheet.Cells(i + 3, k + 1) = CStr(mGrid.Columns(k).Value) End If End If Next mGrid.MoveNext i = i + 1 Wend
xlBook.SaveAs MdifrmMain.DlgMain.FileName xlBook.Close False xlApp.Quit Set xlApp = Nothing VB.Screen.MousePointer = vbDefault MsgForInfo "数据导出完毕!" End Sub
合并单元格也可以象普通选区一样循环访问,只是单元格的值只有左上角那个单元格有效. 例如有一个合并区域:C9:D12 for id
set rng=sheet1.range("c9").mergearea i=rng.Rows.Count j=rng.columns.count for m=1 to i for n=1 to j str1=str1 & rng.cells(m,n).text next next
Sub FindTheName(strName As String)
Dim rng As Range
For i = 1 To ActiveWorkbook.Names.Count
If UCase(ActiveWorkbook.Names(i).Name) = UCase(strName) Then
Set rng = ActiveWorkbook.Names(strName).RefersToRange
MsgBox "找到名字为" & strName & "的区域,位置为" & rng.Address
Exit For
End If
Next
Set rng = Nothing
End Sub
for i=1 to 10
for j=1 to 10
s=sheet.Cells(i, j)
next j
next i
但如果存在合并单元格的话,就不能这样访问了。
Public Sub ToExcel(mGrid As TDBGrid)
Dim ColCount, i, k As Integer
Dim xlApp As New Excel.Application, xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet, sRange As String
ColCount = mGrid.Columns.Count
xlApp.visible = False
MdifrmMain.DlgMain.ShowSave
If MdifrmMain.DlgMain.FileName = "" Then Exit Sub
If Dir$(MdifrmMain.DlgMain.FileName) <> "" Then
Kill MdifrmMain.DlgMain.FileName
End If
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(3)
xlSheet.visible = xlSheetHidden
Set xlSheet = xlBook.Worksheets(2)
xlSheet.visible = xlSheetHidden
Set xlSheet = xlBook.Worksheets(1)
xlSheet.Name = "资料"
VB.Screen.MousePointer = vbHourglass
xlSheet.Range(xlSheet.Cells(1, 1), xlSheet.Cells(1, ColCount)).Merge
xlSheet.Range(xlSheet.Cells(2, 1), xlSheet.Cells(2, ColCount)).Font.Size = 10
For i = 0 To ColCount - 1
xlSheet.Columns(i + 1).ColumnWidth = mGrid.Columns(i).Width / 120
If mGrid.Columns(i).visible = True Then
xlSheet.Cells(2, i + 1) = mGrid.Columns(i).Caption
End If
Next
mGrid.MoveFirst
i = 0
While Not mGrid.EOF
xlSheet.Range(xlSheet.Cells(i + 3, 1), xlSheet.Cells(i + 3, ColCount)).Font.Size = 10
For k = 0 To ColCount - 1
If Not IsNull(mGrid.Columns(k).Value) Then
If mGrid.Columns(k).visible = True Then
xlSheet.Cells(i + 3, k + 1) = CStr(mGrid.Columns(k).Value)
End If
End If
Next
mGrid.MoveNext
i = i + 1
Wend
xlBook.SaveAs MdifrmMain.DlgMain.FileName
xlBook.Close False
xlApp.Quit
Set xlApp = Nothing
VB.Screen.MousePointer = vbDefault
MsgForInfo "数据导出完毕!"
End Sub
例如有一个合并区域:C9:D12
for id
i=rng.Rows.Count
j=rng.columns.count
for m=1 to i
for n=1 to j
str1=str1 & rng.cells(m,n).text
next
next