Editing an external text file is it possible?

isladogs said:
Do I assume you didn't try what I suggested after all?

That took me less than 2 minutes to download, import using the wizard with no reported errors, save the import specification and create a sub for future use.

I'll leave you to check if the lines listed are correct but as there were no import errors I see no reason why not
Click to expand...
Sorry
Not yet.
 
isladogs said:
Do I assume you didn't try what I suggested after all?

That took me less than 2 minutes to download, import using the wizard with no reported errors, save the import specification and create a sub for future use.

I'll leave you to check if the lines listed are correct but as there were no import errors I see no reason why not
Click to expand...
I tried using the Wizard and create a spec.
Seems to work fine.

Is there any way to tell it where the file to import exist?
or will it open a file open dialog if it won't find the file?
 
Unfortunately you can't edit an existing data task AFAIK.
When you create your own task, you provide the location of the file to be imported.
 
isladogs said:
Unfortunately you can't edit an existing data task AFAIK.
When you create your own task, you provide the location of the file to be imported.
Click to expand...

I thought I saw someone posted a function for modifying a saved import task. I just can't remember who.
 
I did some search and found the specs are stored in the MsysIMEXspecs, MsysIMEXcolumns system tables.
They can't be edited directly (adding/deleting a record is possible) but they can be edited using code.
Unfortunately I can't find the import file location in there :(

I also found some code here https://stackoverflow.com/questions/143420/how-can-i-modify-a-saved-microsoft-access-2007-or-2010-import-specification
Code: 
Below are three functions you can use to alter and use the MS Access 2010 Import Specification. The third sub changes the name of an existing import specification. The second sub allows you to change any xml text in the import spec. This is useful if you need to change column names, data types, add columns, change the import file location, etc.. In essence anything you want modify for an existing spec. The first Sub is a routine that allows you to call an existing import spec, modify it for a specific file you are attempting to import, importing that file, and then deleting the modified spec, keeping the import spec "template" unaltered and intact. Enjoy.

Public Sub MyExcelTransfer(myTempTable As String, myPath As String)
On Error GoTo ERR_Handler:
    Dim mySpec As ImportExportSpecification
    Dim myNewSpec As ImportExportSpecification
    Dim x As Integer

    For x = 0 To CurrentProject.ImportExportSpecifications.Count - 1
    If CurrentProject.ImportExportSpecifications.Item(x).Name = "TemporaryImport" Then
        CurrentProject.ImportExportSpecifications.Item("TemporaryImport").Delete
        x = CurrentProject.ImportExportSpecifications.Count
    End If
    Next x
    Set mySpec = CurrentProject.ImportExportSpecifications.Item(myTempTable)
    CurrentProject.ImportExportSpecifications.Add "TemporaryImport", mySpec.XML
    Set myNewSpec = CurrentProject.ImportExportSpecifications.Item("TemporaryImport")

    myNewSpec.XML = Replace(myNewSpec.XML, "\\MyComputer\ChangeThis", myPath)
    myNewSpec.Execute
    myNewSpec.Delete
    Set mySpec = Nothing
    Set myNewSpec = Nothing
    exit_ErrHandler:
    For x = 0 To CurrentProject.ImportExportSpecifications.Count - 1
    If CurrentProject.ImportExportSpecifications.Item(x).Name = "TemporaryImport" Then
        CurrentProject.ImportExportSpecifications.Item("TemporaryImport").Delete
        x = CurrentProject.ImportExportSpecifications.Count
    End If
    Next x
Exit Sub    
ERR_Handler:
    MsgBox Err.Description
    Resume exit_ErrHandler
End Sub

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


Public Sub MyExcelChangeName(OldName As String, NewName As String)
    Dim mySpec As ImportExportSpecification
    Dim myNewSpec As ImportExportSpecification
    Set mySpec = CurrentProject.ImportExportSpecifications.Item(OldName)    
    CurrentProject.ImportExportSpecifications.Add NewName, mySpec.XML
    mySpec.Delete
    Set mySpec = Nothing
    Set myNewSpec = Nothing
End Sub
 
The system tables MSysIMEXSpecs and MSysIMEXColumns were used for Access versions prior to 2010.
They still exist but for backwards compatibility only.

The new data tasks are stored as XML. See this thread that I started yesterday. https://www.access-programmers.co.uk/forums/showthread.php?p=1650466&posted=1#post1650466

Anyway, more to the point, do you now have the solution for your text file import?

In answer to an earlier question, if the location of the file specified for import doesn't exist, an error message occurs but no file open dialog. Easiest to just create a new task
 
Last edited:
isladogs said:
The system tables MSysIMEXSpecs and MSysIMEXColumns were used for Access versions prior to 2010.
They still exist but for backwards compatibility only.

The new data tasks are stored as XML. See this thread that I started yesterday. https://www.access-programmers.co.uk/forums/showthread.php?p=1650466&posted=1#post1650466

Anyway, more to the point, do you now have the solution for your text file import?

In answer to an earlier question, if the location of the file specified for import doesn't exist, an error message occurs but no file open dialog. Easiest to just create a new task
Click to expand...

Thank you for the information
Your code is similar to the code I posted.
I guess I have a full solution for what I need, as I can set the Spec's file path :)
 
Glad you're happy now.
DBG & I were pleased to have helped

I'm in the process of creating a related item for the code repository
 
This code works perfect for modifying the file's path
Code: 
Public Sub pbModifyImportSpecPath(strNewPath As String, strSpecName As String)

Dim strSpecXML As String
Dim x As Integer
Dim x1 As Integer
Dim x2 As Integer


strSpecXML = CurrentProject.ImportExportSpecifications.Item(strSpecName).XML
Debug.Print CurrentProject.ImportExportSpecifications.Item(strSpecName).XML

x = InStr(LCase(strSpecXML), LCase("Path="))
x1 = InStr(x, LCase(strSpecXML), Chr(34))
x2 = InStr(x1 + 1, LCase(strSpecXML), Chr(34))

strSpecXML = Left(strSpecXML, x1) & strNewPath & Right(strSpecXML, Len(strSpecXML) - x2 + 1)

CurrentProject.ImportExportSpecifications.Item(strSpecName).XML = strSpecXML
Debug.Print CurrentProject.ImportExportSpecifications.Item(strSpecName).XML

End Sub
 
You must log in or register to reply here.

Similar threads

P
Solved winscp
2
Replies
23
Views
755
prasadgov
P
A
Solved How to define a conditional string in VBA
Replies
3
Views
358
cheekybuddha
cheekybuddha
E
open saved import external data vba code
2
Replies
23
Views
669
DickyP
DickyP
D
Call a button's action from VBA
Replies
4
Views
195
Dave E
D
oxicottin
Solved Out Of Memory error when emailing
Replies
8
Views
257
oxicottin
oxicottin

Users who are viewing this thread

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