时间戳

首页 > 解决方案 > 将单元格值从另一个工作簿导入工作表

excel - 将单元格值从另一个工作簿导入工作表

问题描述

我不断收到错误消息Set MainRng = Workbooks(mainfile) It is an object not defined 错误，这适用于我的其他电子表格，但不适用于新电子表格。

Sub LoadData(mainfile As String, srcfile As String)
    Dim DS As Worksheet
    Dim Cell As Range
    Dim CurrentRow As Integer, ValPos As Integer
    Dim AsFoundLoc As String, AsLeftLoc As String, ValTextLoc As String
    Dim SheetName As String, ValDataText As String, FileValidation As String
    Dim ImportData As Variant, Multiplier As Variant, AutomationType As String
    Dim AsLeftData As Variant
    Dim VerifySheetName As String

    Workbooks(srcfile).Activate
    AutomationType = Workbooks(mainfile).Worksheets("Import Map").Range("B5").Value
    SheetName = Workbooks(mainfile).Worksheets("Import Map").Range("B7").Value
    ValDataText = Workbooks(mainfile).Worksheets("Import Map").Range("A10").Value
    ValTextLoc = Workbooks(mainfile).Worksheets("Import Map").Range("B10").Value

    'Set ValPos to 0
    ValPos = 0
    AsLeftData = vbNo

    'Set the Verify Sheet Name
    VerifySheetName = SheetName


    'Change Verify Sheet Name for SureCal
    'If SureCal Ask if this is As Left Data
    If AutomationType = "SureCal" Then
        VerifySheetName = "Cover Sheet"
        AsLeftData = MsgBox("NOTE: For SureCal the you will need to Import Data for both" & Chr(13) & "the As Found and As Left Data Seperately if required" _
            & Chr(13) & Chr(13) & "Are you Importing the Left Data now?", vbYesNo)
    End If


    'Check to see if a validation text is used
    If ValDataText <> "" And ValTextLoc <> "" Then
        FileValidation = Workbooks(srcfile).Worksheets(VerifySheetName).Range(ValTextLoc).Value

        ValPos = InStr(1, FileValidation, ValDataText, vbTextCompare)
    Else
        ValPos = 1
    End If


    'Proceed if File Text Validated
    If ValPos <> 0 Then

        Application.StatusBar = "Importing Data..."

        Set MainRng = Workbooks(mainfile).Worksheets("Import Map").Range("A" & DS_StartRow & ":A" & DS_LastRow)
        Workbooks(mainfile).Activate


        For Each Cell In MainRng

            CurrentRow = Cell.Row
            SheetName = Workbooks(mainfile).Worksheets("Import Map").Range("B7").Value
            AsFoundLoc = Workbooks(mainfile).Worksheets("Import Map").Range("C" & CurrentRow).Value
            AsLeftLoc = Workbooks(mainfile).Worksheets("Import Map").Range("D" & CurrentRow).Value
            Multiplier = Workbooks(mainfile).Worksheets("Import Map").Range("E" & CurrentRow).Value
            ImportData = ""

            'Now add the AsFound data
            If AsFoundLoc <> "" Then

                ImportData = Workbooks(srcfile).Worksheets(SheetName).Range(AsFoundLoc).Value

                'Call the Correct Automation Type to Format Data input
                If AutomationType = "SureCal" Then ImportData = SureCalData(ImportData)
                If AutomationType = "NI" Then ImportData = NIData(ImportData)


                'First line of code moves data to datasheet, 2nd line of code adds it to the Repeatability column
                If Not IsEmpty(ImportData) Then
                    If IsNumeric(ImportData) Or LCase(ImportData) = "pass" Or LCase(ImportData) = "fail" Then
                        If IsNumeric(ImportData) Then
                            ImportData = ImportData * Multiplier
                        End If

                        If AsLeftData = vbNo Then
                            Workbooks(mainfile).Worksheets("Datasheet").Range("I" & CurrentRow).Value = ImportData
                            Workbooks(mainfile).Worksheets("Import Map").Range("F" & CurrentRow).Value = ImportData
                        Else
                            Workbooks(mainfile).Worksheets("Datasheet").Range("J" & CurrentRow).Value = ImportData
                        End If

                    End If
                End If
            End If

            'Now add the AsLeft data
            'Note: As Left is skipped for SureCal Imports
            If AutomationType <> "SureCal" Then

                If AsLeftLoc <> "" Then

                    ImportData = ""
                    ImportData = Workbooks(srcfile).Worksheets(SheetName).Range(AsLeftLoc).Value

                    'Call the Correct Automation Type to Format Data input - Note: SureCal Does not get Called
                    'If AutomationType = "SureCal" Then ImportData = SureCalData(ImportData)
                    If AutomationType = "NI" Then ImportData = NIData(ImportData)


                    If Not IsEmpty(ImportData) Then

                        If IsNumeric(ImportData) Or LCase(ImportData) = "pass" Or LCase(ImportData) = "fail" Then
                            If IsNumeric(ImportData) Then
                                ImportData = ImportData * Multiplier
                            End If

                            Workbooks(mainfile).Worksheets("Datasheet").Range("J" & CurrentRow).Value = ImportData
                        End If
                    End If
                End If
            End If

        Next Cell
        'Determine Starting of Data in each the main and the source
        'Workbooks(srcfile).Activate
        'Workbooks(mainfile).Activate

    Else
        MsgBox "Validation Text ( " & ValDataText & " ) Was not Found in the " & VerifySheetName _
        & " at Cell " & ValTextLoc & Chr(13) & Chr(13) & "No Data was Imported"


    End If
End Sub

标签： excelvba

解决方案

此行上的错误 1004：

Set MainRng = Workbooks(mainfile).Worksheets("Import Map").Range("A" & DS_StartRow & ":A" & DS_LastRow)

Range表示为调用提供的参数有问题（错误的参数Workbooks或 Worksheets将引发错误 9 /“下标超出范围”）。

.Range("A" & DS_StartRow & ":A" & DS_LastRow)

在此指令运行之前，您发布的代码中的任何地方都没有声明或分配DS_StartRow变量。DS_LastRow

如果没有Option Explicit并假设它们不是在其他地方定义的全局变量，看起来可以安全地假设它们的值为0.

.Range("A0:A0")

...是非法的，因为工作表行地址是从 1 开始的。因此，抛出错误 1004。

缩小问题范围的一种方法是将执行太多事情的指令拆分为执行一件事的较小语句：

Dim wb As Workbook
Set wb = Workbooks(mainfile)

Dim ws As Worksheet
Set ws = wb.Worksheets("Import Map")

Dim map As Range
Set map = ws.Range("A" & DS_StartRow & ":A" & DS_LastRow)

现在更容易准确地看到哪条指令失败了。

推荐阅读