使用宏将数据从excel复制到csv [英] Copy data from excel to csv using macro

问题描述

我想将数据从excel复制到csv，但似乎找不到正确的逻辑。下面是我要如何复制数据。例如，我想将数据从单元格D4的excel到csv中的A4。我想重复一遍，直到列D中的单元格为空。

I want to copy data from excel to csv but cant seem to find the correct logic. Below is how I want to copy the data. For example I want to copy data in cell D4 of excel to A4 in csv. I want to repeat this until a cell in column D is empty.

"Excel ---> CSV
"D4-->A4
"F4&G4-->B4
"K4-->E4
"L4-->F4
"N4-->G4
"P4-->I4

对不起，问我一个基本的问题，我刚刚开始写宏，下面是我的代码。创建csv文件，但不填充我需要的数据。

Sorry to ask such a basic question as I just started writing macros. Below is my code currently. This creates the csv file but does not populate the data I need.

Sub csvfile()

Dim fs As Object, a As Object, i As Integer, s As String, t As String, l As String, mn As String
Set fs = CreateObject("Scripting.FileSystemObject")
sUser = Environ("username")
Set a = fs.CreateTextFile("S:\ics\jellybean\" & sUser & ".csv", True)

For r = 4 To Range("A65536").End(xlUp).Row
    s = ""
    c = 6
    While Not IsEmpty(Cells(r, c))
        s = s & Cells(r, c) & ","
        c = c + 1
    Wend
    a.writeline s 'write line
Next r

End Sub

推荐答案

这样使用数组， （假设您的数据具有与感兴趣的列相同的从D到P的列长度）

Somewthing like this uses array's and is very efficient (it assumes your data has the same column length from D to P for the columns of interest)

Sub csvfile()

Dim fs As Object
Dim a As Object
Dim lngRow As Long
Dim X

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("c:\temp\" & Environ("username") & ".csv", True)
X = Range([d4], Cells(Rows.Count, "P").End(xlUp)).Value2

For lngRow = 1 To UBound(X)
a.writeline X(lngRow, 1) & "," & X(lngRow, 3) & X(lngRow, 4) & ",,," & X(lngRow, 8) & "," & X(lngRow, 9) & "," & X(lngRow, 11) & ",," & X(lngRow, 13)
Next
a.Close

End Sub

这篇关于使用宏将数据从excel复制到csv的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！