By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,871 Members | 2,575 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,871 IT Pros & Developers. It's quick & easy.

Prompt user to select file (Access VBA)

P: 1
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!
May 9 '07 #1
Share this Question
Share on Google+
2 Replies


Denburt
Expert 100+
P: 1,356
You will need to add a refference to MS Office, go into the VBA window - Tools -References


Expand|Select|Wrap|Line Numbers
  1. Function FindTxtFile(strFNme As String, strSearchPath As String) As String
  2. ' Displays the Open dialog box for the user to locate
  3. ' the BackEnd database. Returns the full path.
  4.  Dim myDialog As FileDialog
  5.     'Declare a variable as a FileDialogFilter object.
  6.  Dim vrtSelectedItem As Variant
  7.     'Set the FileDialogFilters collection variable to
  8.     'the FileDialogFilters collection of the SaveAs dialog box.
  9. Set myDialog = Application.FileDialog(msoFileDialogOpen)
  10. With myDialog
  11.     .AllowMultiSelect = False
  12.      .Filters.Add "Text", "*.txt", 1
  13.     .Title = "Where Is The Text File?"
  14. '    Debug.Print Right(strSearchPath, Len(strSearchPath) - 10)
  15.     .InitialFileName = strSearchPath & strFNme
  16.     If .Show = -1 Then
  17.         For Each vrtSelectedItem In .SelectedItems
  18.             FindTxtFile = Trim(vrtSelectedItem)
  19.         Next vrtSelectedItem
  20.     Else
  21.     End If
  22.    Set myDialog = Nothing
  23. End With
  24. End Function
  25.  
May 9 '07 #2

JConsulting
Expert 100+
P: 603
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!

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim TxtName As String
  3. dim strLine as string
  4. TxtName = Application.GetOpenFilename("TextFiles (*.txt), *.txt", , "Select The file you wish to update:")
  5. open txtname for input as #1
  6. for I = 1 to 5
  7. Line Input #1, strLine
  8. next I
  9. do until eof(1)  
  10. Line Input #1, strLine '<--now you're on record 5...what do you want to do with it?
  11. 'do your stuff here
  12. loop
  13. close #1
  14.  
  15.  
May 10 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.