Problem Importing Fixed Length Text File Using VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I receive this file from another organization that contains several fields
in a Fixed Length format. I can import the file manually using the wizard to
a local table in Access2k3. I saved and regularly use a specification to do
this.

I am trying to automate the process so I can massage the data once imported
and I have the following code:

Public Sub UpdateDaily()
Dim rst As ADODB.Recordset
Dim ImpFile As FileDialog
Dim strFileDate As String, strFileName As String

DoCmd.SetWarnings False
Set ImpFile = Application.FileDialog(msoFileDialogFilePicker)
With ImpFile
.AllowMultiSelect = False
.ButtonName = "Import File"
.Filters.Add "Text Files", "*.txt", 1
.FilterIndex = 1
.InitialFileName = "D:\My Path\*.txt"
.Title = "Select the File to Import"
.Show
End With
strFileName = ImpFile.SelectedItems.Item(1)
Set ImpFile = Nothing
DoCmd.TransferText acImportDelim, "custom Import Spec", _
"custintl_previous", strFileName, False
....

The problem I have is that when I execute the code, the import only brings
in the first column of data. All the other columns are empty and I can't
figure out what goes wrong. When I stop the code and try the import manually,
it works fine and I get no error messages or warnings.

Any assistance is greatly appreciated!

Fermon
 
I realize now that I set the first option in the TransferText to
acImportDelim when it should have been acImportFixed. The code works now as
it should!

I'll go back to banging my head against the wall...

Thanks,

Fermon
 
Back
Top