Question

vb中如何判断excel单元格为空

我写下以下代码，总提示：实时错误'1004‘
应用程序定义或对象定义错误。

Private Sub Command1_Click()

Set objexcel = CreateObject("Excel.application")
Set objworkBook = objexcel.Workbooks.Open("d:\k.xls", 3, False)
Set ExcelSheet = objworkBook.Worksheets(1)

objexcel.Visible = ture
以下三行是想如果单元格不为空就让变量n加1。
Do While ExcelSheet.cells(n, 1) <> ""

n = n + 1
Loop
ExcelSheet.cells(n, 1) = Text1.Text

objworkBook.Save
objworkBook.Close

Set objexcel = Nothing
End Sub

Answer 1

do while 循环前将n=1，cells(n,1)后加.value

Private Sub Command1_Click()

Set objexcel = CreateObject("Excel.application")
Set objworkBook = objexcel.Workbooks.Open("d:\k.xls", 3, False)
Set ExcelSheet = objworkBook.Worksheets(1)

objexcel.Visible = ture
以下三行是想如果单元格不为空就让变量n加1。
n=1'需要指定n的初始值，否则n为空值，下面的语句将错误。
Do While ExcelSheet.cells(n, 1).value <> "" 'cells后加value属性

n = n + 1
Loop
ExcelSheet.cells(n, 1).value = Text1.Text 'cells后加value属性

objworkBook.Save
objworkBook.Close

Set objexcel = Nothing
End Sub

Answer 2

以下代码测试通过

Private Sub Command1_Click()
Dim objExcelFile As Excel.Application
Dim objWorkBook As Excel.Workbook
Dim objImportSheet As Excel.Worksheet
Dim intLastColNum As Long
Dim intLastRowNum As Long
Dim N As Long
Set objExcelFile = Excel.Application
objExcelFile.DisplayAlerts = False
Set objWorkBook = objExcelFile.Workbooks.Open("d:\k.xls")
Set objImportSheet = objWorkBook.Sheets(1)
intLastColNum = objImportSheet.UsedRange.Columns.Count '最大列数
intLastRowNum = objImportSheet.UsedRange.Rows.Count '最大行数

Set objImportSheet = objExcelFile.ActiveSheet '表1开始

For N = 1 To intLastRowNum
If objExcelFile.Cells(N, 1) = "" Then Exit For
Next N
objImportSheet.Cells(N, 1) = Text1.Text
'或者用do判断'''''''''''''''''''''''''
N = 1
Do While objImportSheet.Cells(N, 1) <> ""
N = N + 1
Loop
objImportSheet.Cells(N, 1) = Text1.Text
'''''''''''''''''''''''''''''''''''''''
objWorkBook.Close
objExcelFile.Quit
Set objImportSheet = Nothing
Set objWorkBook = Nothing
Set objExcelFile = Nothing
End Sub

Answer 3

n不能为零，最小是1，cells(0,1)是不存在的

Answer 4

Do While ExcelSheet1.cells(n, 1) <> ""

n = n + 1
Loop