Import & Export limitations

[breilly192]

I created an import macro utilizing the TransferText action, however, it requires the specific file name to be typed into the Action Arguments. Can an InPut Box be used to prompt the user to select the file they want and then incorporate that into the macro?

On the other end, exporting a file with a macro that uses the OutputTo action the file format is limited to .xls if I want to use the exported data as a load file for another system. When I tried .txt format the exported file included field borders and other report-type features that make it unusable as a data file. I'd prefer to have a tab-delimited output file but it isn't an option in the format selections within Access action arguments for the macro. I can export to .xls and then save it as tab delimited, but that is a step I'd like to eliminate in order to automate this as much as possible for the user.

Any help here would be appreciated. If it can be done within Access macros would be ideal, however, if it can be done through VB modules that would be good, as long as it's not too complex for my non-programmer mind to grasp.

 

[BarbDataRUs]

Breilly, did you ever get a solution to your transfer text import problem (ability to have the user specify the filename)? This is EXACTLY the problem I'm trying to find the solution for so would be very happy to know if you had any luck with it.

Thanks for any suggestions you might have!

 

[mmorancbt]

The following assumes Access 2000 or Access XP. I cannot vouch, nor do I remember much on earlier versions.

First, to have someone select a file, use code similar to that below.

This will give you the name a file to use in the transfer.text export....

Private Sub btnSelectFile_Click()
Dim dlgOpen As FileDialog
Dim strDocName
strDocName = ""
If Not IsNull(doc_path.Value) Then
strDocName = doc_path.Value
End If

Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)

With dlgOpen
.InitialFileName = strDocName
.AllowMultiSelect = False
.Show
End With
If dlgOpen.SelectedItems.Count > 0 Then
strDocName = dlgOpen.SelectedItems.Item(1)
doc_path.Value = strDocName
End If

End Sub


While macros don't except variables, the same syntax for exporting data exist using the following code in a module.

DoCmd.TransferText

It will guide you through the necessary elements. You can the put the path/name of the file selected above into that string.

You can do much of the same thing using the transfertext to export data. However, to have the type of flexibility you require, you need to use VBA, not macros. Once again:

DoCmd.TransferText Highlight the words transfertext and press F1. The VBA help on the transfertext method is pretty good.

I hope this helps.


Matthew Moran