Code to export each query to separate csv BUT with certain conditions

[jaryszek]

Ok thank you. I am still confused.

I created specCSV from one query.
And saved it manually. And now i can change path and save into another file with your function.

But how can i pass query which i want to export?
I have qery "qryTest" and specifiction is "specCSV" for it.
Now i want to export another query - "queryTest2" with the same spec.

How can i pass query into your function ?

Best,
Jacek

 

[arnelgp]

ok I revised the code to accept the query/table name:

Public Sub RunSpecOnOtherFile(ByVal SpecName As String, ByVal objName As String, _
                                    ByVal ObjType As String, _
                                    ByVal Path As String)
'
' arnelgp
'
' runs import/export specs to another file
'
' SpecName      = name of import/export spec
' objName       = name of query or table to import/export
' ObjType       = either "Query" or "Table" to import/export
' Path          = Path + filename(including extension) to create
'
    Dim objSpec As ImportExportSpecification
    Dim strXML As String
    Dim intStartPos As Integer, intEndPos As Integer
    
    Set objSpec = CurrentProject.ImportExportSpecifications.Item(SpecName)
    ' get the xml string
    strXML = objSpec.xml
    'Debug.Print
    'Debug.Print strXML
    'replace the Path part
    intStartPos = InStr(1, strXML, "Path")
    intStartPos = InStr(intStartPos, strXML, "=")
    intEndPos = InStr(intStartPos, strXML, "xmlns") - 1
    ' insert the new filename between intstartpos and intendpos
    strXML = Left(strXML, intStartPos) & " " & _
            """" & Path & """" & _
            Mid(strXML, intEndPos)
    'Debug.Print
    'Debug.Print strXML
    ' replace the objName
    strXML = RegExpReplace(strXML, "AccessObject=" & Chr(34) & "[\w]{1,}" & Chr(34), "AccessObject=" & Chr(34) & objName & Chr(34))
    'Debug.Print
    'Debug.Print strXML
    ' replace the ObjType
    strXML = RegExpReplace(strXML, "ObjectType=" & Chr(34) & "[\w]{1,}" & Chr(34), "ObjectType=" & Chr(34) & ObjType & Chr(34))
    'Debug.Print
    'Debug.Print strXML
    ' replace the xml of the spec to the new strXML
    objSpec.xml = strXML
    ' run the specification
    objSpec.Execute
    ' close it
    Set objSpec = Nothing
End Sub

''''''''''''
' helper
''''''''''''
Public Function RegExpReplace(ByVal WhichString As String, _
        ByVal Pattern As String, _
        ByVal ReplaceWith As String, _
        Optional ByVal IsGlobal As Boolean = True, _
        Optional ByVal IsCaseSensitive As Boolean = True) As String
    With CreateObject("vbscript.regexp")
        .Global = IsGlobal
        .Pattern = Pattern
        .IgnoreCase = Not IsCaseSensitive
        RegExpReplace = .Replace(WhichString, ReplaceWith)
    End With
End Function

 

[sxschech]

Sounds like you have a solution. For my own understanding, curious about what the code I posted didn't provide. Only reason asking is that in reading the additional posts, seems that the other method requires a manual setup. The code I posted can directly export the data without having to manually create a spec first. Are there additional criteria/formatting issues not being addressed? Thanks.

 

[jaryszek]

thank you Guys very much.

sxschech - your method is ok but this is workaround with opening recordset - Access has native methods so i want to use them as much as i can. Looping through recordset can be slow.

Pat Hartman -o nice, good to know. i can not post database because of fragile data.
But i think i can handle with the new spec method and arnel code. I will test and let you know.
About old spec - there are specific columns there and what if another table which i want to export will have difference number of columns and columns types? It is to handle in old spec?

Best,
Jacek

 

[jaryszek]

Ok thank you.

By

the format doesn't change

you mean fields format?
Like Number or String?

And most important, the number of fields? If i have spec where i am importing 10 fields and want to use it on table where i have 2 fields - will it work?

Best,
Jacek

 

[jaryszek]

o wow.

So arneglgp code will do not work also.

Pat your method with old way will workaround this?
Or i have to use sxschech code ?

Jacek

 

[jaryszek]

arnelgp so your code will only also on the same spec?

Best,
Jacek

 

[arnelgp]

yes, on same spec.

 

[jaryszek]

Ok Guys so to sum up we have only 2 solutions:
1) Add manually for each exported query spec - and have it separately and use code to run them all, like "Spec_TableName" in the loop.
2) Use code to export recordset to external txt file.

Am i right?

 

[jaryszek]

Ok thank you Pat!!!

I have to only change field separator and decimal symbol but my columns are changing - i do not have the same number of columns in each table...

Best,
Jacek

 

[jaryszek]

Pat,

what do you mean by formats? I have about 15 queries with different number of columns and the same format (change decimal point and field separator only).

Best,
Jacek

 

[jaryszek]

thank you Pat.

I will do this. But in case of more than 15 queries to export - only solution is to use exporting recordset to txt code - am i correct?

arnelgp code referring only to the situation that tables are the same with the same number of fields am i right?

Best,
Jacek