[VBA] Excel macro, formule met data ander workbook

[zoek34]

Beste Tweakers,

Op dit moment ben ik bezig met een klein Visual Basic opdrachtje. Ik moet uit een lange lijst met data de data uit een kolom gebruiken. Met die data moet ik dmv een formule het aantal keer dat er een bepaalde waarde is.

Wat ik tot nu toe wel heb gevonden:
- Hoe excel te openen met macro
- Hoe excel te sluiten met macro
- Het uitrekenen van bovenstaande waardes op 1 workbook

De meeste zoekresultaten van Google hebben het over het geheel overkopieren van de data (mbv een macro) naar het workbook, maar dit is totaal niet gewenst (het bestand waar de data uitgehaald moet worden is +50.000 rijen excel).

Ik heb tot nu toe de volgende code:

code:

Dim pathString As String
Dim resultWorkbook As Workbook
        
pathString = Application.GetOpenFilename(fileFilter:="All Files (* . xl*) , *.xl* ")
Set resultWorkbook = Workbooks.Open(pathString)
    
Cells(6, 2) = "=COUNTIF(resultWorkbook.AH2:AH65536,A6)"
resultWorkbook.Close False

De hele range bij het overkopieren (AH2 t/m AH65536) is gebouwd op een mogelijke uitbreiding. In A6 staat de informatie waarvan er geteld wordt hoevaak de inhoud ervan voorkomt.

Weet iemand een oplossing zodat de formule gaat werken. Blijkbaar doet de huidige regel van Cells(6, 2) wel iets aangezien die gewoon de code doorloopt en geen error geeft (en ook uiteindelijk het workbook sluit).

Zou iemand een oplossing weten?

Met vriendelijke groeten,

Zoek34

[RobIII]

Waar hoort mijn topic?
PRG >> CSA

[Coffeemonster]

De referentie naar een cel in een ander werkblad heeft in het algemeen de volgende vorm:

code:

=[Book1]Sheet1!$A$1

Wil je overigens de referentie naar het andere werkblad wel bewaren? Je wordt dan bij het openen van het bestand elke keer gevraagd of je de referenties wil bijwerken.

[zoek34]

Volgens mij gaat bovenstaande code nietwerken in mijn geval. Het bestand wordt namelijk elke dag opnieuw aangemaakt (met een andere naam) waardoor Book1 een variabele moet zij

De macro heeft als functie dat die de excelfunctie COUNTIF moet gebruik waarbij de waardes die die uitrekent bewaard moeten blijven (ze hoeven dus maar 1x per dag uitgevoerd te worden, de volgende dag is er weer 1 ander bestand)

[Coffeemonster]

Dan moet je ervoor zorgen dat in de uiteindelijke formule de naam van resultWorkbook komt te staan op de plaats waar nu Book1 staat (tekst aan elkaar koppelen met &).

Of je zet er in plaats van de formule meteen de waarde in, als de macro maar een keer per dag wordt gebruikt en resultWorkbook verder niet verandert.

[zoek34]

Net uitgeporbeerd en ik krijg last van errors. ik vermoed een syntaxfoutje:

Cells(6, 2) = "=COUNTIF([resultWorkbook]'SHEETNAAM'!AH2:[resultWorkbook]'SHEETNAAM'!AH65536,A6)"

Error is runtime error 1004

[Coffeemonster]

Wat er nu letterlijk in cel B6 komt te staan is:

code:

=COUNTIF([resultWorkbook]'SHEETNAAM'!AH2:[resultWorkbook]'SHEETNAAM'!AH65536,A6)

Dat gaat natuurlijk niet werken. Wat je uiteindelijk wil hebben is:

code:

"=COUNTIF([Book1]'SHEETNAAM'!AH2:AH65536,A6)

waarbij Book1 de name-property is van resultWorkbook. Je zal de formule dus uit stukjes moeten opbouwen. De stukjes die altijd hetzelfde zijn (dus alles behalve "Book1") zet je tussen aanhalingstekens, en voor het variabele deel (de naam van het werkboek) zet je de name-property van resultWorkbook (dus: resultWorkbook.name). Deze stukken koppel je aan elkaar met & en zet je zo in cel B6.

[zoek34]

Dus als ik het goed begrijp bedoel je zoiets:

Cells(6, 2) = "=COUNTIF([resultWorkbook.name]&" 'Made By CSI'!AH2:AH65536,A6")"

[F_J_K]

Nee je wilt de inoud van resultWorkbook.name meegeven, niet de string resultWorkbook.name.

Dus deel je string op in drieen: "=COUNTIF([" & resultWorkbook.name & "]'SHEETNAAM'!AH2:AH65536,A6)"

Of zoiets, ik heb niet inhoudelijk naar de strings gekeken. Punt is dat je drie deelstringen hebt die je aan elkaar wilt plakken.

[Coffeemonster]

Bijna. Een string is of een stuk tekst tussen aanhalingstekens: "Dit is een string", of een variabele die een string bevat (bijvoorbeeld resultWorkbook.name). Deze kan je netjes aan elkaar koppelen met & om zo één string te krijgen.

spoiler:

Cells(6, 2) = "=COUNTIF([" & resultWorkbook.name & "]'Made By CSI'!AH2:AH65536,A6)"

Dus gekoppeld:
String: "=COUNTIF(["
Variabele: resultWorkbook.name
String: "]'Made By CSI'!AH2:AH65536,A6)"

