vb HELP please

[bobaol]

transfer text
Hi, I am trying to do the following on MS Access using the TransferText Action.
In TransferText Action, Transfer Type =Import Delimited, Specification Name = RC1, Table Name = ABC1, File Name = C:\arc_file1.txt, Has Field Names = NO, HTML Table Name = Blank.

OK, my question. I want to use all the following as described in the above except for File Name = C:\arc_file1.txt because I want to pick and choose my own file. Next month, it will be arc_file2.txt and so on. I know I can use a Command->Import option to get a dialogue box, but that does not allow me to retain Specification Name = RC1. Ok, what is the VB code to get a dialogue box and retain all the other specification as described above for the TransferText Action?


ps. i have been looking a lot for this. I found this on the web: DoCmd.TransferText acImportDelim, , "NewWork", "D:\Work.txt", but the problem is that I want a dialogue box for me to pick "D:\Work.txt" or pick "D:\Work2.txt" rather than me going into the code and change it everytime.

Thanks in advance.

 

[NateO]

Think you might get better responses in the Access forum, I'll move it there.

Regards,
Nate

 

[Gates Is Antichrist]

I don't know offhand but Excel VBA has

Application.GetSaveAsFilename ...

in case that leads you to something.

Then for that, you might look into using Excel functions in Access - my memory is really foggy here, but I seem to recall something LIKE Application.WorksheetFunctions. something or other.
I'm sorry I don't know more offhand. Besides, I don't know if it will yield your solution, or is even meritorious - but rather than silence, it'll give you something to think about

 

[smozgur]

Hi bobaol,

I adapted the technique at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnima01/html/ima1101.asp

for your needs.

Please follow the instructions:

1- Create a new Class Module in VBA in your Access Project
2- Change its name to clsOpenDialog in properties window
3- Copy and paste the following code into this new Class Module

Private Declare Function GetOpenFileName Lib _
    "comdlg32.dll" Alias "GetOpenFileNameA" _
    (pOpenfilename As OPENFILENAME) As Long
Private Declare Function GetSaveFileName Lib _
    "comdlg32.dll" Alias "GetSaveFileNameA" _
    (pOpenfilename As OPENFILENAME) As Long

Private Type OPENFILENAME
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    lpstrFilter As String
    lpstrCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    lpstrFile As String
    nMaxFile As Long
    lpstrFileTitle As String
    nMaxFileTitle As Long
    lpstrInitialDir As String
    lpstrTitle As String
    Flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    lpstrDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type

Private mstrFileName As String
Private mblnStatus As Boolean

Public Property Let GetName(strName As String)
    mstrFileName = strName
End Property

Public Property Get GetName() As String
    GetName = mstrFileName
End Property

Public Function OpenFileDialog(lngFormHwnd As Long, _
    lngAppInstance As Long, strInitDir As String, _
    strFileFilter As String) As Long

Dim OpenFile As OPENFILENAME
Dim X As Long

With OpenFile
    .lStructSize = Len(OpenFile)
    .hwndOwner = lngFormHwnd
    .hInstance = lngAppInstance
    .lpstrFilter = strFileFilter
    .nFilterIndex = 1
    .lpstrFile = String(257, 0)
    .nMaxFile = Len(OpenFile.lpstrFile) - 1
    .lpstrFileTitle = OpenFile.lpstrFile
    .nMaxFileTitle = OpenFile.nMaxFile
    .lpstrInitialDir = strInitDir
    .lpstrTitle = "Open File"
    .Flags = 0
End With
    
X = GetOpenFileName(OpenFile)
If X = 0 Then
    mstrFileName = "none"
    mblnStatus = False
Else
    mstrFileName = Trim(OpenFile.lpstrFile)
    mblnStatus = True
End If
End Function

Public Property Let GetStatus(blnStatus As Boolean)
    mblnStatus = blnStatus
End Property

Public Property Get GetStatus() As Boolean
    GetStatus = mblnStatus
End Property

Copy and paste following code into YOUR module (form or standard module):

Function GetFileName(initDir As String)
Dim cdlg As New clsOpenDialog
Dim lngFormHwnd As Long
Dim lngAppInstance As Long
Dim strInitDir As String
Dim strFileFilter As String
Dim lngResult As Long
    
    lngFormHwnd = 0
    lngAppInstance = Application.hWndAccessApp
    strInitDir = initDir
    
    strFileFilter = "Text Files (*.txt)" & Chr(0) & "*.txt" & Chr(0)
            
    lngResult = cdlg.OpenFileDialog(lngFormHwnd, lngAppInstance, strInitDir, strFileFilter)
    
    If cdlg.GetStatus = True Then
        GetFileName = cdlg.GetName
    Else
        GetFileName = ""
    End If
End Function

When you need to get the open dialog use the following code:

Sub YourSubName()
'....
'....

Dim strFileName As String
  'Change path information as you need, it is "D:\" below
  strFileName = GetFileName("D:\")

  if strFileName = "" then 
     'Operation Cancelled, no file selected
     Exit Sub 'Or whatever you need
  Else
     DoCmd.TransferText acImportDelim, "RC1", "ABC1", strFileName
  End If

'....
'....
End Sub

This would help you. And follow the instructions at the link if you need more explanations.

Or another method (which is short but I don't suggest, just for information since Gates Is Antichrist mentioned this)

Sub GetOpenFileByUsingExcelObject()
Dim xls As Object
Dim strFileName
    Set xls = CreateObject("Excel.Application")
    'Change directory where you need
    ChDir ("D:\")
    strFileName = xls.GetOpenFileName("Text Files (*.txt), *.txt")
    If strFileName = "" Then
       'Operation Cancelled, no file selected
       Exit Sub 'Or whatever you need
    Else
       DoCmd.TransferText acImportDelim, "RC1", "ABC1", strFileName
    End If
    xls.Quit
    Set xls = Nothing
End Sub

I wouldn't use Excel method, because it will open Excel application to use GetOpenFileName Method (not visible but in memory) . And even it seems shorter code, it takes really more time than the API one above.

I hope this helps.
Suat

 

[Philem]

<SNIP>
ps. i have been looking a lot for this. I found this on the web: DoCmd.TransferText acImportDelim, , "NewWork", "D:\Work.txt", but the problem is that I want a dialogue box for me to pick "D:\Work.txt" or pick "D:\Work2.txt" rather than me going into the code and change it everytime.

Thanks in advance.

Hullo. Your line of code can be modified thusly:

DoCmd.TransferText acImportDelim,,'NewWork', variablename

where variable name comes from an InputBox statement earlier in the code.

HTH

P

 

[Gates Is Antichrist]

Or another method (which is short but I don't suggest, just for information since Gates Is Antichrist mentioned this)

...

I wouldn't use Excel method, because it will open Excel application to use

Thank you for that great help.

I took a chance that he might not mind working through Excel, or it would already be active; he posted in the Excel section, at MrExcel At any rate I agree with your comments, and appreciate your help.