如何使用VBA将Excel工作表中的单元格值(不是范围)插入到现有的csv文件中

Question

我已经成功地编写了代码，使用循环将工作表中的单元格值插入SQL表中。 原始工作表中有未使用的列和未使用的行以及不正确的标题，这些由VBA代码管理 现在数据库正在关闭，我们希望使用 csv 文件将数据上传到另一个数据库。 使用什么代码打开csv文件并逐个单元格插入数据？

Answer 1

1. 遍历所有活动工作表。
2. 对于每个工作表，循环访问所有使用的行。
3. 对于每一行，循环访问所有列。
4. 通过将每个单元格值包装为双精度来构建行字符串 引号并用逗号分隔。
5. 将行字符串写入 csv 文件。

Const sFilePath = "C:testmyfile.csv"
Const strDelim = ","
Sub CreateCSV_Output()
Dim ws As Worksheet
Dim rng1 As Range
Dim X
Dim lRow As Long
Dim lCol As Long
Dim strTmp As String
Dim lFnum As Long
lFnum = FreeFile
Open sFilePath For Output As lFnum
For Each ws In ActiveWorkbook.Worksheets
'test that sheet has been used
Set rng1 = ws.UsedRange
If Not rng1 Is Nothing Then
'only multi-cell ranges can be written to a 2D array
If rng1.Cells.Count > 1 Then
X = ws.UsedRange.Value2
'The code TRANSPOSES COLUMNS AND ROWS by writing strings column by column
For lCol = 1 To UBound(X, 2)
'write initial value outside the loop
strTmp = IIf(InStr(X(1, lCol), strDelim) > 0, """" & X(1, lCol) & """", X(1, lCol))
For lRow = 2 To UBound(X, 1)
'concatenate long string & (short string with short string)
strTmp = strTmp & (strDelim & IIf(InStr(X(lRow, lCol), strDelim) > 0, """" & X(lRow, lCol) & """", X(lRow, lCol)))
Next lRow
'write each line to CSV
Print #lFnum, strTmp
Next lCol
Else
Print #lFnum, IIf(InStr(ws.UsedRange.Value, strDelim) > 0, """" & ws.UsedRange.Value & """", ws.UsedRange.Value)
End If
End If
Next ws
Close lFnum
MsgBox "Done!", vbOKOnly
End Sub

裁判