使用VB 2010快速将数据从文本文件粘贴到excel [英] Pasting data from text file to excel using VB 2010 express

问题描述

这听起来很简单，但我是vb编程中的新手。我有一个11行的文本文件，两列之间有39个空格。现在我正在读这个文件，复制它粘贴到Excel表格中。这是我迄今为止的代码：

This may sound very easy but I am a newbie in vb programming. I have a text file with 11 rows, 2 columns separated by 39 spaces between them. Now I'm trying to read this file, copy it & paste into an excel sheet. Here is the code that I have so far:

Imports System.IO

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim oExcel As Object
        Dim oBook As Object
        Dim oRow As Int16 = 0

        oExcel = CreateObject("Excel.Application")
        oBook = oExcel.Workbooks.Add

        'Read input .txt file line-by-line, Copy to Clipboard & Paste to Excel

        Using rdr As New System.IO.StreamReader("C:\Temp\ONI.txt")
            Do While rdr.Peek() >= 0
                Dim InputLine As String = rdr.ReadLine
                oRow = oRow + 1
                System.Windows.Forms.Clipboard.SetDataObject (InputLine)
                oBook.Worksheets(1).Range("A" + oRow.ToString).Select()
                oBook.Worksheets(1).Paste()
            Loop
            rdr.Close()
        End Using

        oExcel.Visible = True
        'oExcel.SaveAs("C\Temp\test.xls")
        oBook = Nothing
        oExcel.Quit()
        oExcel = Nothing
    End Sub

End Class

这是工作在一个卓越的工作簿开放的程度上数据在A1到A11中被粘贴，即行是11（可以），但是列只有1（它应该在A和B列中）。我知道这很简单，请指导我。

This is working to the extent that an excel workbook is opened & the data is getting pasted in A1 to A11 i.e the rows are 11 (which is ok) but the column is only 1 (it should be in columns A & B). I know this is very easy, please guide me.

此外，代码停在另存为行（我已经评论过）。运行时显示错误未找到类型ApplicationClass的公共成员SaveAs。

Also the code stops at the "Save as" line (which I have commented). When run it shows error Public member 'SaveAs' on type 'ApplicationClass' not found.

推荐答案

你应该做 oBook.SaveAs（...），你不保存 Excel.Application ，只是工作簿。

You should be doing oBook.SaveAs(...), you are not saving the Excel.Application, just the workbook.

为了导入文本，为什么不使用 Workbooks.OpenText 方法？通过一些调整，这应该允许您以所需的格式打开分隔的文件。这样做可以节省他在导入之后尝试做文本到列的麻烦，以及不得不使用剪贴板的麻烦麻烦。

For importing text, why not just use the Workbooks.OpenText method? With some tweaking this should allow you to open the delimited files in the desired format. This saves yout he hassle of trying to also do text-to-columns after importing it, and the nasty hassle of having to work with the clipboard.

例如，这打开Excel工作簿中的文本文件，将空格视为分隔符，连续分隔符为一个，因此应正确分割列。

E.g., this opens a text file in an Excel workbook, treats spaces as the delimiter, consecutive delimiters as one, so it should split your columns correctly.

Sub OpenTextFile()
Dim wbText As Workbook
Dim sFName As String ' file name & path'

sFName = Application.GetOpenFilename()
Workbooks.OpenText Filename:=sFName _
    , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False _
    , Comma:=False, Space:=True, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True

Exit Sub
End Sub

这是我使用的测试文件：

Here is the test file I used:

这里是输出，正确地在2列中：

And here is the output, properly in 2 columns:

这篇关于使用VB 2010快速将数据从文本文件粘贴到excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！