[VBA] M.b.v access -> excel sheets manipuleren - Client software algemeen

dinsdag 13 mei 2008 08:16

Acties:

Topicstarter

Heren, misschien kunnen jullie me helpen. Ik ben bezig een job te automatiseren, maar tot op heden lukte alles behalve 1 stapje. Dit stapje houd in dat de inhoud van een sheet wordt doorzocht en indien nodig wordt gereplaced. De volgende code heb ik:

code:

Sub txt_bestanden_maken()
    Dim MyFolder As String
    Dim I As Long
    Dim LastRow As Long
    
    MyFolder = "Mijnfolder"
     
    With Application.FileSearch
        .NewSearch
        .LookIn = MyFolder
        .FileType = 4
        .SearchSubFolders = False
        
        .Execute
        LastRow = .FoundFiles.Count
        For I = 1 To LastRow
            If .FoundFiles(I) = MyFolder & "____.xls" Then
            'do nothing
            ElseIf .FoundFiles(I) = MyFolder & "_____.xls" Then
            'do nothing
            Else
            
            Open_file = .FoundFiles(I)
            'Application.Volatile True
            
            Filename = Mid(.FoundFiles(I), 148)
            filename_new = Mid(Filename, 1, Len(Filename) - 4)
            Saved_file = MyFolder & "Documents\Members\BSCQ\Database\TXT_Bestanden\" + Replace(Replace(filename_new, ".", "_"), " ", "_")
            
            Workbooks.Open Filename:=Open_file, UpdateLinks:=0, ReadOnly:=True
             
            Sheets(1).Select
            Rows("1:7").Delete Shift:=xlUp
            Range("A1").Select
                      
            Columns("A:AK").Replace What:="" & Chr(10) & "", Replacement:="--", LookAt:=xlPart, _
                SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
            
            ActiveWorkbook.SaveAs Filename:= _
                 Saved_file + ".txt", FileFormat:=xlText, CreateBackup:=False

             ActiveWorkbook.Close SaveChanges:=False
            
            End If

        Next I
    End With
   
End Sub

De enige regel die hierin niet werkt is:

code:

1
2
3

 Columns("A:AK").Replace What:="" & Chr(10) & "", Replacement:="--", LookAt:=xlPart, _
                SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False

Hier geeft hij de error:

Microsoft Excel cannot find any data to replace. Check if your search formatting and criteria are difined correctly. If you are sure that matching data exists in this workbook, it may be on a protected sheet. Excel cannot replace data on a protected worksheet.

Echter, de sheet is niet beveiligd, en voorheen deed ik deze zelfde soort macro in een excel bestand. Graag zou ik dit intern door access laten doen om het voor de gebruiker minder foutgevoelig te maken. Kan iemand mij hiermee helpen.

dinsdag 13 mei 2008 09:14

Acties:

pkuppens

BSeB schreef op dinsdag 13 mei 2008 @ 08:16:
Heren, misschien kunnen jullie me helpen. Ik ben bezig een job te automatiseren, maar tot op heden lukte alles behalve 1 stapje. Dit stapje houd in dat de inhoud van een sheet wordt doorzocht en indien nodig wordt gereplaced. De volgende code heb ik:
code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
Sub txt_bestanden_maken()
    Dim MyFolder As String
    Dim I As Long
    Dim LastRow As Long
    
    MyFolder = "Mijnfolder"
     
    With Application.FileSearch
        .NewSearch
        .LookIn = MyFolder
        .FileType = 4
        .SearchSubFolders = False
        
        .Execute
        LastRow = .FoundFiles.Count
        For I = 1 To LastRow
            If .FoundFiles(I) = MyFolder & "____.xls" Then
            'do nothing
            ElseIf .FoundFiles(I) = MyFolder & "_____.xls" Then
            'do nothing
            Else
            
            Open_file = .FoundFiles(I)
            'Application.Volatile True
            
            Filename = Mid(.FoundFiles(I), 148)
            filename_new = Mid(Filename, 1, Len(Filename) - 4)
            Saved_file = MyFolder & "Documents\Members\BSCQ\Database\TXT_Bestanden\" + Replace(Replace(filename_new, ".", "_"), " ", "_")
            
            Workbooks.Open Filename:=Open_file, UpdateLinks:=0, ReadOnly:=True
             
            Sheets(1).Select
            Rows("1:7").Delete Shift:=xlUp
            Range("A1").Select
                      
            Columns("A:AK").Replace What:="" & Chr(10) & "", Replacement:="--", LookAt:=xlPart, _
                SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
            
            ActiveWorkbook.SaveAs Filename:= _
                 Saved_file + ".txt", FileFormat:=xlText, CreateBackup:=False

             ActiveWorkbook.Close SaveChanges:=False
            
            End If

        Next I
    End With
   
End Sub
De enige regel die hierin niet werkt is:
code:
1
2
3
 Columns("A:AK").Replace What:="" & Chr(10) & "", Replacement:="--", LookAt:=xlPart, _
                SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
Hier geeft hij de error:

Microsoft Excel cannot find any data to replace. Check if your search formatting and criteria are difined correctly. If you are sure that matching data exists in this workbook, it may be on a protected sheet. Excel cannot replace data on a protected worksheet.

Echter, de sheet is niet beveiligd, en voorheen deed ik deze zelfde soort macro in een excel bestand. Graag zou ik dit intern door access laten doen om het voor de gebruiker minder foutgevoelig te maken. Kan iemand mij hiermee helpen.

Met een Macro opgenomen?

Ik heb me er niet al te ver in verdiept, en m'n VB is weer effe geleden.
Niettemin, ik vertrouw je Columns("A:AK") niet helemaal:
Na een Range.Select verwacht ik niet een operatie op weer iets anders.

dinsdag 13 mei 2008 09:30

Acties:

hamsteg

Species 5618

Inhakend op vorig commentaar:

code:

1	Range("A:AK").Select

code:

1	selection.Replace What:="" & Chr(10) & "" ....

moet de chr(10) geen chr(13) zijn?

[ Voor 4% gewijzigd door hamsteg op 13-05-2008 09:31 ]

... gecensureerd ...

dinsdag 13 mei 2008 09:35

Acties:

whoami

-> OFF

Dat is nl. de plaats waar VBA toestanden thuishoren.

https://fgheysels.github.io/

woensdag 14 mei 2008 13:57

Acties:

BSeB

Topicstarter

Nope, dit was de wijze die ik zocht:

code:

Dim oXL As Object
           
            Set oXL = CreateObject("Excel.Application")
            
            With oXL
                .Visible = False
                .Workbooks.Open (open_file), UpdateLinks:=0, ReadOnly:=True
            End With
            
            'Workbooks.Open Filename:=Open_file, UpdateLinks:=0, ReadOnly:=False
            oXL.Sheets(1).Select
            
            oXL.Rows("1:7").Delete Shift:=xlUp
            oXL.Range("A1").Select
                      
            oXL.ActiveWorkbook.Application.DisplayAlerts = False
            oXL.Columns("A:AK").Replace What:="" & Chr(10) & "", Replacement:="--", LookAt:=xlPart, _
                SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
            'MsgBox Saved_file
            oXL.ActiveWorkbook.SaveAs Filename:= _
                 Saved_file + ".txt", FileFormat:=xlText, CreateBackup:=False

             oXL.ActiveWorkbook.Close SaveChanges:=False
             oXL.Quit