How to get VBA to treat text in a cell as a valid file path.

[PotatoPotatis]

Hi. I have a formula that takes user inputs and generates a file and range path in cell A1. So instead of hard coding the path I want it to use the text path in cell A1 dynamically to know what cells to copy. A1 is currently:

'C:\Users\DTAK\Desktop\Processing Tool\[Outlet 2012.xlsx]Sheet1'!A30:E44

The following code works hardcoded, but I don’t know how to adjust it. Any help is much appreciated!

Sub GetDataFromClosedBook()

Dim mydata As String

'data location & range to copy

mydata = "='C:\Users\DTAK\Desktop\Processing Tool\[Outlet 2012.xlsx]Sheet1'!A30:E44" '<< change as required

'link to worksheet

With ThisWorkbook.Worksheets(3).Range("A30:E44") '<< change as required

.Formula = mydata

'convert formula to text

.Value = .Value

End With

End Sub

I tried this but it failed with a 400 error:

Sub GetDataFromClosedBook()

Dim mydata As String

'data location & range to copy

mydata = "='" & Range("A1").Value & "'" '<< change as required





'link to worksheet

With ThisWorkbook.Worksheets(3).Range("A30:E44") '<< change as required

.Formula = mydata

'convert formula to text

.Value = .Value





End With

End Sub

I’d also prefer this works without any source files opening to the user. I’m fine with it running in the background though, of course.

Thank you.

 

[James006]

Hi,
You could test following

Sub GetDataFromClosedBook()
Dim mydata As String
'data location & range to copy
'  If cell A1 is holding:
'  'C:\Users\DTAK\Desktop\Processing Tool\[Outlet 2012.xlsx]Sheet1'!A30:E44
'
mydata = "=" & [A1]
'mydata = "='C:\Users\DTAK\Desktop\Processing Tool\[Outlet 2012.xlsx]Sheet1'!A30:E44" '<< change as required
    'link to worksheet
    With ThisWorkbook.Worksheets(3).Range("A30:E44") '<< change as required
        .Formula = mydata
        'convert formula to text
        .Value = .Value
    End With
End Sub

 

[PotatoPotatis]

Hi,
You could test following

Sub GetDataFromClosedBook()
Dim mydata As String
'data location & range to copy
'  If cell A1 is holding:
'  'C:\Users\DTAK\Desktop\Processing Tool\[Outlet 2012.xlsx]Sheet1'!A30:E44
'
mydata = "=" & [A1]
'mydata = "='C:\Users\DTAK\Desktop\Processing Tool\[Outlet 2012.xlsx]Sheet1'!A30:E44" '<< change as required
    'link to worksheet
    With ThisWorkbook.Worksheets(3).Range("A30:E44") '<< change as required
        .Formula = mydata
        'convert formula to text
        .Value = .Value
    End With
End Sub

Thank you. I just tried it, but unfortunately I still get a 400 error when running it. When I debug instead and go through the code it then gives me a Run-time 1004 error telling me there is an application-defined or object-defined error.

 

[PotatoPotatis]

It also works if I replace the range A30:E44 with just A30 and do this:

=“'C:\Users\DTAK\Desktop\Processing Tool\[Outlet 2012.xlsx]Sheet1'!A30”

If I expand the range here

With ThisWorkbook.Worksheets(3).Range("A30:E44")

I still get all the data I need.

However, it’s still not working if I concatenate multiple cells to build that path, which is what I need for it to be dynamic since it concatenates based on user inputs.

 

[PotatoPotatis]

I figured out a solution! I don’t know if it’s the best solution, feel free to correct me, but here:

Sub GetDataFromClosedBook()



'writes a formula in cell A1, based on the value in cell A20 where A20 is a combination of user inputs that have been concatenated by a formula (set the latter up yourself)

'the concatenated cells should end up formatted similar to: 'C:\Users\USERNAME\Desktop\Processing Tool References\[Outlet 2012.xlsx]Sheet1'!A2

'where A2 is the top left and first value being imported.

Range("A1").Formula = "=" & Chr(34) & Range("A20").Value & Chr(34)



'references the closed workbook using the path and range the code wrote in cell A1

'mydata = "= "'C:\Users\USERNAME\Desktop\Processing Tool References\[Outlet 2012.xlsx]Sheet1'!A2" "



Dim mydata As String

mydata = "=" & [A1]



'pastes the data into Sheet2 of the current workbook, pasting the data in cells A30:K45

'which will also represent the size of the range that will be copied from the reference workbook

'starting from the top left and first value being imported; in this case A2



With ThisWorkbook.Worksheets("Sheet2").Range("A30:K45")

.Formula = mydata



'converts formula to text

.Value = .Value



'<< change all cells, sheets, and ranges above as required



End With

End Sub

Thank you again for the help. You pointed me in the right direction.

 

[James006]

Glad you could fix your problem