Help with simple code

[london_irish]

Hi guys, this should be a very straight-forward one for you, but being new to VBA and as a result, a bit of a numpty, I don't really know where to reference the answer.

I have a macro saved which opens up a bunch of txt files and inserts them into a spreadsheet. All the files are in the same location, but I also want to be able to print that file path of those text files in a cell in that spreadsheet.

I can find solutions about getting the file path of the spreadsheet into which I am inserting the text files, but I don't seem to be able to find a solution for grabbing a file path for a different file.

I have copied the relevat bit of code, which prompts me to go search for and open my text files, and so suspect I need to insert a line in, or around this section.

As you can see, I have defined "TheFileName" as a string, so why can I just type =TheFileName in a cell in Excel and get the filepath, or am I way off the mark here?

Any help would be much appreciated

Sub macAS1Import()

' macAS1Import Macro
Dim TheFileName As String
  TheFileName = Application.GetOpenFilename("CAO Data, *.GY*") 
 
  'ChDir _
  "C:\Users\Documents\Databases\Upload Files\2014\120514"
  Workbooks.OpenText Filename:= _
  TheFileName _
  , Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
  Array(Array(0, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), _
  Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array( _
  19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), _
  Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array( _
  32, 1), Array(33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), _
  Array(39, 1), Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), Array( _
  45, 1), Array(46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1), Array(51, 1), _
  Array(52, 1), Array(53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57, 1), Array( _
  58, 1), Array(59, 1), Array(60, 1), Array(61, 1), Array(62, 1), Array(63, 1), Array(64, 1), _
  Array(65, 1), Array(66, 1), Array(67, 1), Array(68, 1), Array(69, 1), Array(70, 1), Array( _
  71, 1), Array(72, 1), Array(73, 1), Array(74, 1), Array(75, 1), Array(76, 1), Array(77, 1), _
  Array(78, 1), Array(79, 1), Array(80, 1), Array(81, 1), Array(82, 1), Array(83, 1), Array( _
  84, 1), Array(85, 1), Array(86, 1), Array(87, 1), Array(88, 1), Array(119, 1), Array(124, 1) _
  , Array(129, 1), Array(134, 1), Array(139, 1), Array(144, 1), Array(149, 1), Array(154, 1), _
  Array(159, 1), Array(164, 1), Array(169, 2), Array(175, 2), Array(181, 2), Array(187, 2), _
  Array(193, 2), Array(199, 2), Array(205, 2), Array(211, 2), Array(217, 2), Array(223, 2), _
  Array(229, 1), Array(232, 1), Array(235, 1), Array(238, 1), Array(241, 1), Array(244, 1), _
  Array(247, 1), Array(250, 1), Array(253, 1), Array(256, 1), Array(259, 1), Array(267, 2), _
  Array(271, 2), Array(273, 2), Array(275, 2), Array(277, 2)) _
  , TrailingMinusNumbers:=True
  Sheets("AS1").Select
  Sheets("AS1").Move After:=Workbooks("DataImportAndUpload.xlsm").Sheets(1)
  'MsgBox (TheFileName)
End Sub

 

[Hui]

In your code to get the file path just add the second line as shown below:

TheFileName = Application.GetOpenFilename("CAO Data, *.GY*")
FolderPart = Left(TheFileName, InStrRev(TheFileName, "\"))

 

[london_irish]

Wow, thanks for the speedy response Hui!

And the next step, how do I go about getting that file path and printing it in any cell in Excel?

(My file subdirectory has informatio which I would like to use elsewhere, but just need to access this)

 

[london_irish]

No worries...I've figured it - cheers!