Prompt user to select file (Access VBA)

[kmarchiony]

Hi anyone!
I am working to translate an Excel macro into Access and I'm having little luck with a couple lines of code. I can't figure out how to prompt the user to select a file (not to past a path and filename) but to actually browse for a file :) Here is the Excel code I already have:

Dim TxtName As String
TxtName = Application.GetOpenFilename("TextFiles (*.txt), *.txt", , "Select The file you wish to update:")


I'm also hoping to strip off the first few blank lines from the text file I am importing. In excel it was easy because I could simply tell the program to look at the 4th line in the text file:
'Import the .txt file:
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & CStr(TxtName), Destination:=Range("A1"))
.TextFileStartRow = 4

But my macro in Access doesn't seem to give that option. All I have in access so far is:
DoCmd.TransferText acImportDelim, "InventoryObsoSpec", NewTableName, filename, No

But that grabs a bunch of blank lines into my data table. Can anyone help me out there???
Thanks!

[Denburt]

You will need to add a refference to MS Office, go into the VBA window - Tools -References

Function FindTxtFile(strFNme As String, strSearchPath As String) As String
' Displays the Open dialog box for the user to locate
' the BackEnd database. Returns the full path.
 Dim myDialog As FileDialog
    'Declare a variable as a FileDialogFilter object.
 Dim vrtSelectedItem As Variant
    'Set the FileDialogFilters collection variable to
    'the FileDialogFilters collection of the SaveAs dialog box.
Set myDialog = Application.FileDialog(msoFileDialogOpen)
With myDialog
    .AllowMultiSelect = False
     .Filters.Add "Text", "*.txt", 1
    .Title = "Where Is The Text File?"
'    Debug.Print Right(strSearchPath, Len(strSearchPath) - 10)
    .InitialFileName = strSearchPath & strFNme
    If .Show = -1 Then
        For Each vrtSelectedItem In .SelectedItems
            FindTxtFile = Trim(vrtSelectedItem)
        Next vrtSelectedItem
    Else
    End If
   Set myDialog = Nothing
End With
End Function
 

[JConsulting]

Hi anyone!
I am working to translate an Excel macro into Access and I'm having little luck with a couple lines of code. I can't figure out how to prompt the user to select a file (not to past a path and filename) but to actually browse for a file :) Here is the Excel code I already have:

Dim TxtName As String
TxtName = Application.GetOpenFilename("TextFiles (*.txt), *.txt", , "Select The file you wish to update:")


I'm also hoping to strip off the first few blank lines from the text file I am importing. In excel it was easy because I could simply tell the program to look at the 4th line in the text file:
'Import the .txt file:
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & CStr(TxtName), Destination:=Range("A1"))
.TextFileStartRow = 4

But my macro in Access doesn't seem to give that option. All I have in access so far is:
DoCmd.TransferText acImportDelim, "InventoryObsoSpec", NewTableName, filename, No

But that grabs a bunch of blank lines into my data table. Can anyone help me out there???
Thanks!

 
Dim TxtName As String
dim strLine as string
TxtName = Application.GetOpenFilename("TextFiles (*.txt), *.txt", , "Select The file you wish to update:")
open txtname for input as #1
for I = 1 to 5
Line Input #1, strLine
next I
do until eof(1)  
Line Input #1, strLine '<--now you're on record 5...what do you want to do with it?
'do your stuff here
loop
close #1