Tilretning af vba der henter celle oplysninger fra lukket fil

Prøv at lege med denne, jeg har ændret i koden.

Sub ReadDataFromAllWorkbooksInFolder()
    Dim FolderName As String, wbName As String, r As Long, cValue As Variant
    Dim wbList() As String, wbCount As Integer, i As Integer
    FolderName = "C:\TestData"
    ' create list of workbooks in foldername
    wbCount = 0
    wbName = Dir(FolderName & "\*.xls")
    While wbName <> ""
        wbCount = wbCount + 1
        ReDim Preserve wbList(1 To wbCount)
        wbList(wbCount) = wbName
        wbName = Dir
    Wend
    If wbCount = 0 Then Exit Sub
    ' get values from each workbook
    r = 0
    Workbooks.Add
    For i = 1 To wbCount
        r = r + 1
        C3Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "C3")
        C24Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "C24")
        D24Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "D24")
        Cells(r, 1).Formula = wbList(i)
        Cells(r, 2).FormulaLocal = "=" & C3Value
        Cells(r, 3).FormulaLocal = "=" & C24Value
        Cells(r, 4).FormulaLocal = "=" & D24Value
    Next i
End Sub

Private Function GetInfoFromClosedFile(ByVal wbPath As String, wbName As String, wsName As String, cellRef As String) As Variant
    Dim arg As String
    GetInfoFromClosedFile = ""
    If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"
    If Dir(wbPath & "\" & wbName) = "" Then Exit Function
    arg = "'" & wbPath & "[" & wbName & "]" & wsName & "'!" & Range(cellRef).Address
    On Error Resume Next
    GetInfoFromClosedFile = arg
End Function

Jeg vil lige høre om den lille ændring du har lavet her, hente værdierne der står i celle d8 og d21 til d45?

Da det er dem jeg skal bruge, og det er X antal faktura jeg skal hente ind.

Undskyld at jeg først svare nu. men blev lige kaldt på arbejde, i min ferie.

Jeg vil lige høre om det er rigtig, at hvis man skal hente flere celler, Såer det disse linier man skal bruge ? 

D24Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "D24")
Cells(r, 2).FormulaLocal = "=" & C3Value

Hvis man husker at ændre celle navnet.

Men kan ikke lige finde ud af hvordan jeg får den til at åbne via valget af navn i dropdown`en.

Mvh. Kenneth.

lige et spørgsmål mere.

hvad skal man ændre celles(r, 2).......... til, for at få den til at udfylde a1 - e1, og der efter hoppe til næste linie. for at forstææe sådan til den har hentet alle faktura ind ?

Nu har jeg været ved at lege lidt med det selv, men kan ikke få det helt til at virke.

Jeg har fået det til at slette de tomme linier og overføre det til det andet ark.

Men det er lige det med at få det til at hente dataerne der de lukkede ark, når man vælger en person i dropdown`en. Når jeg køre den, henter den ikke noget.

Nogen venlige mennesker her inden der kan hjælpe lidt med hvad jeg evt. har lavet galt, og hvis der er noget der kan gøres nemmere. :-)

Her er den prg. kode der er lagt ind under en kommandoknap :

