vba 将数据从 Excel 复制到记事本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42224880/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copy Data from Excel to Notepad
提问by Robin Mackenzie
I copy data from Excel to Notepad using the SendKeysExcel VBA function.
我使用SendKeysExcel VBA 函数将数据从 Excel 复制到记事本。
I'm looking to avoid using the sendkeys.
我希望避免使用发送键。
I have this code:
我有这个代码：
sub test()
dim wb as Workbook
set wb = "C:\Documents\test.xlsx"
wb.Sheets(2).Range("C2:C" & lRow).Copy
myApp = Shell("Notepad.exe", vbNormalFocus)
SendKeys "^v"
Application.CutCopyMode = False
wb.Sheets(2).Range("C2:C" & lRow).NumberFormat = "@"
end sub
This only copies the data from Excel to Notepad, but after doing some corrections in the Excel file, I want the data in Notepad to be copied to Excel starting from C2.
这只是将数据从 Excel 复制到记事本，但在对 Excel 文件进行一些更正后，我希望将记事本中的数据从 C2 开始复制到 Excel。
回答by Robin Mackenzie
This is an alternative process to SendKeys:
这是一个替代过程SendKeys：
gets values from a range of cells on a worksheet
copies to clipboard
gets the clipboard content into a string
saves that string to a temp file
opens Notepad.exe with the content of the temp file
从工作表上的一系列单元格中获取值
复制到剪贴板
获取剪贴板内容到一个字符串
将该字符串保存到临时文件
使用临时文件的内容打开 Notepad.exe
Code:
代码：
Option Explicit
Sub OpenNotepadWithTempFileWithClipboardContent()
Dim rngData As Range
Dim strData As String
Dim strTempFile As String
' copy some range values
Set rngData = Sheet3.Range("B1:B5")
rngData.Copy
' get the clipboard data
' magic code for is for early binding to MSForms.DataObject
With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
.GetFromClipBoard
strData = .GetText
End With
' write to temp file
strTempFile = "D:\temp.txt"
With CreateObject("Scripting.FileSystemObject")
' true to overwrite existing temp file
.CreateTextFile(strTempFile, True).Write strData
End With
' open notepad with tempfile
Shell "cmd /c ""notepad.exe """ & strTempFile & """", vbHide
End Sub
回答by P??
You can use the file system object to write into a text file:
您可以使用文件系统对象写入文本文件：
Dim fso as Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim oFile as Object
Set oFile = FSO.CreateTextFile(strPath)
oFile.WriteLine "test"
oFile.Close
Set fso = Nothing
Set oFile = Nothing
For more info see here: https://technet.microsoft.com/en-us/library/ee198716.aspx
有关更多信息，请参见此处：https: //technet.microsoft.com/en-us/library/ee198716.aspx
回答by Slai
A bit too late, but you can copy the data to the clipboard and paste it as text (tested and works):
有点晚了，但您可以将数据复制到剪贴板并将其粘贴为文本（已测试并有效）：
Dim r As Range
Set r = wb.Sheets(2).Range("C2:C" & lRow)
r.Copy
With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
.GetFromClipboard
Application.CutCopyMode = False
.PutInClipboard
End With
r.Select
r.NumberFormat = "@"
r.Worksheet.PasteSpecial "Text"
回答by JoshInDC
Try this:
尝试这个：
Sub SimpleVBAWriteToFileWithoutQuotes()
Open "c:\TEMP\Excel\out.txt" For Output As #1
Print #1, Application.ActiveSheet.Cells(2, 3)
Close #1
End Sub
回答by Vityata
Here is probably something that would work quite ok:
这可能是一些可以正常工作的东西：
- Copy this to a different sheet:
wb.Sheets(2).Range("C2:C" & lRow).Copy - Save this sheet as a
*.csvfile: Saving excel worksheet to CSV files with filename+worksheet name using VB - Then the *.csv file is a Notepad file. :)
- 将此复制到不同的工作表：
wb.Sheets(2).Range("C2:C" & lRow).Copy - 将此工作表另存为
*.csv文件：使用 VB 将 Excel 工作表保存为带有文件名+工作表名称的 CSV 文件 - 那么*.csv 文件就是一个记事本文件。:)
回答by Jayant Kumar jain
'This macros may solve your problem
Sub SaveMySheetAsTextFile()
Sheets("Sheet1").Select
ActiveWorkbook.SaveAs Filename:="C:\mynotepadfile.txt", FileFormat:=xlText
End Sub
