Use VBA to Grab Only Part of a Filename/Filepath

2
votes

I'm not sure how to tackle this issue. I've done quite a bit of research, but most of the answers I find are a little different than what I need.

What I'm trying to accomplish is this:

  1. Open up an existing workbook manually (wbAI),
  2. Start macro,
  3. Use msoFileDialogOpen to find and open a file (call this wb2),
  4. Store part of wb2's file name (there is a date in the file name) as a variable or string. (I'm not sure which is better for this purpose. Maybe I don't need to store it all...),
  5. Paste part of wb2's filename (the date) in a certain cell in wb1,
  6. Copy the necessary data from wb2,
  7. Paste the data in wb1,
  8. Format the data,
  9. Use a VLOOKUP on the pasted data,
  10. Close wb2 without saving
  11. End the macro.

My macro can do every step listed above except for numbers four and five. On one hand, I'm wondering how I need to pursue this, and on the other hand, I'm wondering where this would fit inside my current code.

To give you an example of what I'm talking about: let's say that in step three I open up a workbook that's named "01.31.13 Group Names." And the file path is from a Sharepoint site so it looks like this:

"https://company.com/team/teamone/_layouts/xlviewer.aspx?01.31.13%20Group%20Names%20.xlsm&Source=https......."

How can I pick out only the date in the filename/filepath?

Here's the beginning of my code:

Sub Test()

Dim lastRow As Long, i As Long
Dim ws As Worksheet
Dim wbSource As Workbook
Dim wbAI As Workbook
Dim vrtSelectedItem As Variant

Set wbAI = ActiveWorkbook
Application.ScreenUpdating = False 

With Application.FileDialog(msoFileDialogOpen)
    .InitialFileName = https://company.com/team/teamone & "\"
    .AllowMultiSelect = False
    .Show
    '  ******  Is this where the new code could be inserted? *******
   For Each vrtSelectedItem In .SelectedItems
   Set wbSource = Workbooks.Open(vrtSelectedItem)
   Next
   End With
   ' Check if the first cell contains data. If not then close file
   If Range("Profile!H9") = "" Then
        ActiveWorkbook.Close savechanges:=False
        ActiveWorkbook.Saved = False

Any suggestions are welcome! Thank you for your time!

Edit: This is how my code looks after Philip's suggestion:

Sub Test()

Dim lastRow As Long, i As Long
Dim ws As Worksheet
Dim wbSource As Workbook
Dim wbAI As Workbook
Dim vrtSelectedItem As Variant

Set wbAI = ActiveWorkbook
Application.ScreenUpdating = False 

With Application.FileDialog(msoFileDialogOpen)
    .InitialFileName = https://company.com/team/teamone & "\"
    .AllowMultiSelect = False
    .Show

   For Each vrtSelectedItem In .SelectedItems
   Set wbSource = Workbooks.Open(vrtSelectedItem)
   Next
   End With
   dateVar = Left(wbSource.Name, 8) '<~~~~ New code
   ' Check if the first cell contains data. If not then close file
   If Range("Profile!H9") = "" Then
        ActiveWorkbook.Close savechanges:=False
        ActiveWorkbook.Saved = False
   Else
        Sheets("Profile").Activate
        Range("H9:I" & Cells(Rows.Count, "H").End(xlUp).Row).Select
        Selection.Copy
        Windows("wbName").Activate
        Sheets("Sheet1").Activate
        Range("A2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("E1").Value = dateVar '<~~~ New code
1
vbaexcelfilenames

1 Answers

5
votes

from the filename you would use the LEFT FUNCTION to return the LEFT 8 chars of the date:

dateVar=left(wbSource.name, 8)

then you can put that in your cell:

rangeVar.value=dateVar

hope that gets you going

Philip