Can you amend an import spec via VBA? (1 Viewer)

t00ley

Registered User.
Local time
Today, 09:13
Joined
Jul 5, 2011
Messages
18
Hi All,

Is there a way to amend the file path & name of an existing import spec, via VBA? Nothing else should change.

FYI, this is to future proof the specs, so if the file location or name changes (which is highly likely this year due to changes), the user can remap by selecting the file via a FilePicker (Application.FileDialog).

I've already setup the FilePicker, which stores the full path & filename in a database parameter table and need to understand how changes can be applied via VBA without the need to manually change.

Thanks

Tooley
 

sneuberg

AWF VIP
Local time
Today, 01:13
Joined
Oct 17, 2014
Messages
3,506
This Web page has some code e.g.

Code:
Public Sub fixImportSpecs(myTable As String, strFind As String, strRepl As String)
    Dim mySpec As ImportExportSpecification    
    Set mySpec = CurrentProject.ImportExportSpecifications.Item(myTable)    
    mySpec.XML = Replace(mySpec.XML, strFind, strRepl)
    Set mySpec = Nothing
End Sub
that might get you started. I found that the myTable argument is actually the name of the import specification. If you keep track of the last file path then this function is probably all you will need.
 

t00ley

Registered User.
Local time
Today, 09:13
Joined
Jul 5, 2011
Messages
18
OK I may have found a solution, but will test over the weekend.

In summary, I understand I need to:

1.loop through ImportExportSpecifications to find the spec by name.
2.When found, update the XML "Path" parameter.

Sounds simple enough

Tooley
 

t00ley

Registered User.
Local time
Today, 09:13
Joined
Jul 5, 2011
Messages
18
Thanks Sneuberg, looks inline with what I'd found. I'll try your code over the weekend.

Much appreciated
 

MarkK

Super Moderator
Local time
Today, 01:13
Joined
Mar 17, 2004
Messages
7,761
Here's a class I wrote that completely builds an import spec from scratch and executes it. You can pass in the filename. You can edit the list of columns very easily.
Code:
[SIZE="1"]Option Compare Database
Option Explicit

'******************************************************** Dec 14 2013 *****
'
'   This class exposes an import spec for customer's Excel files.  Pass
'   the filename to the Load function.
'
'**************************************************************************
Private Const SPEC_NAME As String = "CustomerXLSImport"
Private m_cols
Private m_base
Private m_spec As String

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

Private Property Get Columns()
'   this lets me edit the columns if they change in the source doc
    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
'   this constructs the base XML for the spec, but note that the filename property is not set
    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

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

Function Execute() As Boolean
'   clears the specs collection, adds this spec, and executes it
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()
'   clears any previously existing instance of the import spec
    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]
What you can do is create your spec manually and then copy the XML out of it. Then use that XML in this class's BaseSpecXML property. But maybe it gives you an idea about how to proceed.
 

sneuberg

AWF VIP
Local time
Today, 01:13
Joined
Oct 17, 2014
Messages
3,506
This looked like an opportunity to play with the XML DOM and after putzing with this for some time I've come up with this function:

Code:
Private Sub SetNewPath(ImportExportSpecName As String, NewPath As String)
'Uses the Microsoft XML library reference
Dim mySpec As ImportExportSpecification
Dim xmlDoc As MSXML2.DOMDocument
Set xmlDoc = New MSXML2.DOMDocument
xmlDoc.async = False
xmlDoc.validateOnParse = False
Set mySpec = CurrentProject.ImportExportSpecifications.Item(ImportExportSpecName)
xmlDoc.loadXML (mySpec.XML)
xmlDoc.documentElement.setAttribute "Path", NewPath
CurrentProject.ImportExportSpecifications.Item(ImportExportSpecName).XML = xmlDoc.XML
Set xmlDoc = Nothing
Set mySpec = Nothing

End Sub
which changes the path in the import spec. It's fairly simple as the attribute that needs to be changed is in the root node.

I've attached a zip file which contains the database in which I tested this and a couple of excel spreadsheets that I was using for testing. This code needs some error handling for things like non existent or invalid import specs.
 

Attachments

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom