excelstuff/excelcollect.py at master · gsoukup/excelstuff · GitHub

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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
#!user/Anaconda/envs/base python3
#-*-Coding:UTF8-*-
#VBA remake in python for commuting EXCEL files

import openpyxl

#https://stackoverflow.com/questions/9319317/quick-and-easy-file-dialog-in-python
from tkinter import filedialog
from tkinter import *
path = ""

#https://stackoverflow.com/questions/10377998/how-can-i-iterate-over-files-in-a-given-directory
import os
filename = ""


root = Tk()
root.withdraw()

path = filedialog.askdirectory()
print(path)

for filename in os.listdir(path):
    if filename.endswith(".xls") or filename.endswith(".xlsx"):
        print(os.path.join(path, filename))
        continue
    else:
        continue


"""

Sub TSSARE()


'
'                                   Declaration
'

Dim w As ThisWorkbook

Dim fajl As Variant
Dim fajlok As Variant
Dim id As Variant

Dim path As String

'
'                                   Folder Select
'

With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Choose Folder!"

If .Show = True Then
path = .SelectedItems(1)


Else
path = ”nothing”

End If
End With

'
'                                   Datacopy
'

If path <> "nothing" Then

'
' i - Spacing for Macro Buttons, id-i = 1 becouse first id
'

i = 6
id = 1
'k = 0
Application.DisplayAlerts = False

fajl = Dir(path & "\*")

Do While fajl <> "" 'Or k < 10

Workbooks.Open (path & "\" & fajl)

ThisWorkbook.Sheets(1).Cells(i + 1, 1).Value = id
ThisWorkbook.Sheets(1).Cells(i + 1, 2).Value = Right(path, 8)
ThisWorkbook.Sheets(1).Cells(i + 1, 3).Value = fajl

'                                   Cover

ActiveWorkbook.Sheets(1).Cells(8, 4).Copy Destination:=ThisWorkbook.Sheets(1).Cells(i + 1, 4)
ActiveWorkbook.Sheets(1).Cells(9, 4).Copy Destination:=ThisWorkbook.Sheets(1).Cells(i + 1, 5)
ActiveWorkbook.Sheets(1).Cells(10, 4).Copy Destination:=ThisWorkbook.Sheets(1).Cells(i + 1, 6)
ActiveWorkbook.Sheets(1).Cells(11, 4).Copy Destination:=ThisWorkbook.Sheets(1).Cells(i + 1, 7)
ActiveWorkbook.Sheets(1).Cells(12, 4).Copy Destination:=ThisWorkbook.Sheets(1).Cells(i + 1, 8)
ActiveWorkbook.Sheets(1).Cells(13, 4).Copy Destination:=ThisWorkbook.Sheets(1).Cells(i + 1, 9)

'                                   Clipboard empty
Application.CutCopyMode = False


ActiveWorkbook.Close (False)

fajl = Dir()
i = i + 1
id = id + 1

'k = k + 1


Loop


End If

Application.DisplayAlerts = True

End Sub
"""