Perfect! Worked a charm. Thank you. On 19 Apr, 07:20, "Ajit Navre" <ajit.na...@gmail.com> wrote: > Use something like... > '''' > Dim vArr As Variant > vArr = Split(ActiveSheet.QueryTables(1).Connection, "\") > Debug.Print vArr(UBound(vArr)) > '''' > Ajit > > -----Original Message----- > From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] > > On Behalf Of Mr Incredible > Sent: Sunday, April 19, 2009 3:00 AM > To: MS EXCEL AND VBA MACROS > Subject: $$Excel-Macros$$ Re: Interrogating the file name used/selected in a > query of an external CSV file > > Hi, > > Nearly there..... > > ActiveSheet.QueryTables(1).Connection returns the string "TEXT;<full > path name of the file>" > > Is there a way just to get the file name and not the file type and > path? > > Thanks > > On 17 Apr, 15:09, "Ajit Navre" <ajit.na...@gmail.com> wrote: > > Hi there, > > > If you have loaded the query with the Data->Import External Data->Import > > Data option then, following code would return the source file name > > > ActiveSheet.QueryTables(1).Connection > > > Then probably try to manipulate the Workbook_BeforePrint Event to include > > the file name in the header or footer as appropriate... > > > Let me know if this is what you are looking for... > > > Regards > > > Ajit > > > -----Original Message----- > > From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] > > > On Behalf Of Mr Incredible > > Sent: Friday, April 17, 2009 3:58 PM > > To: MS EXCEL AND VBA MACROS > > Subject: $$Excel-Macros$$ Interrogating the file name used/selected in a > > query of an external CSV file > > > Hi all, > > > I've done many searches for a potential solution to my "problem", and > > have drawn a blank. Any help/suggestions will be appreciated. > > > Problem to solve: > > > I have a worksheet in which there is just one named external query > > which loads an external CSV file. > > The "Data Range Properties" is set to 'Prompt for File Name on > > Refresh' so the user can select a file. The user can repeat this for > > different file names which contain different records. An advanced > > filter then extracts the relevant information and an OFFSET named > > range defines the (variable) print area, and is printed off. > > > What I'd like to do is to place the name of the external file name > > (path not necessary) in a cell which gets printed. In this way any > > printed data will have the reference file name in view. If this could > > be put in the header of footer then great! but in a single cell would > > be fine. I cannot find how to use VBA to get the name of the file > > which hass been selected to load within the query. > > > Thanks.
--~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
