Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to create a dynamic filepath within VB
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 macro that 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 a static filepath in my code I'll need 16 different spreadsheets (the sheets, which also contain very sensitive information, get mailed off to the data provider, 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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to create a dynamic filepath within VB
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to create a dynamic filepath within VB
On Friday, February 8, 2013 2:51:23 PM UTC, GS wrote:
Try... 'import data Dim sConnect$ '//connection string 'Build connection Hi Garry That was perfect, works like a charm; thank you very much! Cheers Steve |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to create a dynamic filepath within VB
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
create dynamic comboboxes | Excel Programming | |||
Create a dynamic list | Excel Worksheet Functions | |||
How to create a 'dynamic' formula? | Excel Worksheet Functions | |||
Create filepath if it's not available | Excel Programming | |||
create a dynamic Range() | Excel Programming |