Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 16
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 16
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,514
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
create dynamic comboboxes Martin Excel Programming 2 July 3rd 09 05:05 PM
Create a dynamic list SteveT Excel Worksheet Functions 3 December 2nd 08 08:52 AM
How to create a 'dynamic' formula? Bill Excel Worksheet Functions 11 April 22nd 06 07:18 PM
Create filepath if it's not available StephanieH Excel Programming 3 November 8th 05 07:36 PM
create a dynamic Range() rbaldwin[_2_] Excel Programming 3 May 3rd 04 10:53 PM


All times are GMT +1. The time now is 10:47 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"