🚀 go-pugleaf

W dniu wtorek, 29 listopada 2016 18:30:01 UTC+1 użytkownik Jan Koc napisał:
> A jak Pan wykona taką operację (txt na csv), to proszę sprawdzić jak u Pana zachowa się Excel, gdy poleci mu Pan "zapisz jako csv". U mnie informuje o utracie formuł. Może mam źle ustanowione jakieś właściwości exelowskie..
> Jan

Może konwersja musi być jawna a nie tylko przez zmianę rozszerzenia.

Wygrzebałem gdzieś jeszcze to:
1. Zapisz w Module swojego Accessa taką funkcję:

Sub txt2csv()
    Dim fnam As String
    Dim spath As String
    Dim ftxt As String
    Dim wb As Workbook
    Dim cel As Range
    Dim txts As String
    spath = "X:\"
    fnam = Dir(spath & "*.txt")
    Do While fnam <> ""
        Open spath & fnam For Input As #1
        ftxt = Input(LOF(1), 1)
        Close 1
        ftxt = Replace(ftxt, "|", ",")
        Open spath & Replace(UCase(fnam), ".TXT", ".csv") For Output As #1
        Print #1, ftxt
        Close 1
        Set wb = Workbooks.Open(spath & Replace(UCase(fnam), ".TXT", ".csv"))
        For Each cel In wb.Sheets(1).Range("A2:A" & wb.Sheets(1).Range("A2").End(xlDown).Row)
        txts = Right("0" & cel, 8)
        cel = CVDate(Left(txts, 2) & "/" & Mid(txts, 3, 2) & "/" & Right(txts, 4))
        Next cel
        wb.Close True
        fnam = Dir
    Loop
End Sub

xxxxxxxxx

2. zmodyfikowany kod pod przyciskiem:
 Dim db As DAO.Database, qd As DAO.QueryDef 
    Dim fd As FileDialog 
    Dim exportSQL As String 
    Dim i As Integer 
    
    Set fd = Application.FileDialog(msoFileDialogSaveAs) 

    Set db = CurrentDb 
exportSQL = "SELECT FIRMA.Nazwa AS S_Name, FIRMA.Ulica AS S_Address, FIRMA.Kod AS S_Zip, FIRMA.Miasto AS S_City, FIRMA.telefon AS S_Phone, FIRMA.e_mail AS S_Email, 'Grzegorz Kociumbas' AS S_Contact_Person, [Export DPD2].P_Cat_KP, [Export DPD2].P_Cat_5, [Export DPD2].P_Cat_10, [Export DPD2].P_Cat_20, [Export DPD2].P_Cat_30, [Export DPD2].P_Cat_50, [Export DPD2].R_Name, [Export DPD2].R_Address, [Export DPD2].R_Zip, [Export DPD2].R_City, [Export DPD2].R_Phone, [Export DPD2].R_Email, [Export DPD2].R_Contact_Person, [Export DPD2].P_Courier_Name, [Export DPD2].P_Comment, [Export DPD2].P_Content, [Export DPD2].A_Terminal, [Export DPD2].P_Cod, [Export DPD2].Time FROM FIRMA, [Export DPD2] WHERE ((([Export DPD2].Wydrukowane)=2));" 
  'Check to see if querydef exists 
    For i = 0 To (db.QueryDefs.Count - 1) 
        If db.QueryDefs(i).Name = "tmpExport" Then 
            db.QueryDefs.Delete ("tmpExport") 
            Exit For 
    End If 
    Next i 

    Set qd = db.CreateQueryDef("tmpExport", exportSQL) 

    'Set intial filename 
    fd.InitialFileName = "X:\export_" & Format(Date, "yyyymmdd") & ".txt" 

    If fd.show = True Then 
        If Format(fd.SelectedItems(1)) <> vbNullString Then 
            DoCmd.TransferText acExportDelim, "Export Specyfikacja eksportu", "tmpExport", fd.SelectedItems(1), True, , 852 
        End If 
    End If 

    'Cleanup 
    db.QueryDefs.Delete "tmpExport" 
    db.Close 
    Set db = Nothing 
    Set qd = Nothing 
    Set fd = Nothing 

' jawnie konwertowane txt na csv
Call txt2csv

End Sub


Proszę spróbować jeszcze to.
MF