Optional File Browsing

judicial85 · #1 03-06-2011, 06:11 PM

Hello!

I currently have a macro that automatically imports "DATA.txt" off of the Desktop and into EXCEL. What I was thinking of was to add a subroutine that if "DATA.txt" was not found by the program, it would open a file browsing window so the user can select the file themselves.

Can someone help with that?
Thanks, Bryan

(Current code piece)

Code:

'IMPORT the file from desktop (DATA.txt)
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\BK\Desktop\DATA.txt", Destination:=Range("$A$1"))
        .name = "dos"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1)
        .TextFileFixedColumnWidths = Array(13, 12)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

macropod · #2 03-08-2011, 01:10 AM

Here's one way:

Code:

Dim strFile As String
strFile = "C:\Users\BK\Desktop\DATA.txt"
With Application.FileDialog(msoFileDialogFilePicker)
  .AllowMultiSelect = False
  .Show
  If .SelectedItems.Count = 1 Then
    strFile = .SelectedItems(1)
  Else
    MsgBox "No file selected", vbExclamation
    Exit Sub
  End If
End If
'IMPORT the file from desktop (DATA.txt)
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & strFile, Destination:=Range("$A$1"))
     ...

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Help! Find text in doc file & copy to another file	northstarman	Word	1	11-05-2010 08:15 AM
Word Macro: Save file as text with current file name	jabberwocky12	Word VBA	2	10-22-2010 12:23 PM
linking a word file and an excell file on a macbook pro	Fleur	Office	0	07-14-2010 08:19 PM
How to open ppt file directly with slide show when clicked on file.	praveen_p	PowerPoint	1	05-06-2009 12:42 PM
Outlook xp slow when Browsing to attached a file	jmelgar1	Outlook	0	09-23-2008 01:49 PM