从Excel数据中为列中的每个不同值创建CSV文件? [英] Create CSV file from Excel data for an each distinct value in a column?
本文介绍了从Excel数据中为列中的每个不同值创建CSV文件?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个带有供应商代码(NUMBERS)作为专栏之一的excel.
I have an excel with vendor codes(NUMBERS) as one of the columns.
VENDORITEM| DESCRIPTION |PRICE|PRICEGROUP|VENDOR NUMBER|PRODUCT CATEGORY
_______________________________
HNM36789 |30ML FLUID CLIN|50.00| B | 023445 |CMI
TNG78934 |BACK PAD 3X5" |32.00| D | 000905 |CMI
JPD12780 |FLEX DRILL GH |9.50 | R | 233590 |MISC
我需要创建一个excel vba宏,以便可以将每个供应商编号的数据导出到一个csv文件中,并为csv文件名提供类似于023445NEW的名称,并指定一个文件夹来保存所有csv文件?目前,我是手动执行此操作,并且花费了大量时间.
I need to create an excel vba macro so that I can export the data for each vendor number into a csv file and give the csv filename something like 023445NEW, and specify a folder to save all the csv files ? Currently, I doing this manually and taking lot of time.
推荐答案
此范围将转换为csv.
This convert range to csv.
Sub SaveRangeToCsvFiles()
Dim FileName As String
Dim Ws As Worksheet
Dim rngDB As Range
Dim r As Long, c As Long
Dim pathOut As String
Dim i As Long
pathOut = ThisWorkbook.Path & "\" ' set your path: C:\temp\
Set Ws = ActiveSheet 'Sheets("AllData")
With Ws
r = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'c = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
For i = 2 To r
Set rngDB = .Range("a" & i).Resize(1, 6)
FileName = .Range("a" & i).Offset(, 4) & "NEW"
TransToCSV pathOut & FileName & ".csv", rngDB
Next i
End With
MsgBox ("Files Saved Successfully")
End Sub
Sub TransToCSV(myfile As String, rng As Range)
Dim vDB, vR() As String, vTxt()
Dim i As Long, n As Long, j As Integer
Dim objStream
Dim strTxt As String
Set objStream = CreateObject("ADODB.Stream")
vDB = rng
For i = 1 To UBound(vDB, 1)
n = n + 1
ReDim vR(1 To UBound(vDB, 2))
For j = 1 To UBound(vDB, 2)
vR(j) = vDB(i, j)
Next j
ReDim Preserve vTxt(1 To n)
vTxt(n) = Join(vR, ",")
Next i
strTxt = Join(vTxt, vbCrLf)
With objStream
'.Charset = "utf-8"
.Open
.WriteText strTxt
.SaveToFile myfile, 2
.Close
End With
Set objStream = Nothing
End Sub
这篇关于从Excel数据中为列中的每个不同值创建CSV文件?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文