Get External Data dialog (1 Viewer)

W

WaiveIt

Registered User.
Local time
Yesterday, 20:11
Joined
Dec 2, 2013
Messages
58
Hello All,
I am asking my users to interact with the Get External Data dialog, and I'm trying to restrict what they can do.
Code: 
    DoCmd.RunCommand acCmdImportAttachText

I really like the mapping feature Access uses so I want to use that part of the Wizard, but I don't want my users to import data to the wrong table.

Can I skip the first page of the 'Get External Data' Dialog series?
see: AWF

or

Invoke the 'Import Text Wizard' without the previous dialog?
see: AWF2. I can get the file path from a File Picker dialog.

or if nothing else

Can I set the combobox to a default selection?


Thanks
 

Attachments

  • AWF.png
    AWF.png
    15.6 KB · Views: 144
  • AWF2.png
    AWF2.png
    8.3 KB · Views: 155
Last edited:
MarkK

MarkK

bit cruncher
Local time
Yesterday, 20:11
Joined
Mar 17, 2004
Messages
8,186
Yeah, you can manipulate that ImportExport spec completely. What you can do is set it up how you want, run it, and then save it, and once you've saved it it becomes a member of the CurrentProject.ImportExportSpecifications collection, which is a collection of Access.ImportExportSpecification objects. If you look at those in the object browser you can see the members, but take note of properties Name, and XML, and methods Execute and Delete. And the collection has an Add method, which takes a Name and the XML as parameters, so you can create one programmatically also.

What I've done with a few specs is just copied the XML after I save it, and then save that XML somewhere else. It's easy to find the import filename in there or the destination table, and the fieldnames and so on, so you can easily set up an XML stub that you can just plug in the source file name, destination table, and so.

So here a piece of where I save the xml in code . . .
Code: 
        BaseSpecXML = _
        "<?xml version=""1.0"" encoding=""utf-8"" ?> " & vbCrLf & _
        "<ImportExportSpecification Path=""[COLOR="Red"]<filespec>[/COLOR]"" xmlns=""urn:www.microsoft.com/office/access/imexspec""> " & vbCrLf & _
        "   <ImportExcel FirstRowHasNames=""true"" AppendToTable=""XLSRawData"" Range=""Sheet1$"" > " & vbCrLf & _
        "      <Columns PrimaryKey=""{Auto}""> " & vbCrLf & _
        "         <Column Name=""Col1"" FieldName=""FileID"" Indexed=""YESDUPLICATES"" SkipColumn=""false"" DataType=""Long"" /> " & vbCrLf
. . . and you can see in red I've stubbed in the source file name, which gets replaced like this . . .
Code: 
Private Property Get ImportSpecXML() As String
[COLOR="Green"]'   this exposes the final XML by inserting the source file name[/COLOR]
    ImportSpecXML = Replace(BaseSpecXML, "[COLOR="Red"]<filespec>[/COLOR]", m_spec)
End Property
. . . and you can execute your custom spec programmatically like . . .
Code: 
    With CurrentProject.ImportExportSpecifications
        .Add SPEC_NAME, ImportSpecXML
        .item(SPEC_NAME).Execute
    End With
But this bypasses their interface completely. You have to open your own filedialogs and so on, but you can have total programmatic control of an import/export too.
 
MarkK

MarkK

bit cruncher
Local time
Yesterday, 20:11
Joined
Mar 17, 2004
Messages
8,186
Actually, here's the whole class that I use to manage this import. You call it with code like . . .
Code: 
Private Sub ImportFile()
On Error GoTo handler
[COLOR="Green"]'   Imports the excel data.[/COLOR]
    Dim tmp As New cXLSFileImportSpec
    tmp.Load m_filespec
    tmp.Execute
    Exit Sub

handler:
    Err.Raise Err, Err.Source & " cXLSFileReader.ImportFile()"
End Sub
I think it's a good example of an object oriented approach to solving this problem. You still need to get the import filename on your own though.
Code: 
[SIZE="1"]Option Compare Database
Option Explicit

[COLOR="Green"]'******************************************************** Dec 14 2013 *****
'
'   This class encapsulates an import spec for XXXXXX's Excel files.  Pass
'   the filename to the Load function, and run the Execute method.
'
'**************************************************************************
[/COLOR]Private Const SPEC_NAME As String = "MyXLSImport"
Private m_cols
Private m_base
Private m_spec As String

[COLOR="Green"]'******************************************************** Dec 14 2013 *****
'
'   Properties
'
'**************************************************************************[/COLOR]
Private Property Get ImportSpecXML() As String
[COLOR="Green"]'   this exposes the final XML by inserting the source file name[/COLOR]
    ImportSpecXML = Replace(BaseSpecXML, "<filespec>", m_spec)
