Results 1 to 3 of 3

VBA Formula Prompts me again to open a workbook

Threaded View

  1. #1
    Valued Forum Contributor
    Join Date
    03-23-2008
    Location
    Tennessee
    MS-Off Ver
    Office 2007
    Posts
    706

    VBA Formula Prompts me again to open a workbook

    Greetings, Gurus.

    I have a script designed to prompt you to open a workbook, giving you a selection box to use, and then perform a vlookup from the workbook you selected. I have set all my variables, but there must be some issue with my formula. Although the script enters the correct formula it prompts you twice more to open the workbook before entering the formula. Script below, with comments. Thanks in advance for any help you can offer.

    Sub Find_Already_Commit()
    Dim mwb As Workbook, vwb As Workbook
    Dim vfPath As String, vfName As String, str As String
    Dim fd As FileDialog
    
    Application.ScreenUpdating = False
    
    Set mwb = ThisWorkbook
    
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
       .Filters.Clear
       .Filters.Add "Excel Files", "*.xls*"
       .AllowMultiSelect = False
       .Title = "Select your Current Tooling Commit Workbook file to open!"
       If .Show <> -1 Then
          MsgBox "You didn't select any file", vbExclamation, "No File Selected!"
          Exit Sub
       End If
       vfName = .SelectedItems(1)
    End With
    
    Workbooks.Open vfName
    Set vwb = ActiveWorkbook
        mwb.Activate
        Range("AJ19").FormulaR1C1 = "CURRENT COMMIT VENDOR"
        Range("AJ19").WrapText = True
    
    'For some reason the formula below prompts me again to select a workbook to open.
    'I can select the same file I selected earlier.  It will then prompt me again.
    'I can agains select the file I selected earlier, and the remainder of the script
    'works correctly, giving me the results I expect.
        Range("AJ20").FormulaR1C1 = _
            "=IFERROR(VLOOKUP(RC2,'[vwb]MASTER'!R13C2:R15000C33,32,FALSE),"""")"
    
        Range("AJ20").Copy
        Range("AJ20:AJ" & Cells(Rows.Count, "A").End(xlUp).Row).PasteSpecial xlPasteFormulas
        Selection.Value = Selection.Value
        Application.CutCopyMode = False
       
    vwb.Close savechanges = False
    
    Range("A1").Select
    Application.ScreenUpdating = True
    MsgBox "Data has been imported successfully.", vbInformation, "Task Completed!"
    End Sub
    Last edited by hutch@edge.net; 06-13-2016 at 04:27 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Running workbooks.open to open pax-protected file still prompts for a password
    By karinincanada in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-24-2015, 05:06 PM
  2. Need to write formula from open workbook to closed workbook
    By punna111 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-07-2014, 02:33 PM
  3. [SOLVED] bypass opening workbook prompts
    By slxia1 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-01-2012, 04:44 AM
  4. macro that prompts to open another Excel file
    By jhmayor03 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-02-2012, 02:19 PM
  5. Replies: 1
    Last Post: 04-15-2011, 11:38 AM
  6. Workbook sheet change event prompts save
    By pprseller in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-29-2009, 02:12 PM
  7. Save an open workbook, then open template workbook and close the saved workbook
    By ondvirg in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-27-2009, 10:20 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1