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"
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
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.
