如何自动从Excel文件中复制文本 [英] How to automatically copy text out of excel files

问题描述

我在一个文件夹中有250个Microsoft Excel(.xls)文件.我需要执行以下操作:

I have 250 Microsoft Excel (.xls) files, all in a folder. I need to do the following:

for each file:
    open the file
    switch to a specific tab in the file
    extract the text from rows 15-100 on that tab
    save the text in a text file somewhere

我认为这可以通过某种方式实现自动化，但是我不知道如何实现.我从哪里开始寻找解决方法?我真的不想打开250个excel文件并用手复制文本，因为那样可能要花几个小时.:(

I assume this can be automated somehow, but I have no idea how. Where do I start looking to figure out how to do this? I really don't want to open 250 excel files and copy text out by hand, as that would take hours. :(

推荐答案

由于您已经拥有Excel，因此可以在单独的工作表中创建Excel宏来执行此操作；只需确保工作表在您要解析的目录之外即可.您需要添加FileSystemObject的引用，该引用应该在C:\ Windows \ System32 \ scrrun.dll中找到.

Since you already have Excel, you can create an Excel macro in a separate worksheet to do this; just make sure the worksheet is outside of the directory you are parsing. You'll need to add a reference for the FileSystemObject, which should be found in C:\Windows\System32\scrrun.dll.

Option Explicit

Sub ExtractData()
    Dim fso As New FileSystemObject
    Dim oFile As File
    Dim oFolder As Folder

    Dim sFileOutput As String
    Dim fNum

    Dim excelFile As Excel.Workbook
    Dim excelWorksheet As Excel.Worksheet
    Dim i As Integer

    sFileOutput = "C:\FolderToScan\ExcelOutput.txt"

    Set oFolder = fso.GetFolder("C:\FolderToScan")

    For Each oFile In oFolder.Files
        If Right(oFile.Name, 3) = "xls" Then
            fNum = FreeFile()
            Open sFileOutput For Append As fNum

            Set excelFile = Workbooks.Open(oFile.Path)

            Set excelWorksheet = excelFile.Sheets(1)
            'Or:
            ' Set excelWorksheet = excelFile.Sheets("Name of your sheet")

            For i = 15 To 100
                Write #fNum, excelWorksheet.Cells(i, 1)
            Next
            Close #fNum

            excelFile.Close
            Set excelFile = Nothing
        End If
    Next
End Sub

这篇关于如何自动从Excel文件中复制文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！