| 网站首页 | 业界新闻 | 群组 | 人才 | 技术文章 | 下载频道 | 博客 | 代码贴 | 编程论坛
绝地游戏外挂辅助教学千里之行 始于足下
共有 701 人关注过本帖
标题:求教如何将EXCEL表中第一列和第二列交换位置
只看楼主 收藏
ictest
Rank: 2
等 级:论坛游民
帖 子:185
专家分:53
注 册:2010-2-17
结帖率:70.37%
  已结贴   问题点数:20  回复次数:9   
求教如何将EXCEL表中第一列和第二列交换位置
如题,
如何将EXCEL表中第一列和第二列交换位置,使原来的第一列变成第二列,原来的第二列变成第一列。
不用VBA,求纯VB代码。
2017-07-28 14:12
ZHRXJR
Rank: 16Rank: 16Rank: 16Rank: 16
等 级:版主
威 望:53
帖 子:558
专家分:3188
注 册:2016-5-10
  得分:5 
这个我认为并不难,连接Excel表格,打开工作表应该没有问题。
原数据
转换程序界面
保存后的数据
附件: 您没有浏览附件的权限,请 登录注册

QQ    2653043392
2017-07-28 18:46
xyxcc177
Rank: 20Rank: 20Rank: 20Rank: 20Rank: 20
等 级:版主
威 望:16
帖 子:87
专家分:381
注 册:2017-7-8
  得分:5 
Dim excel As Object
        excel = CreateObject("excel.application")
        Dim book As Object
        Dim sheet As Object
        excel.visible = True
        book = excel.workbooks.add
        sheet = book.worksheets(1)
        sheet.Cells(1, 1) = 1
        sheet.Cells(1, 2) = 2
        sheet.Columns(2).select()
        excel.Selection.Cut()
        sheet.Columns(1).select()
        excel.selection.insert()
2017-07-28 19:37
xyxcc177
Rank: 20Rank: 20Rank: 20Rank: 20Rank: 20
等 级:版主
威 望:16
帖 子:87
专家分:381
注 册:2017-7-8
  得分:0 
Dim excel As Object
        excel = CreateObject("excel.application")
        Dim book As Object
        Dim sheet As Object
        excel.visible = True
        book = excel.workbooks.open("d:\ssss.xlsx") '打开一个文件
        sheet = book.worksheets(1)
      
        sheet.Columns(2).select()
        excel.Selection.Cut()
        sheet.Columns(1).select()
        excel.selection.insert()
2017-07-28 19:49
xzlxzlxzl
Rank: 14Rank: 14Rank: 14Rank: 14
来 自:湖北
等 级:贵宾
威 望:99
帖 子:1009
专家分:5369
注 册:2014-5-3
  得分:5 
不是要求列互换吗?粗看上去3楼4楼的代码可行,就是操作excel进行列剪切粘贴。
2017-07-28 20:38
xyxcc177
Rank: 20Rank: 20Rank: 20Rank: 20Rank: 20
等 级:版主
威 望:16
帖 子:87
专家分:381
注 册:2017-7-8
  得分:0 
Dim excel As Object
        excel = CreateObject("excel.application")
        Dim book As Object
        Dim sheet As Object
        excel.visible = False
        book = excel.workbooks.open("d:\abc.xlsx")
        sheet = book.worksheets(1)
        Dim str1 As String
        Dim str2 As String
        str1 = sheet.Cells(1, 1).value
        str2 = sheet.Cells(1, 2).value
        sheet.Columns(2).select()
        excel.Selection.Cut()
        sheet.Columns(1).select()
        excel.selection.insert()
        sheet.Cells(1, 1).value = str1
        sheet.Cells(1, 2).value = str2
        book.save()
        excel.quit()
2017-07-28 22:38
xyxcc177
Rank: 20Rank: 20Rank: 20Rank: 20Rank: 20
等 级:版主
威 望:16
帖 子:87
专家分:381
注 册:2017-7-8
  得分:0 
Dim excel As Object
        excel = CreateObject("excel.application")
        Dim book As Object
        Dim sheet As Object
        excel.visible = False
        book = excel.workbooks.open("d:\abc.xlsx")
        sheet = book.worksheets(1)
        Dim str1 As String
        Dim str2 As String
        Dim i As Integer
        i = 2
        Do While True
            str1 = CStr(sheet.Cells(i, 1).value)
            str2 = CStr(sheet.Cells(i, 2).value)
            sheet.Cells(i, 1).value = str2
            sheet.Cells(i, 2).value = str1
            i = i + 1
            If str1 = "" And str2 = "" Then Exit Do
        Loop
        book.save()
        excel.quit()
2017-07-28 22:47
xiangyue0510
Rank: 20Rank: 20Rank: 20Rank: 20Rank: 20
等 级:版主
威 望:79
帖 子:826
专家分:4857
注 册:2015-8-10
  得分:5 
VBA和VB代码有多少差别……直接在excel录制宏,剪切第二列,在第一列前面插入剪切的列即可
2017-07-29 19:00
xyxcc177
Rank: 20Rank: 20Rank: 20Rank: 20Rank: 20
等 级:版主
威 望:16
帖 子:87
专家分:381
注 册:2017-7-8
  得分:0 
VBA就是VB的亲儿子
2017-07-29 20:26
ictest
Rank: 2
等 级:论坛游民
帖 子:185
专家分:53
注 册:2010-2-17
  得分:0 
我用这样的方法解决的,说穿了,就是把第一列复制到第三列,然后删除第一列,就完成了第一列和第二列的位置交换:
Private Sub Command1_Click()
Dim z As Integer
Dim i As Integer
Dim s As String

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
For z = 0 To File1.ListCount - 1
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then Set xlApp = CreateObject("Excel.Application")
On Error GoTo prcERR
Set xlBook = xlApp.Workbooks.Open(Dir1.Path & "\" & File1.List(z)) '打开你的EXCEL文件
xlApp.DisplayAlerts = False
xlApp.Visible = False
Set xlSheet = xlBook.Worksheets(1) '第一个表格
xlSheet.Application.Visible = False '设置Excel 不可见
xlSheet.Columns(1).Copy xlSheet.Columns(3)
xlSheet.Columns(1).Delete

xlBook.Close True  '先保存修改再关闭工作簿
xlApp.Quit
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

prcERR:
 Debug.Print Err.Number & ":" & Err.Description
 Next z
 MsgBox "OK"
End Sub
2017-07-31 08:40







关于我们 | 广告合作 | 编程中国 | 清除Cookies | TOP | 手机版

编程中国 版权所有,并保留所有权利。
Powered by Discuz, Processed in 0.078887 second(s), 8 queries.
Copyright©2004-2017, BCCN.NET, All Rights Reserved