End Property

Private Property Get Columns()
[COLOR="Green"]'   this lets me edit the columns if the change in the source doc[/COLOR]
    If IsEmpty(m_cols) Then m_cols = Split("docid billto coname street1 street2 city state zip country terms custpono email phone item descrip orderqty ourprice linenum needby shipvia QTYtoShip Shipnote")
    Columns = m_cols
End Property

Private Property Get BaseSpecXML() As String
[COLOR="Green"]'   this constructs the base XML for the spec, but note that the filename property is not set[/COLOR]
    If IsEmpty(m_base) Then
        Dim var
        Dim tmp As String
        Dim i As Integer
        i = 1
        tmp = _
        "<?xml version=""1.0"" encoding=""utf-8"" ?> " & vbCrLf & _
        "<ImportExportSpecification Path=""<filespec>"" xmlns=""urn:www.microsoft.com/office/access/imexspec""> " & vbCrLf & _
        "   <ImportExcel FirstRowHasNames=""true"" AppendToTable=""XLSRawData"" Range=""Sheet1$"" > " & vbCrLf & _
        "      <Columns PrimaryKey=""{Auto}""> " & vbCrLf & _
        "         <Column Name=""Col1"" FieldName=""FileID"" Indexed=""YESDUPLICATES"" SkipColumn=""false"" DataType=""Long"" /> " & vbCrLf
        For Each var In Columns
            i = i + 1
            tmp = tmp & "         <Column Name=""Col" & i & """ FieldName=""" & var & """ Indexed=""NO"" SkipColumn=""false"" DataType=""Text"" /> " & vbCrLf
        Next
        m_base = tmp & _
        "      </Columns>" & vbCrLf & _
        "   </ImportExcel>" & vbCrLf & _
        "</ImportExportSpecification>"
    End If
    BaseSpecXML = m_base
End Property

[COLOR="Green"]'******************************************************** Dec 14 2013 *****
'
'   Methods
'
'**************************************************************************[/COLOR]
Function Load(filespec As String) As cXLSFileImportSpec
[COLOR="Green"]'   receives the filespec and returns the instance[/COLOR]
    m_spec = filespec
    Set Load = Me
End Function

Function Execute() As Boolean
[COLOR="Green"]'   clears the specs collection, adds this spec, and executes it[/COLOR]
On Error GoTo handler
    Clear
    With CurrentProject.ImportExportSpecifications
        .Add SPEC_NAME, ImportSpecXML
        .item(SPEC_NAME).Execute
    End With
    Execute = True
    Exit Function

handler:
    Err.Raise Err, Err.Source & " cXLSFileImportSpec.Execute"
End Function

Private Sub Clear()
[COLOR="Green"]'   clears any previously existing instance of the import spec[/COLOR]
    Dim i As Integer
    
    With CurrentProject.ImportExportSpecifications
        For i = .Count - 1 To 0 Step -1
            If .item(i).name = SPEC_NAME Then
                .item(i).Delete
                Exit For
            End If
        Next
    End With
End Sub

Function ToString() As String
    ToString = ImportSpecXML
End Function[/SIZE]
 
W

WaiveIt

Registered User.
Local time
Yesterday, 20:11
Joined
Dec 2, 2013
Messages
58
Thanks Lag.

You have outlined a completely thorough approach to importing data via code only.
lagbolt said:
But this bypasses their interface completely. You have to open your own filedialogs and so on, but you can have total programmatic control of an import/export too.
Click to expand...

However, The Import Text Wizard's mapping function is required to allow my users to import any *.csv they want. I do not have the ability to create an import spec for every *.csv.

I could ask them to rename field headings, remove fields that won't get imported, and organize their field headings to match the tmpImport, but not all users will have Excel (or another spreadsheet editor) or the knowhow to use it.

I will just resign to hiding every table other than tmpImport so the list of choices is limited to the only tmpImport.

I do appreciate your input!
Thanks!
 
You must log in or register to reply here.

Similar threads

D
Importing excel spreadsheet truncates field to 255 characters
Replies
16
Views
1,347
June7
June7
A
converting early binding to late binding
Replies
10
Views
425
Josef P.
J
M
DoCmd.TransferSpreadsheet no longer trims trailing spaces from field names after conversion to Office 365
Replies
12
Views
190
Gasman
Gasman
D
Error 31602 when using IMEX Data Tasks with isladogs frmManagingTasks Form
2
Replies
27
Views
842
isladogs
isladogs
N
Solved How to copy data only from the customer table from AZURE
Replies
5
Views
389
nector
N

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom