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 ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
