Automatically import txt file using a browse function and import specs 1

[Jennpen1]

WhatI need for the user of my program to click on a button on the switchboard and it automatically imports a delimited text file. I know that is easy to do with a macro and the TransferText option, however my problem is a little more involved. Every user will be importing their file from a different location. Which means, when they click on the button I need the a browse function to appear. This is the code I have written to do that:

On Error GoTo Error_OpenCmd
DoCmd.RunCommand acCmdImport
Exit_OpenCmd:
Exit Sub
Error_OpenCmd:
MsgBox Err & ":" & Err.Description
Resume Exit_OpenCmd


Once the file is found and the user clicks "import" it starts to go through all of the import specifications. I want to set all of the speciications for the user so that there are no problem importing the file. So basically what I need is for the user to be able to click on a button on the switchboard which will then open a browse function, and when the file is found, then click on import and the file is automatically imported into a table. I know this has to be possible, I just have no idea how to do it. I would truly appreciate any help.

 

[ToeShot]

Below is some code to help you. This may not be the best way but it works.

What the code does is open a flat text file then takes it a line at a time searching each character until it hits the delimiter or the end of line. Then it will take that value and store it into your table field then advance to the next field in your table and the next value in your file. If you need help let me know. The code is fairly simple but I don't know your coding experience.

Option Compare Database
Option Explicit

Function GetFlatFile()

Dim CurrentTextFile
Dim LineLength As Integer
Dim CharCounter As Integer
Dim CharPointer As Integer
Dim FieldNum As Integer
Dim CurrentChar As String
Dim TempValue As String
Dim CurrentLine As String
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("Fields", dbOpenDynaset)
rst.AddNew

'If you need the Open File Dialog Box code let me know. It would be a better
'Way to code it.
'CurrentTextFile = APIOpenFile.ap_FileOpen(, "*.txt", "Text&quot

'If you use the above Procedure, replace this
'"C:\windows\desktop\New Text Document.txt"
'with CurrentTextFile. or just change the path to your file location
Open "C:\windows\desktop\New Text Document.txt" For Input As #1

Line Input #1, CurrentLine
FieldNum = 0

Do While Not EOF(1)
FieldNum = 0
LineLength = Len(CurrentLine)
CharCounter = 1
CharPointer = 0
CurrentChar = Left(CurrentLine, CharCounter)

If Not CurrentLine = "" Then
While CharPointer <= LineLength
CurrentChar = Left(CurrentLine, CharCounter)
CurrentChar = Right(CurrentChar, 1)

'change Chr(32) with your file delimiter.
If CurrentChar = Chr(32) Or CharPointer = LineLength Then
rst.Fields(FieldNum).Value = TempValue
FieldNum = FieldNum + 1
TempValue = &quot;&quot;
Else
TempValue = TempValue & CurrentChar
End If

CharCounter = CharCounter + 1
CharPointer = CharPointer + 1
Wend
rst.Update
rst.AddNew
End If

Line Input #1, CurrentLine

Loop

Close #1

End Function



Below is the OpenFile Dialog Call. Save it as a module APIOpenFile:


Option Compare Database
Option Explicit

Private Declare Function ap_GetOpenFileName Lib &quot;comdlg32.dll&quot; Alias _
&quot;GetOpenFileNameA&quot; (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

Public Function ap_FileOpen(Optional strTitle As String = &quot;Open File&quot;, _
Optional strFileName As String = &quot;&quot;, _
Optional strFilter As String = &quot;&quot As String

Dim OpenFile As OPENFILENAME
Dim lngReturn As Long

If Len(strFileName) = 0 Then
strFileName = String(255, 0)
End If

If Len(strFilter) = 0 Then
strFilter = &quot;Access Databases (*.mdb)&quot; & Chr(0) & _
&quot;*.mdb&quot; & Chr(0)
End If

OpenFile.lStructSize = Len(OpenFile)

OpenFile.lpstrFilter = strFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = strFileName + _
Space(255 - Len(strFileName))
OpenFile.nMaxFile = 255
OpenFile.lpstrFileTitle = strTitle
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = CurrentProject.Path
OpenFile.lpstrTitle = strTitle
OpenFile.flags = 0

ap_GetOpenFileName OpenFile

ap_FileOpen = Left(OpenFile.lpstrFile, _
InStr(OpenFile.lpstrFile, Chr$(0)) - 1)

End Function





HTH
Dave
Dave
ToeShot@Hotmail.com
Today Is Tomorrows Yesterday. So Why Wait

 

[Jennpen1]

I am slightly confused, and this is probably because of my lack of coding experience. However, do I use code as a replacement for the code I wrote or as a supplement to it? If I am supposed to use it along with it then I am not sure how the codes relate. You mentioned something about sending me the Open File Dialog Box, and I think that is what I will need to run this code you have sent me. I apprecaite your help so much.

 

[ToeShot]

Sorry took so long to get back, had a problem with my monitor. It umm took Flying lessons. Well anyways the first part of my response (the Code) is used in place of what you have and you would put that behind a button in the OnClick_Event()

The Second part is the open file dialog. You paste this into a new module and save the module as APIOpenFile.

Use the code below I made the necessary changes I believe for it to work for you. You will have to select the corect table you want to use. Just copy from here and paste into your VBA Editor.


Option Compare Database
Option Explicit

Function GetFlatFile()

Dim CurrentTextFile
Dim LineLength As Integer
Dim CharCounter As Integer
Dim CharPointer As Integer
Dim FieldNum As Integer
Dim CurrentChar As String
Dim TempValue As String
Dim CurrentLine As String
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset(&quot;Fields&quot;, dbOpenDynaset)
rst.AddNew

'This will will bring up the open file dialog box.
CurrentTextFile = APIOpenFile.ap_FileOpen(, &quot;*.txt&quot;, &quot;Text&quot
Open CurrentTextFile For Input As #1

Line Input #1, CurrentLine
FieldNum = 0

Do While Not EOF(1)
FieldNum = 0
LineLength = Len(CurrentLine)
CharCounter = 1
CharPointer = 0
CurrentChar = Left(CurrentLine, CharCounter)

If Not CurrentLine = &quot;&quot; Then
While CharPointer <= LineLength
CurrentChar = Left(CurrentLine, CharCounter)
CurrentChar = Right(CurrentChar, 1)

'change Chr(44) with your file delimiter.
If CurrentChar = Chr(44) Or CharPointer = LineLength Then
rst.Fields(FieldNum).Value = TempValue
FieldNum = FieldNum + 1
TempValue = &quot;&quot;
Else
TempValue = TempValue & CurrentChar
End If

CharCounter = CharCounter + 1
CharPointer = CharPointer + 1
Wend
rst.Update
rst.AddNew
End If

Line Input #1, CurrentLine

Loop

Close #1

End Function




HTH

if you need more help just let me know.

Dave
ToeShot@Hotmail.com
Today Is Tomorrows Yesterday. So Why Wait