文章/答案/技术大牛

发布

社区首页 >问答首页 >如何将数据从csv文件复制到其他excel文件

问如何将数据从csv文件复制到其他excel文件
EN

Stack Overflow用户

提问于 2018-09-28 01:18:19

回答 2查看 4.9K关注 0票数 1

由于我是VBA新手，所以我创建了一个代码，它可以打开.csv文件并将数据从.csv复制到excel文件，而无需同时打开两者。

实际上，它适用于excel文件，但是当我使用.csv文件时，它会显示一个错误消息“下标超出范围”.How，我能解决这个问题吗？谢谢!

    Sub Copywb1()
    Dim wkb1 As Workbook
    Dim sht1 As Worksheet
    Dim wkb2 As Workbook
    Dim sht2 As Worksheet

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

Setwkb1 = ThisWorkbook
Setwkb2 = Workbooks.Open("C:\Desktop\AAA.xlsx")
Setwkb1 = Workbooks.Open("C\Reports\BBB.csv")
Setsht1 = wkb1.Sheets("Reports")
Setsht2 = wkb2.Sheets("Fees")

sht1.Range("A1:BM9").Copy
sht2.Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False
wkb2.Close True
wkb1.Close True

  End Sub

vba

excel

回答 2

Stack Overflow用户

回答已采纳

发布于 2018-09-28 02:16:19

下面是我的一个工作示例，您应该能够相当容易地适应您的需求：

Sub demo_loadDataFromCSV()
    Const csvFile = "x:\mypath\myfile.csv"
    Dim ws As Worksheet, csv As Workbook, cCount As Long, cName As String

    ' Application.ScreenUpdating = False       'keep these commented-out until...
    ' Application.DisplayAlerts = False        ' ...done testing/troubleshooting!

    Set ws = ThisWorkbook.ActiveSheet          'remember where we parked
    Workbooks.Open csvFile                     'open the csv
    Set csv = ActiveWorkbook                   'create object of csv workbook
    cName = csv.Name                           'get name of csv while its open
    ActiveSheet.Columns("A:B").Copy            'copy columns A and B
    ws.Activate                                'go back to the first sheet
    ws.Range("A1").PasteSpecial xlPasteValues  'paste values
    cCount = Selection.Cells.Count             'count pasted cells
    csv.Close                                  'close CSV

    Application.DisplayAlerts = True           're-enable alerts
    Application.ScreenUpdating = True          'resume screen updates

    MsgBox cCount & " cells were copied from " & cName _
                  & " to " & ws.Parent.Name, vbInformation, "Done"
End Sub

更多资料：

MS： (Excel)
MS： (Excel)
首席财务官：Referring to Other Worksheets or Workbooks in Excel VBA
代码VBA：variable

票数 2

Stack Overflow用户

发布于 2022-04-11 07:28:19

下面是代码中的一些小改动，现在它将选择新的工作簿并将数据粘贴到选定的工作表中。

Sub demo_loadDataFromCSV()
Const csvFile = "C:\Users\PC\Downloads\R1C2.txt"
Dim ws As Worksheet, csv As Workbook, cCount As Long, cName As String
Dim ws2 As Worksheet

' Application.ScreenUpdating = False       'keep these commented-out until...
' Application.DisplayAlerts = False        ' ...done testing/troubleshooting!

Set ws = ThisWorkbook.ActiveSheet          'remember where we parked
Workbooks.Open csvFile                     'open the csv
Set csv = ActiveWorkbook                   'create object of csv workbook

'to open new workbook
Filename = Application.GetOpenFilename(, , "Browse for workbook")

cName = csv.Name                           'get name of csv while its open
ActiveSheet.Columns("A:B").Copy            'copy columns A and B

'Open workbook
Workbooks.Open Filename
'Go to sheets Fees
Set test = ActiveWorkbook.Sheets("Fees")
test.Activate
test.Range("A1").PasteSpecial xlPasteValues  'paste values
cCount = Selection.Cells.Count             'count pasted cells
csv.Close                                  'close CSV

Application.DisplayAlerts = True           're-enable alerts
Application.ScreenUpdating = True          'resume screen updates

MsgBox cCount & " cells were copied from " & cName _
              & " to " & ws.Parent.Name, vbInformation, "Done"
End Sub

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/52547016

复制

相似问题

问如何将数据从csv文件复制到其他excel文件
EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将数据从csv文件复制到其他excel文件EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将数据从csv文件复制到其他excel文件
EN