[zoek34]

Nog steeds krijg ik last van de runtime error 1004 met de volgende regel:

Cells(6, 2) = "=COUNTIF([" & resultWorkbook.name & "]'Made By CSI'!AH2:AH65536,A6)"

[LiquidT_NL]

Maar de string ziet er wel goed uit?

Als in: wat gebeurt er als je bijvoorbeeld
MsgBox "=COUNTIF([" & resultWorkbook.name & "]'Made By CSI'!AH2:AH65536,A6)"

doet?

[zoek34]

Dan krijg ik keurig een MsgBox met het volgende:

=COUNTIF([" & resultWorkbook.Name & "]'Made By CSI'!AH2:AH65536,A6)

[F_J_K]

Haal de spaties (en aanhalingstekens) in de tabbladen eens weg.

Ook: vergeet niet dat je de activesheet hebt veranderd. Als je het originele bestand wilt vullen dan wil je Set orgWorksheet = ActiveSheet doen en dan orgWorksheet.cells(..) gebruiken.

[ Voor 8% gewijzigd door F_J_K op 18-03-2013 13:50 ]

[Coffeemonster]

Sorry, syntaxfoutje van mijn kant:

Cells(6, 2) = "=COUNTIF(['" & resultWorkbook.name & "]Made By CSI'!AH2:AH65536,A6)"

Let op de plaatsing van de enkele aanhalingstekens.

Edit: was weer te snel. Eerste enkele aanhalingsteken moet natuurlijk voor het rechte haakje staan. Dus: "=COUNTIF('[" etcetera.

[ Voor 27% gewijzigd door Coffeemonster op 18-03-2013 15:47 ]

[zoek34]

Met de bovenstaande regel nog steeds last van een 1004 runtime error:

Cells(6, 2) = "=COUNTIF(['" & resultWorkbook.Name & "]Made By CSI'!AH2:AH65536,A6)"

[F_J_K]

Het lijkt me goed als je dus even gaat troubleshooten ipv enkel steeds te copypasten en de melding te dumpen

[zoek34]

Ik heb het ondertussen werkend met de volgende macro:

code:

Sub CalculateCISubType()
'
' CalculateCISubType Macro
'
' Keyboard Shortcut: Ctrl+g
'
    Dim FileOrg, s As String
    
    Dim pathString As String
    Dim resultWorkbook As Workbook
    
    FileOrg = ActiveWorkbook.Name
        
    pathString = Application.GetOpenFilename(fileFilter:="All Files (* . xl*) , *.xl* ")
    Set resultWorkbook = Workbooks.Open(pathString)
    
    Workbooks(FileOrg).Activate
    
    s = "=COUNTIF('[" + resultWorkbook.Name + "]Made By CSI'!AH2:AH65536,"
    
    Cells(6, 2) = s + "A6)"
    Cells(7, 2) = s + "A7)"
    Cells(8, 2) = s + "A8)"
    Cells(9, 2) = s + "A9)"
    Cells(10, 2) = s + "A10)"
    Cells(11, 2) = s + "A11)"
    Cells(12, 2) = s + "A12)"
    Cells(13, 2) = s + "A13)"
    Cells(14, 2) = s + "A14)"
    Cells(15, 2) = s + "A15)"
    resultWorkbook.Close False
End Sub

Ik heb alleen nu te horen gekregen dat in het orginele bestand er een filter ontbrak. Ik moet dus in de macro iets weten te verwerken zodat die de filter toepast en de output geeft met de rijen die na de filter zichtbaar zijn. Mogelijk heb ik al een oplossing voor deze extra toepassing, maar daar ben ik nu nog mee bezig.


EDIT:
Ik ben ondertussen al iets verder gegaan (even een 2de macro gemaakt), maar ik loop weer vast. Nu loopt die al vast bij de sub (1ste regel):

code:

Sub Macro1()
'
' Macro1 Macro
'

'
    Dim lastrow As Long
    Dim fr, counters As Integer

    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "Dit is tijdelijk"
        
    Windows("Sheetnaam").Activate
        
    lastrow = Determine_Last
        
    ActiveSheet.Range("$A$1:$EA$" + Trim(Str$(lastrow))).AutoFilter Field:=75, Criteria1:="Filter"
    
    lastrow = Determine_Last
    Range("AH1").Select
    Selection.Offset(1, 0).Select
    fr = ActiveCell.Row
    Range("AH" + Trim(Str$(fr)) + ":AH" + Trim(Str$(lastrow))).Select
    Selection.Copy
    
    Windows("Calculation.xlsx").Activate
    Sheets("Dit is tijdelijk").Select
    
    Range("A1").Select
    
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    Range("A1").Select
    lastrow = Determine_Last
    
    For i = 6 To 15
        counters = CountIf("A1:A" + Trim(Str$(lastrow)), Macroform!A + Trim(Str$(i)))
        Cells(i, 2) = counters
    Next i
    
End Sub

Private Function Determine_Last() As Long
'
' Determine_Last Macro
'
    ActiveCell.SpecialCells(xlLastCell).Select
    Determine_Last = ActiveCell.Row
        
End Function

Iemand die het probleem erin ziet

Heeft iemand een oplossing voor dit probleempje?

[ Voor 37% gewijzigd door zoek34 op 19-03-2013 13:22 ]