+ Reply to Thread
Results 1 to 9 of 9

Inputting single file information instead of batch files

  1. #1
    Registered User
    Join Date
    09-01-2017
    Location
    Leicester
    MS-Off Ver
    2010
    Posts
    5

    Question Inputting single file information instead of batch files

    Hello everybody (hello doctor nick)

    I'm a long time reader and follower first time posting.

    From this amazing community i was able to import multipul file names into excel. One down side was that sometimes i only need to select one file instead of the whole folder. I used the following code but it does not work.

    Sub GetFile()

    Dim xFso As Object
    Dim xFolder As Object
    Dim xFile As Object
    Dim xFiDialog As FileDialog
    Dim xPath As String
    Dim i As Integer

    Set xFiDialog = Application.FileDialog(msoFileDialogFilePicker)

    If xFiDialog.Show = -1 Then
    xPath = xFiDialog.SelectedItems(1)
    End If

    Set xFiDialog = Nothing

    If xPath = "" Then Exit Sub

    Set xFso = CreateObject("Scripting.FileSystemObject")

    Set xFolder = xFso.GetFile(xPath)

    i = 1

    For Each xFile In xFolder.Files


    i = i + 1

    ActiveSheet.Cells(i, 1) = Left(xFile.Name, InStrRev(xFile.Name, ".") - 1)

    ActiveSheet.Cells(i, 8) = xPath

    Next

    MsgBox "Completed!"

    End Sub

    I'm really not a programmer but i love how VB works. I've been messing around with it at work to make our lives easier. I tried to do this program but failed hard.

    I would appreciate anyone and everyones help on this please.

    So the end goal is i want to be able to select individual files in all different kinds of folders and extract there file names and place them into the currently selected empty cell.

    Thank you all for reading this and await your thoughts and ideas.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,613

    Re: Inputting single file information instead of batch files

    Quote Originally Posted by Fusion187 View Post
    So the end goal is i want to be able to select individual files in all different kinds of folders and extract there file names and place them into the currently selected empty cell.
    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    09-01-2017
    Location
    Leicester
    MS-Off Ver
    2010
    Posts
    5

    Re: Inputting single file information instead of batch files

    thank you very much for your help with this.

    Is it still possible to use the following code with it as well:-

    ActiveSheet.Cells(i, 1) = Left(xFile.Name, InStrRev(xFile.Name, ".") - 1)

    ActiveSheet.Cells(i, 8) = xPath

    Just realised i need part of the file name only in column A and then the path for the file in column H.

    I dont think i need the i value now as there is only one file selected.

    I really do appreciate what you have done

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,613

    Re: Inputting single file information instead of batch files

    Quote Originally Posted by Fusion187 View Post
    Just realised i need part of the file name only in column A and then the path for the file in column H.
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    09-01-2017
    Location
    Leicester
    MS-Off Ver
    2010
    Posts
    5

    Re: Inputting single file information instead of batch files

    My god that is perfect! I owe you a beer my friend. Thank you very much

  6. #6
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,613

    Re: Inputting single file information instead of batch files

    You're welcome. Thanks for the feedback.

  7. #7
    Registered User
    Join Date
    09-01-2017
    Location
    Leicester
    MS-Off Ver
    2010
    Posts
    5

    Re: Inputting single file information instead of batch files

    Sorry to be a pain AlpaFrog, but it there any chance to get the file name without the file extension showing. I'm pulling in .dxf files but our laser machine will not read it because its trying to find a .dxf.dxf file which does not exist haha.

    I tried to change the "\" with a "." but it didn't work. Would i need to create another macro to remove the dxf?

    Thanks again AlphaFrog

  8. #8
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,613

    Re: Inputting single file information instead of batch files

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    09-01-2017
    Location
    Leicester
    MS-Off Ver
    2010
    Posts
    5

    Re: Inputting single file information instead of batch files

    Your the kind of VB! Thank you very much for your help yet again. You should be working for the FBI

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 11-12-2013, 03:44 PM
  2. Macro works well on single file but not on all files in a single directory
    By dnganatra in forum Word Programming / VBA / Macros
    Replies: 6
    Last Post: 08-19-2013, 02:15 AM
  3. Use a Macro to pull all information from a txt file into a single column
    By ArmyExcelProgrammer in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-20-2013, 03:11 PM
  4. [SOLVED] inputting a single value into a variant
    By guerrillagaurav in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-05-2012, 03:44 PM
  5. Inputting information into a webpage from VB Excel
    By saamer87 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-24-2012, 08:46 AM
  6. get file information from files with http://
    By tlew in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-24-2009, 10:52 AM
  7. Transfering files from lots of files to one single Exel master file.
    By yjacob in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 07-01-2009, 11:37 AM
  8. Batch file to start multiple excel files with auto_open() macro. Delay code needed
    By pierrelap@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-29-2005, 02:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1