A workbook from Input box to place in table on worksheet

[DThib]

I have a macro that will ask for a date to append 2 workbook names and copy their data into the current workbook.

I would like to add the names (File1 and File2) to a table on a worksheet in the current workbook.

So this table will grow as the workbook data is added incrementally.
I am having trouble adding to next row or adding the names at all.

What I have to this point is below.
File1 and File2 are both variable names. AddTable is the table.

Sub NewData_Click()


   Dim Message, Title, Default, MyValue
   Dim LRa1 As Variable
   Dim File1, File2 As String
   Dim QArow As Variant
   
   LRa1 = Sheets("QA_Data").Range("AddTable[#All]").Select
           
    Message = "Add dates to Filernames"
    Title = "Data Draw"
    Default = "month DD"
    MyValue = inputbox(Message, Title, Default)
    
     Fudge = MsgBox("Files to be pulled:" & vbCrLf & "MB51 " & MyValue & ".xlsx" _
       & vbCrLf & "COOIS " & MyValue & ".xlsx", vbInformation + vbYesNo, "Filenames")
    
    If Fudge = vbYes Then
      File1 = "MB51 " & MyValue & ".xlsx"
      File2 = "COOIS " & MyValue & ".xlsx"


        If File1 = "MB51 month DD.xlsx" And File2 = "COOIS month DD.xlsx" Then
          MsgBox "You for got to add date of data pull for the Files, please revise", _
             vbCritical + vbOKOnly, "Wrong Information"
             Exit Sub
        End If
           Filer1 = VBA.FileSystem.Dir("\\server\folder" & File1)
             If Filer1 = VBA.Constants.vbNullString Then
               MsgBox "There is no corresponding File, check name of workbook and try again" & vbCrLf & File1, _
                  vbInformation + vbOKOnly, "Missing File"
                      Exit Sub
             Else
                Workbooks.Open "\\server\folder" & File1
                Call MB51_Thing
             End If
                    
           Filer2 = VBA.FileSystem.Dir("\\server\folder" & File2)


             If Filer2 = VBA.Constants.vbNullString Then
               MsgBox "There is no corresponding File, check name of workbook and try again" & vbCrLf & File2, _
                  vbInformation + vbOKOnly, "Missing File"
                      Exit Sub
             Else
                Workbooks.Open "\\server\folder" & File2
                Call COOIS_Thing
             End If
       For Each QArow In LRa1
        If Not File1 = Sheets("QA_Data").Cells(LRa1, 1) Is Nothing Then
           Sheets("QA_Data").Cells(LRa1, 1) = File1
        End If
        If Not File2 = Sheets("QA_Data").Cells(LRa1, 2) Is Nothing Then
            Sheets("QA_Data").Cells(LRa1, 2) = File2
        End If
       Next QArow
    ElseIf Fudge = vbNo Then
             Exit Sub
    End If
      
End Sub

Ideas?

DThib

 

[DThib]

I fixed one part, but I need to get it to add next entry in new row of the table. I cannot get it to work right now.

Here is the first fix:

Sub NewData_Click()


   Dim Message, Title, Default, MyValue
   Dim File1, File2 As String
           
    Message = "Add dates to Filernames"
    Title = "Data Draw"
    Default = "month DD"
    MyValue = inputbox(Message, Title, Default)
    
     Fudge = MsgBox("Files to be pulled:" & vbCrLf & "MB51 " & MyValue & ".xlsx" _
       & vbCrLf & "COOIS " & MyValue & ".xlsx", vbInformation + vbYesNo, "Filenames")
    
    If Fudge = vbYes Then
      File1 = "MB51 " & MyValue & ".xlsx"
      File2 = "COOIS " & MyValue & ".xlsx"


        If File1 = "MB51 month DD.xlsx" And File2 = "COOIS month DD.xlsx" Then
          MsgBox "You for got to add date of data pull for the Files, please revise", _
             vbCritical + vbOKOnly, "Wrong Information"
             Exit Sub
        End If
        
         With Worksheets("QA_Data")
          .Range("AddTable[MB51 Files Called]") = File1
          .Range("AddTable[COOIS Files Called]") = File2
         End With
           Filer1 = VBA.FileSystem.Dir("\\server\folder\" & File1)
             If Filer1 = VBA.Constants.vbNullString Then
               MsgBox "There is no corresponding File, check name of workbook and try again" & vbCrLf & File1, _
                  vbInformation + vbOKOnly, "Missing File"
                      Exit Sub
             Else
                Workbooks.Open "\\server\folder\" & File1
                Call MB51_Thing
             End If
                    
           Filer2 = VBA.FileSystem.Dir("\\server\folder\" & File2)
             If Filer2 = VBA.Constants.vbNullString Then
               MsgBox "There is no corresponding File, check name of workbook and try again" & vbCrLf & File2, _
                  vbInformation + vbOKOnly, "Missing File"
                      Exit Sub
             Else
                Workbooks.Open "\\server\folder\" & File2
                Call COOIS_Thing
             End If


    ElseIf Fudge = vbNo Then
             Exit Sub
    End If
      
End Sub

DThib

 

[DThib]

Solved it!

DThib