求教如何将EXCEL表中第一列和第二列交换位置 - 编程论坛

#2

ZHRXJR2017-07-28 18:46

这个我认为并不难，连接Excel表格，打开工作表应该没有问题。
原数据

只有本站会员才能查看附件，请登录

转换程序界面

只有本站会员才能查看附件，请登录

保存后的数据

只有本站会员才能查看附件，请登录

#3

xyxcc1772017-07-28 19:37

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()

#4

xyxcc1772017-07-28 19:49

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()

#5

xzlxzlxzl2017-07-28 20:38

不是要求列互换吗？粗看上去3楼4楼的代码可行，就是操作excel进行列剪切粘贴。

#6

xyxcc1772017-07-28 22:38

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()

#7

xyxcc1772017-07-28 22:47

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()

#8

xiangyue05102017-07-29 19:00

VBA和VB代码有多少差别……直接在excel录制宏，剪切第二列，在第一列前面插入剪切的列即可

#9

xyxcc1772017-07-29 20:26

VBA就是VB的亲儿子

#10

ictest2017-07-31 08:40

我用这样的方法解决的，说穿了，就是把第一列复制到第三列，然后删除第一列，就完成了第一列和第二列的位置交换：
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