如何从多个已关闭的Excel工作簿中提取数据，以便通过VBA在不同的工作表中放入单独的工作簿？-Java 学习之路

-1

（初学者VBA编码器在这里！）有谁知道如何从具有相同工作表格式的多个已关闭工作簿中提取多个特定单元格数据？

我目前的任务是从许多不同的和新的（但格式相同的）来源的某些单元格中复制非常具体的数据，并将它们转移到具有不同工作表的现有主列表中的另一组特定单元格中 .

这是我希望有用的代码，但与我需要的相比，它缺少太多方法......

Sub Importsheet() 
Dim Importsheet As Worksheet 
'import worksheet from a closed workbook
Sheets.Add Type:= _ 
'e.g. directory below
"C:\Users\Loli\Desktop\Testing1.xlsx" 
End Sub

这段代码可以帮助我从封闭源代码工作簿中获取 sheets ，但不能获得封闭源代码中特定放置的单元格 . 它也无法将数据粘贴到目标excel中不同工作表中的特定放置单元格中 .

1 回答

完全理解您的要求是非常困难的，因为有时您似乎想要复制范围而有时需要复制一个单元格，因此为了指出正确的方向我的答案仅显示如何打开并将相关的工作表复制到您的主工作簿，然后能够参考您想要的单元格/范围

（我会在你得到你的数据然后删除工作表，这样你的主人不会突然变得庞大）：

Sub ImportSheet() 
    Dim sImportFile As String, sFile As String 
    Dim sThisBk As Workbook 
    Dim vfilename As Variant 
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
    Set sThisBk = ActiveWorkbook 
    sImportFile = Application.GetOpenFilename( _ 
    FileFilter:="Microsoft Excel Workbooks, *.xls; *.xlsx", Title:="Open Workbook")  'open dialog to choose the file you want, you can change this to loop through a folder if they are all in there.
    If sImportFile = "False" Then 'check if a file was selected before importing
        MsgBox "No File Selected!" 
        Exit Sub 

    Else 
        vfilename = Split(sImportFile, "\") 
        sFile = vfilename(UBound(vfilename)) 
        Application.Workbooks.Open Filename:=sImportFile 'open the selected file

        Set wbBk = Workbooks(sFile) 
        With wbBk 
            If SheetExists("Raw_Data") Then ' you should change this to the date, you can do this easily by using a variable such as if SheetExists(variableDate) then, where variableDate = "12/12/2017" or something similar
                Set wsSht = .Sheets("Raw_Data") 
                wsSht.Copy before:=sThisBk.Sheets("Sheet1") 'copy the worksheet into your master
                'WsSht.range("A1:B2").copy Destination:=sThisBk.Sheets("Temp").Range("A1").paste xlpastevalues 'use this to copy a specified range in this case A1:B2 to a sheet in master workbook called Temp A1
            Else 
                MsgBox "There is no sheet with name :Raw_Data in:" & vbCr & .Name 
            End If 
            wbBk.Close SaveChanges:=False 
        End With 
    End If 
    Application.ScreenUpdating = True 
    Application.DisplayAlerts = True 
End Sub 

Private Function SheetExists(sWSName As String) As Boolean 
    Dim ws As Worksheet 
    On Error Resume Next 
    Set ws = Worksheets(sWSName) 
    If Not ws Is Nothing Then SheetExists = True 
End Function

回复于 2024-04-25T18:28:57+08:00

如何从多个已关闭的Excel工作簿中提取数据，以便通过VBA在不同的工作表中放入单独的工作簿？

1 回答

相关问题