Delete data in closed workbook?

[JKB]

Hi everybody!

I would like to delete data within a specified range in a closed workbook from which im already extracting data.
Im using the following code to get the data, and i would prefer to keep loading it this way, else the rest of the code will not work.


dim dDate As Date
Dim sTemp As String
Dim mydata As String
Dim shtTemp As Worksheet: Set shtTemp = Worksheets("Temp")
Dim shtMain As Worksheet: Set shtMain = Worksheets("Main")

'data location & range to copy (CANT)
mydata = "='S:\JKB\(Name of workbook).xlsx](Name of sheet)'!$A$2:$B$10000" 'Getting data from closed WB


With ThisWorkbook.Worksheets("Temp").Range("A2:B10000") 'Inserting into sheet from which im running the code
.Formula = mydata
'convert formula to text
.Value = .Value
End With




I would be able to solve this problem by opening the workbook, but i would like to do it without opening it, just as im copying data without opening the workbook

Does anybody have any suggestions i could try out?

[snb]

Why don't you use a querytable ?

[JKB]

Never heard about it, how would you use it in this case?

- Was also wondering, whether anybody know what the method im currently using is called? Would be easier to google then

[Aflatoon]

- Was also wondering, whether anybody know what the method im currently using is called?

A formula.

[JKB]

Haha lol! Google search: "Use formula to delete in closed workbook - vba" :P

[Aflatoon]

You can't do it with a formula.

[JKB]

Crap/shame