使用VBA将行从写字板复制到Excel [英] Copying lines from Wordpad into Excel using VBA

问题描述

我正在编写一些代码，以便在TMX(xml的一种形式)下导入一些文件.我尝试了各种选择

I am writing some code where I import some files under TMX (a form of xml). I tried various options

a)使用打开文件名"作为输入，但这会弄乱字符编码

a) using the Open FileName For input, but this messes up the character encoding

b)打开文件并使用msoDialog复制数据，但是如果文件太大(通常是这种情况)，则返回错误，并且将数据完全弄乱了.

b) opening the file and copying the data using the msoDialog, but this return an error if the file is too large (which is often the case) and this put the data in an utterly messy manner.

c)使用记事本打开文件，但是在将整个文件复制到Excel中作为前一个选项方面，存在相同的限制.我不尝试使用调用Wordpad的shell函数.

c) opening the file using notepad, but there are the same limitations in so far as copying the entirety of the file into Excel as the previous option. I am not trying to use a shell function calling onto Wordpad.

我现在的问题是，我需要逐行复制文件以根据需要处理文件的内容(希望不会丢失字符编码

My issue right now, is that I need to copy the file line by line to treat its content according to my needs (hopefully without losing the character encoding

有人会知道如何从写字板中打开的文件中复制每一行并将其粘贴后(选择相关元素)粘贴到Excel中吗?

Would someone know how to copy every single line from the file opened in WordPad and paste it post treatment (selection of the relevant elements) into Excel?

谢谢

推荐答案

对于大文件，您可以使用以下解决方案:

For large files you can use this solution:

Public Sub ImportTMXtoExcel()

Call Application.FileDialog(msoFileDialogOpen).Filters.Clear
Call Application.FileDialog(msoFileDialogOpen).Filters.Add("TMX Files", "*.tmx")
Application.FileDialog(msoFileDialogOpen).Title = "Select a file to import..."
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
intChoice = Application.FileDialog(msoFileDialogOpen).Show
If intChoice <> 0 Then
    strFileToImport = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
Else
    Exit Sub
End If

intPointer = FreeFile()
Open strFileToImport For Input Access Read Lock Read As #intPointer

intCounter = 0
Do Until EOF(intPointer)
    Line Input #intPointer, strLine
    intCounter = intCounter + 1
    Worksheets(1).Cells(intCounter + 1, 1).Value2 = strLine
Loop

Close intPointer

End Sub

对于其他编码，您可以按照此解决方案中的说明使用ADO的Stream: VB6/VBScript将文件编码更改为ansi

For other encodings you can use ADO's Stream as described in this solution: VB6/VBScript change file encoding to ansi

如果您有需要ADO Stream的大文件，那么您可能要考虑按照此解决方案中的说明先分解大文件:如何将大文本文件拆分为行数相等的小文件?

If you have large files which require ADO's Stream then you might want to consider breaking down the large files first as described in this solution: How to split a large text file into smaller files with equal number of lines?

以下网站提供了一个工具，该工具可以在命令提示符下模拟Windows的Unix命令拆分: https://www.fourmilab.ch/splits/

The following website provides a tool which mimics the Unix command split for Windows in command prompt: https://www.fourmilab.ch/splits/

这篇关于使用VBA将行从写字板复制到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！