- Sub proba()
- Dim wsCopy As Worksheet
- Dim rngCopy As Range
- Dim wbPaste As Workbook
- Dim wsPaste As Worksheet
- Dim rngPaste As Range
- aktivniList = ActiveSheet.Name
- ChDir ActiveWorkbook.Path 'odredivanje direktorija u kojem se nalazi excel datoteka sa procedurom
- FileToOpen = Application.GetOpenFilename _
- (Title:="izaberi datoteku za uvoz podataka", _
- FileFilter:="prihvacene extenzije *.xls (*.xlsm),")
- If FileToOpen = False Then
- 'ako datoteka nije izabrana
- MsgBox "Nije izabrana datoteka.", vbExclamation, "ERROR"
- Exit Sub
- Else
- 'ako je datoteka izabrana
- imeFajla = GetFilenameFromPath(FileToOpen) 'ime datoteke koju smo uzeli
- Set wbPaste = ActiveWorkbook
- Set wbCopy = Workbooks.Open(FileToOpen)
- Set wsCopy = wbCopy.Worksheets("Sheet1")
- zadnjiRed = traziZadnjiRed("Sheet1", 1)
- zadnjaKolona = traziZadnjuKolonu("Sheet1")
- Set rngCopy = wsCopy.Range(Cells(1, 1), Cells(zadnjiRed, zadnjaKolona))
- Set wsPaste = wbPaste.Worksheets(aktivniList) 'shit u koji idu podaci
- Set rngPaste = wsPaste.Range("A1") 'pocinje od celije A1
- rngCopy.Copy
- rngPaste.PasteSpecial
- Workbooks.Application.CutCopyMode = False
- Filename = ActiveWorkbook.Name
- Workbooks(Filename).Close
- End If
- End Sub
- Function traziZadnjiRed(ImeSita As String, kolona)
- Dim Zadnji As Long
- Dim ws As Worksheet
- Set ws = Sheets(ImeSita)
- With ws
- Zadnji = .Cells(.Rows.Count, kolona).End(xlUp).Row
- End With
- traziZadnjiRed = Zadnji
- End Function
- Function traziZadnjuKolonu(ImeSita As String)
- Dim Zadnji As Long
- Dim ws As Worksheet
- Dim zadnjaCelija As Range
- Set ws = Sheets(ImeSita)
- Set zadnjaCelija = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), LookIn:=xlFormulas, _
- LookAt:=xlPart, SearchOrder:=xlByColumns, _
- SearchDirection:=xlPrevious, MatchCase:=False)
- Zadnji = zadnjaCelija.Column
- traziZadnjuKolonu = Zadnji
- End Function
- Function GetFilenameFromPath(ByVal strPath As String) As String
- ' npr. 'c:\winnt\win.ini' vraca 'win.ini'
- If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then
- GetFilenameFromPath = GetFilenameFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)
- End If
- End Function