Private Sub CommandButton1_Click()

  Dim FolderName As String, wbName As String, r As Long, cValue As Variant
    Dim wbList() As String, wbCount As Integer, i As Integer
    FolderName = "g:\Faktura\excelfakturaDB"
            If Target.Address = "$c$2" Then                  'Som test er cellen m/dropdown anbragt i denne celle - tilpasses!
            ActiveWorkbook.Open FolderName:=sti + Target + ".xls"
            End If

   
   
    ' create list of workbooks in foldername
    wbCount = 0
    wbName = Dir(FolderName & "\*.xls")
    While wbName <> ""
        wbCount = wbCount + 1
        ReDim Preserve wbList(1 To wbCount)
        wbList(wbCount) = wbName
        wbName = Dir
    Wend
    If wbCount = 0 Then Exit Sub
    ' get values from each workbook
    r = 0
    Workbooks.Add
    For i = 1 To wbCount
        r = r + 1
        D5Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "D5")
        D8Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "D8")
        A21Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "A21")
        A22Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "A22")
        A23Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "A23")
        A24Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "A24")
        A25Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "A25")
        A26Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "A26")
        A27Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "A27")
        A28Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "A28")
        A29Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "A29")
        A30Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "A30")
        A31Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "A31")
        A32Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "A32")
        A33Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "A33")
        A34Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "A34")
        A35Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "A35")
        A36Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "A36")
        A37Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "A37")
        A38Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "A38")
        A39Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "A39")
        A40Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "A40")
        A41Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "A41")
        A42Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "A42")
        A43Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "A43")
        A44Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "A44")
        A45Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "A45")
        B21Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "B21")
        B22Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "B22")
        B23Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "B23")
        B24Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "B24")
        B25Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "B25")
        B26Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "B26")
        B27Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "B27")
        B28Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "B28")
        B29Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "B29")
        B30Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "B30")
        B31Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "B31")
        B32Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "B32")
        B33Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "B33")
        B34Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "B34")
        B35Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "B35")
        B36Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "B36")
        B37Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "B37")
        B38Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "B38")
        B39Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "B39")
        B40Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "B40")
        B41Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "B41")
        B42Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "B42")
        B43Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "B43")
        B44Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "B44")
        B45Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "B45")
        C21Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "C21")
        C22Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "C22")
        C23Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "C23")
        C24Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "C24")
        C25Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "C25")
        C26Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "C26")
        C27Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "C27")
        C28Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "C28")
        C29Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "C29")
        C30Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "C30")
        C31Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "C31")
        C32Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "C32")
        C33Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "C33")
        C34Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "C34")
        C35Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "C35")
        C36Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "C36")
        C37Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "C37")
        C38Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "C38")
        C39Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "C39")
        C40Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "C40")
        C41Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "C41")
        C42Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "C42")
        C43Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "C43")
        C44Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "C44")
        C45Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "C45")
        D21Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "D21")
        D22Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "D22")
        D23Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "D23")
        D24Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "D24")
        D25Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "D25")
        D26Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "D26")
        D27Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "D27")
        D28Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "D28")
        D29Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "D29")
        D30Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "D30")
        D31Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "D31")
        D32Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "D32")
        D33Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "D33")
        D34Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "D34")
        D35Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "D35")
        D36Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "D36")
        D37Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "D37")
        D38Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "D38")
        D39Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "D39")
        D40Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "D40")
        D41Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "D41")
        D42Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "D42")
        D43Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "D43")
        D44Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "D44")
        D45Value = GetInfoFromClosedFile(FolderName, wbList(i), "Ark1", "D45")
        Cells(r, 1).Formula = wbList(i)
        Cells(3, 2).Formula = "=" & D5Value
        Cells(3, 3).Formula = "=" & D8Value
        Cells(3, 4).Formula = "=" & A21Value
        Cells(3, 5).Formula = "=" & B21Value
        Cells(3, 6).Formula = "=" & C21Value
        Cells(3, 7).Formula = "=" & D21Value
        Cells(4, 4).Formula = "=" & A22Value
        Cells(4, 5).Formula = "=" & B22Value
        Cells(4, 6).Formula = "=" & C22Value
        Cells(4, 7).Formula = "=" & D22Value
        Cells(5, 4).Formula = "=" & A23Value
        Cells(5, 5).Formula = "=" & B23Value
        Cells(5, 6).Formula = "=" & C23Value
        Cells(5, 7).Formula = "=" & D23Value
        Cells(6, 4).Formula = "=" & A24Value
        Cells(6, 5).Formula = "=" & B24Value
        Cells(6, 6).Formula = "=" & C24Value
        Cells(6, 7).Formula = "=" & D24Value
        Cells(7, 4).Formula = "=" & A25Value
        Cells(7, 5).Formula = "=" & B25Value
        Cells(7, 6).Formula = "=" & C25Value
        Cells(7, 7).Formula = "=" & D25Value
        Cells(8, 4).Formula = "=" & A26Value
        Cells(8, 5).Formula = "=" & B26Value
        Cells(8, 6).Formula = "=" & C26Value
        Cells(8, 7).Formula = "=" & D26Value
        Cells(9, 4).Formula = "=" & A27Value
        Cells(9, 5).Formula = "=" & B27Value
        Cells(9, 6).Formula = "=" & C27Value
        Cells(9, 7).Formula = "=" & D27Value
        Cells(10, 4).Formula = "=" & A28Value
        Cells(10, 5).Formula = "=" & B28Value
        Cells(10, 6).Formula = "=" & C28Value
        Cells(10, 7).Formula = "=" & D28Value
        Cells(11, 4).Formula = "=" & A29Value
        Cells(11, 5).Formula = "=" & B29Value
        Cells(11, 6).Formula = "=" & C29Value
        Cells(11, 7).Formula = "=" & D29Value
        Cells(12, 4).Formula = "=" & A30Value
        Cells(12, 5).Formula = "=" & B30Value
        Cells(12, 6).Formula = "=" & C30Value
        Cells(12, 7).Formula = "=" & D30Value
        Cells(13, 4).Formula = "=" & A31Value
        Cells(13, 5).Formula = "=" & B31Value
        Cells(13, 6).Formula = "=" & C31Value
        Cells(13, 7).Formula = "=" & D31Value
        Cells(14, 4).Formula = "=" & A32Value
        Cells(14, 5).Formula = "=" & B32Value
        Cells(14, 6).Formula = "=" & C32Value
        Cells(14, 7).Formula = "=" & D32Value
        Cells(15, 4).Formula = "=" & A33Value
        Cells(15, 5).Formula = "=" & B33Value
        Cells(14, 6).Formula = "=" & C33Value
        Cells(14, 7).Formula = "=" & D33Value
        Cells(15, 4).Formula = "=" & A34Value
        Cells(15, 5).Formula = "=" & B34Value
        Cells(15, 6).Formula = "=" & C34Value
        Cells(15, 7).Formula = "=" & D34Value
        Cells(16, 4).Formula = "=" & A35Value
        Cells(16, 5).Formula = "=" & B35Value
        Cells(16, 6).Formula = "=" & C35Value
        Cells(16, 7).Formula = "=" & D35Value
        Cells(17, 4).Formula = "=" & A36Value
        Cells(17, 5).Formula = "=" & B36Value
        Cells(17, 6).Formula = "=" & C36Value
        Cells(17, 7).Formula = "=" & D36Value
        Cells(18, 4).Formula = "=" & A37Value
        Cells(18, 5).Formula = "=" & B37Value
        Cells(18, 6).Formula = "=" & C37Value
        Cells(18, 7).Formula = "=" & D37Value
        Cells(19, 4).Formula = "=" & A38Value
        Cells(19, 5).Formula = "=" & B38Value
        Cells(19, 6).Formula = "=" & C38Value
        Cells(19, 7).Formula = "=" & D38Value
        Cells(20, 4).Formula = "=" & A39Value
        Cells(20, 5).Formula = "=" & B39Value
        Cells(20, 6).Formula = "=" & C39Value
        Cells(20, 7).Formula = "=" & D39Value
        Cells(21, 4).Formula = "=" & A40Value
        Cells(21, 5).Formula = "=" & B40Value
        Cells(21, 6).Formula = "=" & C40Value
        Cells(21, 7).Formula = "=" & D40Value
        Cells(22, 4).Formula = "=" & A41Value
        Cells(22, 5).Formula = "=" & B41Value
        Cells(22, 6).Formula = "=" & C41Value
        Cells(22, 7).Formula = "=" & D41Value
        Cells(23, 4).Formula = "=" & A42Value
        Cells(23, 5).Formula = "=" & B42Value
        Cells(23, 6).Formula = "=" & C42Value
        Cells(23, 7).Formula = "=" & D42Value
        Cells(24, 4).Formula = "=" & A43Value
        Cells(24, 5).Formula = "=" & B43Value
        Cells(24, 6).Formula = "=" & C43Value
        Cells(24, 7).Formula = "=" & D43Value
        Cells(25, 4).Formula = "=" & A44Value
        Cells(25, 5).Formula = "=" & B44Value
        Cells(25, 6).Formula = "=" & C44Value
        Cells(25, 7).Formula = "=" & D44Value
        Cells(26, 4).Formula = "=" & A45Value
        Cells(26, 5).Formula = "=" & B45Value
        Cells(26, 6).Formula = "=" & C45Value
        Cells(26, 7).Formula = "=" & D45Value
    Next i
