VBA陈旧的代码:VBA in Access、Excel、Word
最喜欢的是用:Access。
我在csdn里面回复别人的提问。
现在把每一个工作簿的第一个表的第5行第7列的单元格数据写入一个新工作簿New.xls的表"new"中,该怎么做呢,我只是举了一个简单例子以说明问题,请高手指教,万分感谢!!!
'要先引用 Excel library
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim TmpStr As String
dim NewXlApp as Excel.Application '新工作簿New.xls
dim NewXlBook as Excel.Workbook
dim NewSheet as Excel.Worksheet
Set NewXlApp = New Excel.Application
Set NewXlBook = NewXlApp.Workbooks(1)
Set NewSheet = NewXlBook.Worksheets(1)
NewSheet.Activate
For i =1 to XXX '目录下的工作簿个数
Set xlApp = New Excel.Application
Set xlBook = Nothing
Set xlSheet = Nothing
xlApp.Workbooks.open "B" & i & ".xls" '打开 B1.xls ,B2.xls ...工作簿
Set xlBook = xlApp.Workbooks(1) '一个工作簿
Set xlSheet = xlBook.Worksheets(1) '第一个表
xlSheet.Activate
NewSheet.Cells(i, 1)="B" & i '新工作簿New.xls的第一列入 B1 B2
NewSheet.Cells(i, 2)=xlSheet.Cells(5, 7) '打开B1.xls B2.xls ...工作簿第5行第7列的单元格数据写入一个新工作簿New.xls的表的第二列中
xlApp.Quit
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
Next
NewSheet.Name ="New" '表"new"中
NewXlBook.SaveAs "C:/New.xls" ' C:盘中的工作簿New.xls
NewXlApp.Quit
Set NewSheet = Nothing
Set NewXlBook = Nothing
Set NewXlApp = Nothing
ole_excel_doc.Windows(1).Caption=is_file_name
ole_excel_doc.Windows(1).WindowState= -4137 //最大化
'----在这里加上这一句:
ole_excel_doc.Windows(1).Worksheets(1).Activate
'----
ole_excel_app.Visible = true
ole_excel_app.ScreenUpdating = true
=========================================
写入数据
'要先引用 Excel library
dim NewXlApp as Excel.Application '新工作簿New.xls
dim NewXlBook as Excel.Workbook
dim NewSheet as Excel.Worksheet
Set NewXlApp = New Excel.Application
Set NewXlBook = NewXlApp.Workbooks(1)
Set NewSheet = NewXlBook.Worksheets(1)
NewSheet.Activate
NewSheet.Range("A1")="写入数据"
NewSheet.Cells(1, 2)="第一行,第二列的数据"
NewSheet.Name ="New" '表"new"中
NewXlBook.SaveAs "C:/New.xls" ' C:盘中的工作簿New.xls
NewXlApp.Quit
Set NewSheet = Nothing
Set NewXlBook = Nothing
Set NewXlApp = Nothing
=================
'要先引用 Excel library
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim TmpStr As String
dim NewXlApp as Excel.Application '新工作簿New.xls
dim NewXlBook as Excel.Workbook
dim NewSheet as Excel.Worksheet
Set NewXlApp = New Excel.Application
Set NewXlBook = NewXlApp.Workbooks(1)
Set NewSheet = NewXlBook.Worksheets(1)
NewSheet.Activate
Set xlApp = New Excel.Application
Set xlBook = Nothing
Set xlSheet = Nothing
xlApp.Workbooks.open "你的Excel文件名.xls" '包括路径。
Set xlBook = xlApp.Workbooks(1) '一个工作簿
Set xlSheet = xlBook.Worksheets(1) '第一个表
xlSheet.Activate
NewSheet.Cells(1, 1)="B" & i '新工作簿New.xls的第一列入 B1 B2
NewSheet.Cells(2, 2)=xlSheet.Cells(5, 7) '工作簿第5行第7列的单元格数据写入一个新工作簿New.xls的表的第二行,二列中
xlApp.Quit
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
NewSheet.Name ="New" '表"new"中
NewXlBook.SaveAs "C:/New.xls" ' C:盘中的工作簿New.xls
NewXlApp.Quit
Set NewSheet = Nothing
Set NewXlBook = Nothing
Set NewXlApp = Nothing