作者:乔山办公网日期:
返回目录:excel表格制作
以下是在VB6.0中标准代码,你可以改成VBA的形式。
按钮1点击后创建文件,按钮2点击后修改文件达到替换列。e68a84e8a2ade799bee5baa6330你可以手动查看修改前后数据的差异。
'工程-引用-Microsoft Excel Object library(勾选此项)
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
'写文件
Private Sub Command1_Click() '创建文件并写入原始数据
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add '新建EXCEL工件簿文件
Set xlSheet = xlBook.Worksheets(1)
For i = 1 To 10 '10行
For j = 1 To 10 '10列
xlSheet.Cells(i, j) = i * j
Next j
Next i
xlSheet.SaveAs "d:\test.xls" '按指定文件名存盘
xlApp.Quit '结束EXCEL对象
Set xlApp = Nothing '释放xlApp对象
End Sub
'替换
Private Sub Command2_Click()
REP 1, 2
End Sub
'用第m列替换第n列
Private Function REP(m As Integer, n As Integer) As Long
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Application.Workbooks.Open("d:\test.xls")
Set xlSheet = xlBook.Worksheets(1)
Dim s As String
For i = 1 To 10 '读取10行
xlSheet.Cells(i, n) = xlSheet.Cells(i, m)
Next i
Print s
xlApp.Quit '结束EXCEL对象
Set xlApp = Nothing '释放xlApp对象
End Function
Private Sub Command1_Click()
Dim A As Integer
Dim scxls As New excel.Application
Dim scbook As excel.Workbook
Dim scsheet As excel.Worksheet
Set scxls = CreateObject("excel.application") '创建EXCEL对象
scxls.DisplayAlerts = False '不显示对话框,如果D:\2\1\3.xls文件已存在,让它不出现这个提示,就需加这段代码。
Set scbook = scxls.Workbooks.Open("D:\2\4.xls")
'Set scbook = scxls.Workbooks.Add '新建636f7079e799bee5baa6e79fa5e98193336EXCEL工件簿文件
Set scsheet = scbook.Worksheets(1) '设置活动工作表
scxls.Visible = True '显示Excle
Text1.Text = scsheet.Cells(2, 2) '读取数据 并在text1里显示
Text2.Text = scsheet.Cells(2, 6) '读取数据 并在text1里显示
scsheet.SaveAs ("D:\2\1\3.xls") 'excel 存档 语法:表达式.SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)
scxls.Quit 'excel 关闭
End Sub
是不是这样的?你最好将你的TXT文件截个图,这样才好改VB代码。
'引用:
'添加对该对象的引用 工程菜单-->引用,找到 Microsoft Scripting Runtime
'引用microsoft Excel 14.0 object library
'
Dim elApp As Excel.Application
Dim elBooks As Excel.Workbook
Dim ekSheet As Excel.Worksheet
Dim TblMap_Card '创建一个变量
Private Sub Command1_Click()
Dim i As Integer
openEl
Set dic = CreateObject("Scripting.Dictionary")
'MsgBox ekSheet.Cells(Rows.Count, 1).End(3).Row
For i = 2 To ekSheet.Cells(Rows.Count, 1).End(3).Row
If dic.Exists(ekSheet.Cells(i, 2).Value) Then
dic(ekSheet.Cells(i, 1).Value) = dic(ekSheet.Cells(i, 1).Value) + ekSheet.Cells(i, 2).Value
Else
dic(ekSheet.Cells(i, 1).Value) = ekSheet.Cells(i, 2).Value
End If
Next i
ekSheet.Range("H:J").Clear
'ekSheet.Cells(1, 9).Resize(1, 2) = Array("商品", "售量")
ekSheet.Cells(2, 9).Resize(dic.Count, 1) = Application.Transpose(dic.Keys)
ekSheet.Cells(2, 10).Resize(dic.Count, 1) = Application.Transpose(dic.Items)
End Sub
Private Sub openEl()
Dim myPath As String
myPath = "\week.xlsx"
Set elApp = CreateObject("Excel.Application")
Set elBooks = elApp.Workbooks.Open(App.Path & myPath)
Set ekSheet = elBooks.Worksheets("Sheet1")
'Set ekSheet = elBooks.Worksheets(1)
elApp.Visible = True
End Sub
张志晨:VB6的,测试通e799bee5baa6e997aee7ad94e58685e5aeb9361过。界面上只有一个按钮。