Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: VBA - Opening a file within a macro
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2019
    Posts
    88
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default VBA - Opening a file within a macro

    Hi, when I open a file within a macro, I'm typically hitting F8 so that I can see if all the step are working properly, but when I open a file within the macro, it runs the macro fully, is there anyway to prevent this so that I can just keep hitting F8 to see if I have an errors and the information is working correctly.

    [ub Filter()Dim lRow As Long
    Dim fd As Office.FileDialog


    lRow = ActiveSheet.Range("A500").End(xlUp).Row
    If AutoFilterMode = True And FilterMode = True Then ActiveSheet.ShowAllData
    lRow = ActiveSheet.Range("A500").End(xlUp).Row

    Range("B9").Select
    ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[2],'[Finance Extract1.xls]Sheet1'!R8C4:R500C4,1,FALSE)"
    Selection.AutoFill Destination:=Range("B9:B500")
    Range("B9:B500").Select

    Set fd = Application.FileDialog(msoFileDialogFilePicker)


    'open finance rec
    With fd
    .AllowMultiSelect = False
    .Title = "Please select the file."

    .Filters.Clear

    If .Show = True Then
    txtfilename = .SelectedItems(1)
    Workbooks.Open txtfilename
    End If]

  2. #2
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    2,796
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA - Opening a file within a macro

    .
    Hmmm ... as you have learned, the purpose of F8 is to allow you to execute one line at a time.

    Another method I've used is to insert a MsgBox after the line I want to test. As it progresses, the MsgBox
    is moved down one line at a time.

  3. #3
    Board Regular
    Join Date
    Mar 2019
    Posts
    88
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Opening a file within a macro

    what message box do you use,

    MsgBox "stop"

  4. #4
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Opening a file within a macro

    Debug.Print is your friend.

  5. #5
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    2,796
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA - Opening a file within a macro

    .
    Your example will work.

    Or just MsgBox ""

    Debug.Print is another method.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,177
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA - Opening a file within a macro

    Select the line of code after you open the other workbook, hit F9 then step through with F8
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular
    Join Date
    Mar 2019
    Posts
    88
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Opening a file within a macro

    how do you use debug print and what does it mean

  8. #8
    Board Regular
    Join Date
    Mar 2019
    Posts
    88
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Opening a file within a macro

    even when I use the msg "stop", it is still running through the macro. How do I use debug print to stop this from happening

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,177
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA - Opening a file within a macro

    Debug.print won't stop it, it simply writes information to the immediate window.

    Do you mean that you have the word Stop in the code & it doesn't actually stop ?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    Board Regular
    Join Date
    Mar 2019
    Posts
    88
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Opening a file within a macro

    yes, I have msg "stop", and a message box appears and I click Ok, then it keeps moving through the macro until the next msg "stop" box appears, but I want it to actually stop and then I can click F8 again to move through the macro myself until I'm confident it fully works as it should. Once I'm confident, I will let it run on it's own, but right now I'm just trying to test all this to ensure the macro works properly.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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