locked
Open workbook saved on desktop via text in active workbook RRS feed

  • Question

  • I have a file saved on my desktop. The end of the file name will always be the same ("asreported.xls"). However, the start of the file name will have a ticker prefix (ie: PPG, COST, O, D, T, etc...). 

    I would like to define the file name in my active workbook on a sheet called "Start". On the "Start" sheet in A1 I can put "c:\users\name\desktop\tmp\ppg_asreported.xls" so that when I run my VBA code, it will know which file to open. The sheet from that file is then copied into a my active workbook and the sheet is named "Mergent". 

    My current VBA cod is: 

    Public Sub OpenStockRowBS()
    
    ' *************************************************
    
    ' Define Workbook and Worksheet Variables
    
    ' *************************************************
    
    Dim wkbMyWorkbook As Workbook
    
    Dim wkbWebWorkbook As Workbook
    
    Dim wksWebWorkSheet As Worksheet
    
    Dim wsDest As Worksheet
    
    
    
    Set wkbMyWorkbook = ActiveWorkbook
    
    On Error Resume Next
    
    Set wsDest = wkbMyWorkbook.Worksheets("Mergent")
    
    wsDest.Cells.ClearContents
    
    On Error GoTo 0
    
    
    
    ' *************************************************
    
    ' Open The Web Workbook From Desktop (Financial Statements)
    
    ' *************************************************
    
    Workbooks.Open
    ("C:\Users\name\Desktop\tmp\asreported.xls")
    
    
    
    ' *************************************************
    
    ' Set the Web Workbook and Worksheet Variables
    
    ' *************************************************
    
    Set wkbWebWorkbook = ActiveWorkbook
    
    Set wksWebWorkSheet = ActiveSheet
    
    
    
    ' *************************************************
    
    ' Copy The Web Worksheet To My Workbook and Rename
    
    ' *************************************************
    
    If wsDest Is Nothing Then
    
        wksWebWorkSheet.Copy
    After:=wkbMyWorkbook.Sheets("Start")
    
       
    wkbMyWorkbook.Sheets(ActiveSheet.Name).Name = "Mergent"
    
    Else
    
        wksWebWorkSheet.Cells.Copy
    wsDest.Range("A1")
    
    End If
    
    
    
    ' *************************************************
    
    ' Close the Web Workbook
    
    ' *************************************************
    
    wkbMyWorkbook.Activate
    
    wkbWebWorkbook.Close
    
    End Sub

    I have tried changing the C:\users..... code line to: 

    Workbooks.Open ("C:\Users\name\Desktop\tmp\"& thisworkbook.Worksheets("Start").Range("A7")")

    In an effort to get the code to reflect changes in the name on my "Start" sheet.  I can't get the data to pull. 

    Any ideas? Thanks in advance!

    Friday, February 1, 2019 3:54 PM

All replies

  • If the file called "ppg_asreported.xls" is saved in a folder called "tmp" on your Desktop and the value "ppg" is in A7 on Sheet Start, try something like this...

    Public Sub OpenStockRowBS()

    ' *************************************************
    ' Define Workbook and Worksheet Variables
    ' *************************************************
    Dim wkbMyWorkbook As Workbook
    Dim wkbWebWorkbook As Workbook
    Dim wksWebWorkSheet As Worksheet
    Dim wsDest As Worksheet
    Dim strFileName As String, strFilePath As String

    Set wkbMyWorkbook = ActiveWorkbook

    On Error Resume Next
    Set wsDest = wkbMyWorkbook.Worksheets("Mergent")
    wsDest.Cells.ClearContents
    On Error GoTo 0

    ' *************************************************
    ' Open and set the Web Workbook From Desktop (Financial Statements)
    ' *************************************************
    strFileName = wkbMyWorkbook.Worksheets("Start").Range("A7").Value & "_asreported.xls"
    strFilePath = Environ("UserProfile") & "\Desktop\tmp\"

    'Check if the file exists
    If Len(Dir(strFilePath & strFileName)) = 0 Then
        MsgBox "The file " & strFileName & " was not found at the location " & strFilePath & ".", vbCritical, "File Not Found!"
        Exit Sub
    End If
    Set wkbWebWorkbook = Workbooks.Open(strFilePath & strFileName)

    ' *************************************************
    ' Set the Web Worksheet Variables
    ' *************************************************

    Set wksWebWorkSheet = ActiveSheet

    ' *************************************************
    ' Copy The Web Worksheet To My Workbook and Rename
    ' *************************************************
    If wsDest Is Nothing Then
        wksWebWorkSheet.Copy After:=wkbMyWorkbook.Sheets("Start")
        wkbMyWorkbook.Sheets(ActiveSheet.Name).Name = "Mergent"
    Else
        wksWebWorkSheet.Cells.Copy wsDest.Range("A1")
    End If

    ' *************************************************
    ' Close the Web Workbook
    ' *************************************************
    wkbMyWorkbook.Activate
    wkbWebWorkbook.Close
    End Sub


    Subodh Tiwari (Neeraj) sktneer



    Friday, February 1, 2019 6:03 PM
  • Hi Subodh, 

    That did work. The code debugger stops at:

    If Len(Dir(strFilePath & strFileName)) = 0 Then

    In Start A7 I have 

    C:\Users\name\Desktop\tmp\PPG_asreported.xls
    Friday, February 1, 2019 7:49 PM
  • If the all the files are saved in a specific folder and only a part of file path gets changed, I don't think you need to mention the full file path in A7 unless you are going to change the source folders from the file path in any case.

    Why not just enter PPG or COST or O or D etc in A7 and the code will pick the correct file if it is saved in the source folder? User may make a mistake in entering the full file path.

    So in A7, you may have a data validation list with all the file strings like PPG, COST etc and user may run the code after selecting the correct file name from the list.

    BTW the line If Len(Dir(strFilePath & strFileName)) = 0 Then checks whether a file with the mentioned file path exists or not.


    Subodh Tiwari (Neeraj) sktneer




    Friday, February 1, 2019 8:00 PM