Trying to create a dynamic filepath within VB

[Steve]

Hi

Is it possible to amend the code below so that the filepath is determined by the contents of a cell, say Sheet1!B$2$? The code is the start of a macrothat pulls in the delimited data from the file Applesnew, formats it, and converts # symbols to £ ready for calculating totals.

The problem is that on any given day I get 16 of the source files from different providers, say Applesnew, Bananasnew, Cherriesnew. If I have to use astatic filepath in my code I'll need 16 different spreadsheets (the sheets, which also contain very sensitive information, get mailed off to the dataprovider, so I don't want a single-workbook-with-16-worksheets solution. My auditor probably wouldn't appreciate it either

'import data
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;\\server\apollo\LATEST PAYMENTS - TEST\Applesnew.", _
Destination:=Range("$A$5"))
.Name = "Apples080213."
.FieldNames = True
.RowNumbers = False
…[]

The macro was recorded as I'm not much at writing code, but I assume the part that needs amending is that "Applesnew." should somehow link to the contents of cell B2. And that's where I get stuck. Does anyone have any suggestions at all, please?

Thanks in advance,

Steve

 

[GS]

Try...

'import data

Dim sConnect$ '//connection string

'Build connection string; edit to suit
sConnect = "TEXT;\\server\apollo\LATEST PAYMENTS - TEST\"
sConnect = sConnect & Range("$B$2").Value & "."

'Run the query
With ActiveSheet.QueryTables.Add(sConnect, Range("$A$5"))

.Name = "Apples080213."
.FieldNames = True: .RowNumbers = False
…[]

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Steve]

Try... > 'import data Dim sConnect$ '//connection string 'Build connection

Hi Garry

That was perfect, works like a charm; thank you very much!

Cheers

Steve

 

[GS]

You're welcome! I appreciate the feedback...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion