Browsing to a file to open in excel from Access

[JungleJme]

Hi,

I am very new to VBA and am trying to achieve the following:

Starting in Access:
Open up a new book in Excel
Prompts the user to browse for a .txt file on their system
File is opened in Excel
Sort the data to columns
(based on various parameters) export the data back into an Access table.
Excel Document Closes without saving
Access Document Saves.

I started the process in excel and managed to get the first 5 steps sorted. (With lots of help from the internet! Thanks)

However, i have copied the code to a module in Access and now the "Application.GetOpenFileName" line fails :banghead:

Function GetTextFile(sPath) As String
    ChDir sPath
    GetTextFile = Application.GetOpenFilename( _
        FileFilter:="Text Files (*.txt), *.txt,", _
        FilterIndex:=1, _
        Title:="Select A Text File")
End Function

[COLOR=lime]' Above function is used to browse to a .txt file[/COLOR]

Sub ImportTextFile()
Dim XL As Object
Dim XLWorkbook As Object
Set XL = CreateObject("Excel.Application")
XL.Visible = True
Set XLWorkbook = XL.Workbooks.Add
 
[COLOR=lime]'Above Opens Up a New Workbook in Excel[/COLOR]
 
Dim TxtFileName As String

TxtFileName = GetTextFile("C:\Users\7092\Desktop\")

Workbooks.OpenText filename:=TxtFileName, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), DecimalSeparator:=".", ThousandsSeparator:=",", TrailingMinusNumbers:=True
 
If Len(TxtFileName) Then
    If Right$(TxtFileName, 4) <> ".txt" Then
        MsgBox "You must select a text file!"
        Exit Sub
    End If
Else
    Exit Sub
End If

Any help would be greatly appreciated!

Ta

J

 

[pr2-eugin]

I do not think that Access has the method GetOpenFilename.. Instead use the FileDialog method.. Similar function.. You can find more information on uts usage and syntax here..
http://msdn.microsoft.com/en-us/library/aa208726(v=office.11).aspx