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 and member on here! Thanks)

However, i have copied the code to a module in Access and now the "Application.GetOpenFileName" line fails, is it because the Application has changed? Anybody know how i can achieve what i want to? Thanks

 

Function GetTextFile(sPath) As String

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

End Function

' Above function is used to browse to a .txt file

Sub ImportTextFile()
Dim XL As Object
Dim XLWorkbook As Object
Set XL = CreateObject("Excel.Application")
XL.Visible = True
Set XLWorkbook = XL.Workbooks.Add

'Above Opens Up a New Workbook in Excel

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

' Above prompts user to open file
Any help would be greatly appreciated!



Ta



J