将csv导入excel的vbscript [英] Vbscript to import csv into excel

问题描述

我想制作一个 VBSript，它将打开一个包含不同行和列的 csv，并从第 4 行第 1 列开始粘贴它们.到目前为止，我写的内容很慢，并且有很多列硬编码到其中.

I would like to make a VBSript that will open a csv of varying rows and columns and paste them starting at row 4 column 1. So far what I have written is slow and has the amount of columns hard coded into it.

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(wscript.Arguments(0))
objExcel.Visible = True
objExcel.Cells(1, 4).Value = wscript.Arguments(1)+" - "+wscript.Arguments(2)
objExcel.Cells(2, 4).Value = wscript.Arguments(3)

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile (wscript.Arguments(4))
cCount = 1
rCount = 4

Do While objTextFile.AtEndOfStream <> True
        arrUserRecord = split(objTextFile.Readline, ",")
        Do While cCount<213
            objExcel.Cells(rCount, cCount).Value = arrUserRecord(cCount-1)
            cCount=cCount+1
        Loop
        cCount=1
        rCount=1+rCount
Loop

推荐答案

Excel 对它所接受的有效 CSV"可能相当挑剔.我不得不多次求助于以下内容:

Excel can be rather particular about what it accepts as "valid CSV". I had to resort to the following on several occasions:

Const vbFormatStandard = 1
Const vbFormatText     = 2
Const vbFormatDate     = 4

Const xlDelimited      = 1
Const xlDoubleQuote    = 1

' change according to number/type of the fields in your CSV
dataTypes = Array( Array(1, vbFormatText) _
  , Array(2, vbFormatStandard) _
  , Array(3, vbFormatText) _
  , Array(4, vbFormatDate) _
  )

Set xl = CreateObject("Excel.Application")
xl.Visible = True

xl.Workbooks.OpenText "input.csv", , , xlDelimited, xlDoubleQuote, False _
  , False, True, , , , dataTypes
Set wb = xl.ActiveWorkbook

这篇关于将csv导入excel的vbscript的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！