End Sub

Private Function GetInfoFromClosedFile(ByVal wbPath As String, wbName As String, wsName As String, cellRef As String) As Variant
    Dim arg As String
    GetInfoFromClosedFile = ""
    If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"
    If Dir(wbPath & "\" & wbName) = "" Then Exit Function
    arg = "'" & wbPath & "[" & wbName & "]" & wsName & "'!" & Range(cellRef).Address
    On Error Resume Next
    GetInfoFromClosedFile = arg
End Function

det var en lang smøre, kan du sende et eksempel af både excelfakturaDB og den du vil have det over i.

Skriv venligst i den du vil have det overi, hvad du ønsker.

kabbak snabela tiscali dot dk

jeg vil prøve om der ikke er en anden løsning.

Så har jeg sendt det til dig.

Hvis du skal have flere point for det, finder vi ud af det.

Mvh.

Kenneth

Private Sub CommandButton1_Click()
    Dim FolderName As String, I As Integer, NO As Integer, strFilnavn() As Variant
    Dim Data As Variant, RW As Integer
    FolderName = "g:\Faktura\excelfakturaDB\" & [C2]    ' rettes til
   
    If Right(FolderName, 1) <> "\" Then FolderName = FolderName & "\"
    NO = 1
    ReDim Preserve strFilnavn(NO)
    strFilnavn(NO) = Dir(FolderName & "*.xls")    ' Hent den første filnavn.
    Do While strFilnavn(NO) <> ""  ' Start løkken
        If strFilnavn(NO) <> "." And strFilnavn(NO) <> ".." Then
            NO = NO + 1
            ReDim Preserve strFilnavn(NO)
        End If
        strFilnavn(NO) = Dir    ' Hent næste filnavn.
    Loop
    NO = NO - 1
   
    Application.ScreenUpdating = False
    For I = 1 To NO
        Workbooks.Open Filename:=FolderName & strFilnavn(I)
        Data = Workbooks(strFilnavn(I)).Worksheets("Enkel faktura").Range("A1:D45")
        ActiveWorkbook.Close False
        For y = 21 To UBound(Data, 1)
        RW = ThisWorkbook.Worksheets("se alle faktura på en person").Range("B65536").End(xlUp).Row + 1
            For x = 1 To UBound(Data, 2)
                If Data(y, 1) = Empty Then Exit For ' hopper over tomme linier
                ThisWorkbook.Worksheets("se alle faktura på en person").Cells(RW, 1) = Data(8, 4)
                ThisWorkbook.Worksheets("se alle faktura på en person").Cells(RW, x + 1) = Data(y, x)
            Next x
        Next y
    Next I
    Application.ScreenUpdating = True
End Sub

Hej kabbak.

Det virker bare som det skal. Mange tak for hjælpen.

Hvis du ligger et svar, giver jeg point.

Du må have en rigtig god påske.

MVH.

Kenneth

et svar ;-))

Tak og i lige måde