最近在做VB读取Excel的小程序,虽然CSDN上有许多关于VB读取Excel的文章,但没有我所需要的。
dim app as new Excel.Applicaton
dim book as excel.workbook
set book = app.workbooks.open ***
dim sheet as excel.worksheet
set sheet = book.worksheets(1)
dim ss as string
ss = sheet.cell(1,1) 一般都是这样的,但我想把Excel工作薄中的sheet名称读出放在list框里,当双击其中之一时,读出其中的内容。
读出工作薄中的sheet名称,放在list中我都实现了,可是怎么把选择的哪个sheet参数传给book.worksheets(1)它呢?
就是怎么动态改变book.worksheets(1)中的1!
高手给点指点吧。
dim app as new Excel.Applicaton
dim book as excel.workbook
set book = app.workbooks.open ***
dim sheet as excel.worksheet
set sheet = book.worksheets(1)
dim ss as string
ss = sheet.cell(1,1) 一般都是这样的,但我想把Excel工作薄中的sheet名称读出放在list框里,当双击其中之一时,读出其中的内容。
读出工作薄中的sheet名称,放在list中我都实现了,可是怎么把选择的哪个sheet参数传给book.worksheets(1)它呢?
就是怎么动态改变book.worksheets(1)中的1!
高手给点指点吧。
Option ExplicitPrivate Sub Command1_Click()
Dim i As Long
Dim dddd As String
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
dddd = "sheet3" '打开指定的表
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Open("C:\Book1.xls")
xlBook.Sheets(dddd).Select '打开指定的dddd表
xlApp.Visible = True
For i = 1 To xlBook.Worksheets.Count
Debug.Print xlBook.Worksheets(i).Name '显示Book1中有多少sheet表
Next
End Sub
On Error GoTo ErrhandlerDim xlExcel As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim AppExcel As ObjectCommonDialog1.Filter = "Excel(*.xls)|*.xls|AllFile(*.*)|*.*"
CommonDialog1.FilterIndex = 1
CommonDialog1.ShowOpenSet xlExcel = CreateObject("Excel.Application")
xlExcel.Workbooks.Open CommonDialog1.FileName
Set xlBook = xlExcel.Workbooks(CommonDialog1.FileTitle)
For Each xlSheet In xlBook.Worksheets
List1.AddItem xlSheet.Name
Next
Text2.Text = xlBook.Worksheets.Count
Errhandler:
Exit Sub
End SubPrivate Sub List1_Click()
Set xlSheet = xlBook.Worksheets(list1.listindex)
Text1.Text = xlSheet.Cells(2, 2).Value
xlBook.Save
xlBook.Close
xlExcel.Quit
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlExcel = Nothing
End Sub
这是我写的代码,我觉得list_click的代码不对但我又不知道怎么改.
我的本意是运行程序单击command1按钮,选择一个excel文件,然后在list1中显示excel中所有表的名称(这些已经能实现)。然后单击list1中其中一个表,在text1中显示指定单元格的内容。
'把你选择的哪个sheet参数传给下面程序中的dddd即可
Option ExplicitPrivate Sub Command1_Click()
Dim i As Long
Dim dddd As String
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
dddd = "sheet3" '准备打开指定的表
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Open("C:\Book1.xls")
For i = 1 To xlBook.Worksheets.Count
If InStr(LCase(xlBook.Worksheets(i).Name), LCase(dddd)) > 0 Then
xlBook.Sheets(dddd).Select '打开book1中指定的sheet3表
xlApp.Visible = True
Exit For
End If
Next
End Sub
'注意写程序规范点
Option Explicit
Dim xlExcel As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
'Dim er As Excel.Range
Dim AppExcel As Object
Private Sub Command1_Click()
On Error GoTo Errhandler
CommonDialog1.Filter = "Excel(*.xls) |*.xls |AllFile(*.*) |*.*"
CommonDialog1.FilterIndex = 1
CommonDialog1.ShowOpen
Set xlExcel = CreateObject("Excel.Application")
xlExcel.Workbooks.Open CommonDialog1.FileName
Set xlBook = xlExcel.Workbooks(CommonDialog1.FileTitle)
For Each xlSheet In xlBook.Worksheets
List1.AddItem xlSheet.Name
Next
Text2.Text = xlBook.Worksheets.Count
Errhandler:
Exit Sub
End SubPrivate Sub List1_Click()
xlBook.Sheets(List1.List(List1.ListIndex)).Select ' xlBook.Worksheets(List1.ListIndex )
Text1.Text = xlBook.Worksheets(List1.List(List1.ListIndex)).Cells(1, 1) 'xlBook.xlSheet.Cells(1, 1).Value
xlBook.Save
xlBook.Close
xlExcel.Quit
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlExcel = Nothing
End Sub
我想单击菜单文件->打开 选择一个文件后,弹出一个对话框,在对话框上选择一个工作薄上的工作表。然后在主窗口上显示指定的单元格的内容。
Dim xlExcel As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim AppExcel As Object
这段代码写在什么地方,才能顺利的传递参数呢?
我把代码放在下面,大家帮忙看看
Option Explicit
Dim xlExcel As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim AppExcel As ObjectPrivate Sub OpenFile_Click()
On Error GoTo Errhandler
CommonDialog1.Filter = "Excel(*.xls) |*.xls |AllFile(*.*) |*.*"
CommonDialog1.FilterIndex = 1
CommonDialog1.ShowOpen
Set xlExcel = CreateObject("Excel.Application")
xlExcel.Workbooks.Open CommonDialog1.FileName
Set xlBook = xlExcel.Workbooks(CommonDialog1.FileTitle)
For Each xlSheet In xlBook.Worksheets
SelectSheet.List1.AddItem xlSheet.Name
Next
SelectSheet.Show
Errhandler:
Exit Sub
End Sub
上面是主窗口的代码Option ExplicitPrivate Sub OKButton_Click()
xlBook.Sheets(List1.List(List1.ListIndex)).Select
MainFrame.Text1.Text = xlBook.Worksheets(List1.List(List1.ListIndex)).Cells(1, 1)
xlBook.Save
xlBook.Close
xlExcel.Quit
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlExcel = Nothing
End Sub
这个是弹出窗口的代码。
运行的时候xlBook.Sheets(List1.List(List1.ListIndex)).Select 这句出错。