none
VBA code to open workbook that changes filename every single time RRS feed

  • Question

  • Hello VBA developers! 

    Hoping you can help me update this code. Basically, its objective is that each time there is a quote created, it will log all the details into a master workbook and grabbing all the data from the quote that was created like reference number, date, client name, processor etc. 

    I created the code below so it will be able to open the workbook/file. however, the filename changes everytime a quote is created. Thanks in advance for the help!

    Dim copyrow As Workbook
    Dim pasterow As Workbook
    
    
    
    '## Open both workbooks first:
    
    Set copyrow = Workbooks.Open("C:\Users\sea\Desktop\PROJECT_MASTER\PROJECT\TEST2.xlsm ")       ' need to change this part since workbook filename will always change
    Set pasterow = Workbooks.Open("C:\Users\sea\Desktop\PROJECT_MASTER\ PROJECT\Quote_mastersheet.xlsx")
    

    Tuesday, July 17, 2018 11:33 PM

Answers

  • Change the Set copyrow = … line to this:

    Dim strFile As String
    strFile = Application.GetOpenFilename
    If strFile = "False" Then
        Beep
        Exit Sub
    End If
    Set copyrow = Workbooks.Open(strFile)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by IamJackie Wednesday, July 18, 2018 4:36 PM
    Wednesday, July 18, 2018 8:03 AM

All replies

  • Change the Set copyrow = … line to this:

    Dim strFile As String
    strFile = Application.GetOpenFilename
    If strFile = "False" Then
        Beep
        Exit Sub
    End If
    Set copyrow = Workbooks.Open(strFile)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by IamJackie Wednesday, July 18, 2018 4:36 PM
    Wednesday, July 18, 2018 8:03 AM
  • Appreciate your help as always! It worked perfectly! 
    Wednesday, July 18, 2018 4:37 PM