把多个CSV文件加载到一个EXCEL文件
来源:互联网 发布:电子数据保全行业公约 编辑:程序博客网 时间:2024/05/01 15:36
Option Explicit
Const path1 = "C: emp"
Const path2 = "C: empCsvToExcel"
Const sPattern = "*.csv"
Private Sub Form_Load()
On Error GoTo err
Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlSheet1 As Excel.Worksheet
Dim excelname As String
excelname = Replace(DateTime.Now, ":", "-") & ".xls"
'Write in log file
Dim logtxtfile As String
Dim excelfilename() As String
excelfilename = Split(excelname, ".")
logtxtfile = excelfilename(0)
' Dim sw As System.IO.StreamWriter = New StreamWriter(path2 & logtxtfile & ".txt")
Set xlBook = xlApp.Workbooks.Add
'get all *.csv files
Dim csvPath As String
Dim csvPaths(1 To 30) As String
Dim xlsPath As String
Dim csvfilename As String
Dim csvfiles() As String
File1.Path = path1
File1.Pattern = sPattern
Dim i As Integer
For i = 0 To File1.ListCount - 1
csvfilename = File1.List(i)
csvPath = path1 & "" & csvfilename
'csvPaths(i + 1) = csvPath
Set xlSheet = xlBook.Worksheets.Add()
xlSheet.Name = csvfilename
'xlApp.ActiveSheet
Dim linesFromFile() As String
Dim NextLine As String
Dim dataStart, mapWidth As Integer
Open csvPath For Input As #1
'Open path2 + excelname For Output Access Write As #55
Dim rowCount As Long
rowCount = 0
Do While Not EOF(1)
Line Input #1, NextLine
linesFromFile() = Split(NextLine, ",")
'Dim counter As Integer
Dim WriteStrArrayI As Integer
Dim lenLines As Integer
lenLines = UBound(linesFromFile)
rowCount = rowCount + 1
For WriteStrArrayI = 0 To lenLines
xlApp.Cells(rowCount, WriteStrArrayI + 1) = linesFromFile(WriteStrArrayI)
Next
'Print #55, linesFromFile
Loop
'Set xlSheet.Range(xlSheet.Cells(1, 1), xlSheet.Cells(1, WriteStrArrayI)).Font.Bold = True '设置表头字体
'xlSheet.Range(xlSheet.Cells(1, 1), xlSheet.Cells(1, WriteStrArrayI)).Interior.Color = 77777700 '设置表头颜色
Close #1
Next
Label1.Caption = csvPath
Set xlSheet1 = xlBook.Worksheets("sheet1")
xlSheet1.Delete
Set xlSheet1 = xlBook.Worksheets("sheet2")
xlSheet1.Delete
Set xlSheet1 = xlBook.Worksheets("sheet3")
xlSheet1.Delete
Dim savePath As String
savePath = path2 & excelname
xlApp.ActiveWorkbook.SaveAs (savePath)
xlApp.Quit
err:
If err.Description <> "" Then
savePath = path2 & excelname
xlApp.ActiveWorkbook.SaveAs (savePath)
xlApp.Quit
End If
End Sub
Const path1 = "C: emp"
Const path2 = "C: empCsvToExcel"
Const sPattern = "*.csv"
Private Sub Form_Load()
On Error GoTo err
Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlSheet1 As Excel.Worksheet
Dim excelname As String
excelname = Replace(DateTime.Now, ":", "-") & ".xls"
'Write in log file
Dim logtxtfile As String
Dim excelfilename() As String
excelfilename = Split(excelname, ".")
logtxtfile = excelfilename(0)
' Dim sw As System.IO.StreamWriter = New StreamWriter(path2 & logtxtfile & ".txt")
Set xlBook = xlApp.Workbooks.Add
'get all *.csv files
Dim csvPath As String
Dim csvPaths(1 To 30) As String
Dim xlsPath As String
Dim csvfilename As String
Dim csvfiles() As String
File1.Path = path1
File1.Pattern = sPattern
Dim i As Integer
For i = 0 To File1.ListCount - 1
csvfilename = File1.List(i)
csvPath = path1 & "" & csvfilename
'csvPaths(i + 1) = csvPath
Set xlSheet = xlBook.Worksheets.Add()
xlSheet.Name = csvfilename
'xlApp.ActiveSheet
Dim linesFromFile() As String
Dim NextLine As String
Dim dataStart, mapWidth As Integer
Open csvPath For Input As #1
'Open path2 + excelname For Output Access Write As #55
Dim rowCount As Long
rowCount = 0
Do While Not EOF(1)
Line Input #1, NextLine
linesFromFile() = Split(NextLine, ",")
'Dim counter As Integer
Dim WriteStrArrayI As Integer
Dim lenLines As Integer
lenLines = UBound(linesFromFile)
rowCount = rowCount + 1
For WriteStrArrayI = 0 To lenLines
xlApp.Cells(rowCount, WriteStrArrayI + 1) = linesFromFile(WriteStrArrayI)
Next
'Print #55, linesFromFile
Loop
'Set xlSheet.Range(xlSheet.Cells(1, 1), xlSheet.Cells(1, WriteStrArrayI)).Font.Bold = True '设置表头字体
'xlSheet.Range(xlSheet.Cells(1, 1), xlSheet.Cells(1, WriteStrArrayI)).Interior.Color = 77777700 '设置表头颜色
Close #1
Next
Label1.Caption = csvPath
Set xlSheet1 = xlBook.Worksheets("sheet1")
xlSheet1.Delete
Set xlSheet1 = xlBook.Worksheets("sheet2")
xlSheet1.Delete
Set xlSheet1 = xlBook.Worksheets("sheet3")
xlSheet1.Delete
Dim savePath As String
savePath = path2 & excelname
xlApp.ActiveWorkbook.SaveAs (savePath)
xlApp.Quit
err:
If err.Description <> "" Then
savePath = path2 & excelname
xlApp.ActiveWorkbook.SaveAs (savePath)
xlApp.Quit
End If
End Sub
- 把多个CSV文件加载到一个EXCEL文件
- 把多个excel文件的sheet1数据合并到一个excel文件的sheet1中
- 用VB把多个excel文件的数据顺序拷到一个excel中
- excel文件的多个sheet表合并成一个csv文件并导入数据库
- 用VBA实现把多个Excel文件合并到一个Excel文件的多个工作表(Sheet)里
- 多个Excel文件中的多个Sheet合并到一个Excel文件中两个函数
- golang语言实现读取csv文件内容,把相同的内容提取到另外一个文件
- 使用VBA合并多个EXCEL文件到一个EXCEL文件
- 从CSV文件转换为Excel的多个Sheet
- 在mysql中,多个CSV文件导入到一个表的方法
- 把DATASET里的多个表导出到一个excel文件?
- 将多个csv文件合并到一个excel文件的不同的sheet中
- 如何把多个文件写入到一个文件中去并能分解
- 如何把多个文件写入到一个文件中去并能分解
- 如何把多个文件写入到一个文件中去并能分解
- 如何把多个文件写入到一个文件中去并能分解
- 流合并(把多个文件里面的内容写到一个文件里面)
- Excel 已经检测到"test.csv"是SYLK文件,但是不能将其加载
- MySQL是否值得我们选择的正反五个理由
- Silverlight微软研讨会范例
- 明天就系分考试了
- rsloads
- 给VC添加注释块的功能
- 把多个CSV文件加载到一个EXCEL文件
- 教你解决微软MSN8.5无法安装问题
- 工作效率和薪水加倍的7个秘诀
- 八月半·鬼
- orz是什么意思?
- 一位软件工程师的6年总结
- 勇敢的面对比什么都重要!
- RemObject SDK在d2007中的安装成功。
- 中兴、华为——五年计划