时间戳

首页 > 解决方案 > 从记事本复制并粘贴到 Excel 工作表的最后一行

excel - 从记事本复制并粘贴到 Excel 工作表的最后一行

问题描述

我能够将内容从记事本复制到 Excel。

我在将数据粘贴到最后填充的行下方时遇到问题。

下面是部分代码。它粘贴数据，但当它到达下一个文本文件时，它说对象不支持：ws.Paste Range("A1:A" & 1LastRow)

Option Explicit

Dim p
Dim ws As Worksheet
Dim MyPath As String
Dim strFilename As String
Dim lLastRow As Long
Dim LC As Long
Dim s As Worksheet, t As String
Dim i As Long, K As Long
K = Sheets.Count
Set ws = ThisWorkbook.Worksheets("First Sheet")
Sheets("First Sheet").Columns(1).NumberFormat = "@"
Sheets("First Sheet").Columns(2).NumberFormat = "@"
Sheets("First Sheet").Columns(3).NumberFormat = "@"

Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False

Set ws = ThisWorkbook.Worksheets("First Sheet")
lLastRow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row + 1
p = Shell("Notepad.exe " & myfile, vbNormalFocus)
waitTime (2000) 'as an alternative
AppActivate p, False
Application.SendKeys "^a", True 'sends select all command keys
waitTime (500)
Application.SendKeys "^c", True 'sends copy keys
waitTime (500)
ws.activate
ws.Paste Range("A1:A" & 1LastRow)

Loop

Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False
End Sub

Public Function waitTime(ms As Long)
    Application.Wait Now() + (ms / 24 / 60 / 60 / 1000)
End Function

共产国际建议的新代码

Sub ISINCompilerx2()
Dim handle As Integer
Dim wbDst As Workbook
Dim wsDst As Worksheet
Dim lastRow As Long
Dim MyPath As String
Dim strFilename As String
handle = FreeFile
Set wbDst = ThisWorkbook
Set wsDst = wbDst.Worksheets("First Sheet")
lastRow = wsDst.Cells(Rows.Count, "A").End(xlUp).Row + 1

MyPath = "W:\Product Platforms\ISIN- CUSIP Country of Corporation\August 2018\All Asset Classes\"
strFilename = Dir(MyPath, vbNormal)

Do While strFilename <> ""
    Dim buffer As String
    Open MyPath & strFilename For Input As #handle
    buffer = Input(LOF(handle), handle)  '<-- reads the entire contents of the file to "buffer"
    Close #handle

    With CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .SetText buffer
        .PutInClipboard
    End With

    wsDst.Activate
    Range("A1").Select
    Selection.Paste
    Application.CutCopyMode = False
    strFilename = Dir()
Loop

End Sub

标签： excelvba

解决方案

你错过了我上面评论的重点：

你知道记事本只是打开文本文件，对吧？您是否出于某种原因使用复制和粘贴而不是简单地打开文件并直接读取它？

当有可用且易于实施的本机解决方案时，您无需尝试“自动化”其他应用程序，您将为自己省去很多麻烦。我不是说使用Excel直接打开文本文件（或 CSV 文件），我的意思是使用 VBA 的内置功能直接从文件中读取。在记事本中打开一个文本文件以便将内容放到剪贴板SendKeys上有点荒谬，因为您可以轻松地将文本文件的全部内容放入String：

Dim handle As Integer
handle = FreeFile

Dim buffer As String
Open myfile For Input As #handle
buffer = Input(LOF(handle), handle)  '<-- reads the entire contents of the file to "buffer"
Close #handle

如果需要从这里处理，只需处理String. 如果您想将其粘贴到某处，您真正需要做的就是将其发送到剪贴板。如果您已经引用了“Microsoft Forms 2.0 Object Library”（例如，您UserForm在项目中有一个），您可以使用它：

With New MSForms.DataObject
    .SetText buffer
    .PutInClipboard
End With

或者，如果您没有参考，您可以使用此 CLSID 后期绑定它：

With CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    .SetText buffer
    .PutInClipboard
End With

一旦它在剪贴板上，您就可以将它粘贴到任何需要去的地方。

推荐阅读