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