CODEWITHSUNDEEP
vbaexcel
Lambo
Lambo

Reputation: 897

How to define file directory in VBA

I'm right now learning VBA in excel by myself. As we know in excel we can put a file directory in a function to link one value in a file to another. Say for example when in a cell we can do this

B2 ='C:\directory\[filename.xlsx]sheetname'!A1

How can I put this into a VBA script? Ultimately how can I pre-define "directory", "filename", "sheetname" and even cell position say something like 

directory = "myfolder\myfolder2\.."
cell = "A1"

Upvotes: 1

Views: 2514

Answers (2)

Dan Donoghue
Dan Donoghue

Reputation: 6206

I "think" you are asking how to split that string up into components yes? This is quite long winded but a good insight into how to play with strings, I hope it helps you with your learning. I have commented every line for you.

This may seem a little daunting at first but if you get comfortable with the split command and arrays now it will aid you very much moving forwards:

Sub SplitCellContents()
Dim MyString As String, MySheetName As String, MyCell As String, MyDrive As String, MyDir As String, MyFileName As String
MyString = "'C:\directory\[filename.xlsx]sheetname'!A1" 'Set the string value
MyCell = Split(MyString, "!")(UBound(Split(MyString, "!"))) 'Split the string into an array on the ! and take the last value in the array
MySheetName = Split(Split(MyString, "]")(UBound(Split(MyString, "]"))), "'")(0) 'Split the string into an array on "]" then split the resulting last value and split again on "'" and take the first value
' Look at what the above line does, split on ] gives a last value of sheetname'!A1 then split that on ' gives us a first value of sheetname
MyDrive = Replace(Split(MyString, "\")(0), "'", "") 'Split the string on \ and take first value
MyString = Replace(MyString, "'" & MyDrive, "") 'Chop out the drive reference from the string to allow further manipulation
MyString = Replace(MyString, "'!" & MyCell, "") 'Chop out the cell reference from the string to allow further manupulation
MyFileName = Replace(Replace(Split(MyString, "[")(UBound(Split(MyString, "["))), "]", ""), MySheetName, "") 'Similar to what we do for mycell, see if you can work out how
MyDir = Replace(Replace(MyString, "[" & MyFileName & "]", ""), MySheetName, "") ' Replace the fileName and sheetname in the string with nothing, should leave the DIR
MsgBox "MyCell = " & MyCell & vbLf & _
    "MySheetName = " & MySheetName & vbLf & _
    "MyDrive = " & MyDrive & vbLf & _
    "MyDir = " & MyDir & vbLf & _
    "MyFileName = " & MyFileName 'Output to a messagebox
End Sub

Looks scary but paste it into the VBE and see if you can follow it.

There are MANY ways to play around with strings, I do tend to prefer a split and array manipulation but many people would use a combination of Mid, Left, Right and Find / Instr.

Upvotes: 0

brettdj
brettdj

Reputation: 55682

Given you are learning you would do it something like this

Note that the code tests for the filepath being valid

your followup question

[b2] = "='" & strPath & "[" & strFile & "]" & strSht & "'!" & strCell
[b2].Copy [b3:b4]

original

Sub Test()

Dim strPath As String
Dim strFile As String
Dim strSht As String
Dim strCell As String

strSht = "Sheet2"
strCell = "A1"

strPath = "C:\temp\"
strFile = "test.xlsx"

If Len(Dir(strPath & strFile)) > 0 Then
    [b2] = "='" & strPath & "[" & strFile & "]" & strSht & "'!" & strCell
Else
    MsgBox "invalid file", vbCritical
End If

End Sub

Upvotes: 2

Related Questions