Projdi makro v té příloze na to přejmenování souborů, tam to je.
Ad2)
Ale jak postupovat při importování hodnoty z formuláře?
Do toho formůláře se načítají data odkud?
Nebo nevím jestli chápu tento bod dobře.
Ale jak postupovat při importování hodnoty z formuláře?
Kód: Vybrat vše
aFilename = "RUN_" & aIndex & ".xlsm"
Kód: Vybrat vše
aFilename = ThisWorkbook.Path & "\" & Target.Value & ".xlsm"
Kód: Vybrat vše
Sub NactiDataZAdresare()
Dim aWorkbook As Workbook, aRange As Range
Dim TWbk As String, SExN As String, SPath As String
Dim SFleFirst As Boolean, CntFFile As Integer, aIndex As Integer, MsgResponse As Byte
'
Application.ScreenUpdating = False
' nacist parametry
Set aRange = Range("G3:AG100")
TWbk = ThisWorkbook.Name ' nazev tohoto souboru
SPath = ThisWorkbook.Path & "\" ' cesta k souborum
SExN = "*.xlsm" ' koncovka soubor
aIndex = 1 ' radek od ktereho se vkladaji data
' v katalogu otevirat jednotlive soubory
SFleFirst = True
Do
If SFleFirst Then
SExN = Dir(SPath & SExN) ' prvni soubor v adresari
If SExN = vbNullString Then _
MsgResponse = MsgBox("Složka souborù: '" & SPath & "' je prázdná!", _
vbOKOnly + vbInformation): Exit Do
SFleFirst = False
Else
SExN = Dir ' dalsi soubory v adresari
End If
' vsechny soubory otevreny
If SExN = vbNullString Then _
MsgResponse = MsgBox("Ve složce nejsou již další soubory pro pøejmenování.", _
vbOKOnly + vbInformation): Exit Do
' otevrit soubor
On Error GoTo err
' preskoc otevreni tohoto sesitu
If Not TWbk = SExN Then
Set aWorkbook = Workbooks.Open(SPath & SExN)
aRange.Cells(aIndex, 1).Value = aWorkbook.Worksheets("Conditions").Range("B9")
aRange.Cells(aIndex, 2).Value = aWorkbook.Worksheets("Langmuir").Range("N26")
aRange.Cells(aIndex, 3).Value = aWorkbook.Worksheets("Langmuir").Range("N33")
aRange.Cells(aIndex, 4).Value = aWorkbook.Worksheets("DR and Medek").Range("P34")
aRange.Cells(aIndex, 5).Value = aWorkbook.Worksheets("DR and Medek").Range("P27")
aRange.Cells(aIndex, 6).Value = aWorkbook.Worksheets("DR and Medek").Range("Z27")
aRange.Cells(aIndex, 7).Value = aWorkbook.Worksheets("Micropores distribution").Range("N29")
aRange.Cells(aIndex, 8).Value = aWorkbook.Worksheets("Micropores distribution").Range("N36")
aRange.Cells(aIndex, 9).Value = aWorkbook.Worksheets("Langmuir").Range("N27")
aRange.Cells(aIndex, 10).Value = aWorkbook.Worksheets("Langmuir").Range("N34")
aRange.Cells(aIndex, 11).Value = aWorkbook.Worksheets("DR and Medek").Range("P35")
aRange.Cells(aIndex, 12).Value = aWorkbook.Worksheets("DR and Medek").Range("P28")
aRange.Cells(aIndex, 13).Value = aWorkbook.Worksheets("DR and Medek").Range("Z28")
aRange.Cells(aIndex, 14).Value = aWorkbook.Worksheets("Micropores distribution").Range("N30")
aRange.Cells(aIndex, 15).Value = aWorkbook.Worksheets("Micropores distribution").Range("N37")
aRange.Cells(aIndex, 16).Value = aWorkbook.Worksheets("Langmuir").Range("N28")
aRange.Cells(aIndex, 17).Value = aWorkbook.Worksheets("Langmuir").Range("N35")
aRange.Cells(aIndex, 18).Value = aWorkbook.Worksheets("DR and Medek").Range("P36")
aRange.Cells(aIndex, 19).Value = aWorkbook.Worksheets("DR and Medek").Range("P29")
aRange.Cells(aIndex, 20).Value = aWorkbook.Worksheets("DR and Medek").Range("Z29")
aRange.Cells(aIndex, 21).Value = aWorkbook.Worksheets("Micropores distribution").Range("N31")
aRange.Cells(aIndex, 22).Value = aWorkbook.Worksheets("Micropores distribution").Range("N38")
aRange.Cells(aIndex, 23).Value = aWorkbook.Worksheets("Conditions").OptionButton1
err:
aIndex = aIndex + 1
On Error GoTo 0
aWorkbook.Close False
Set aWorkbook = Nothing
End If
' DoEvents
Loop
Application.ScreenUpdating = True
End Sub
Kód: Vybrat vše
SExN = "*.xlsm" ' koncovka soubor
Kód: Vybrat vše
aRange.Cells(aIndex, 23).Value = aWorkbook.Worksheets("Conditions").OptionButton1.Value = True
Akorát to na konci chce otevřít "sebe samo" - ale to nevadí :-)
Pokud se vrátím k těm tlačítkům
Kód: Vybrat vše
OB5 = Sheets("Conditions").OptionButton5.Value
OB6 = Sheets("Conditions").OptionButton6.Value
If OB5 = True Then
OB5popisek = Worksheets("Conditions").OptionButton5.Caption
ElseIf OB6 = True Then
OB5popisek = Worksheets("Conditions").OptionButton6.Caption
End If
Takto je pod sebe vypiš a mohlo by to splňovat co potřebuješ
Kód: Vybrat vše
OB5 = Sheets("Conditions").OptionButton5.Value
OB6 = Sheets("Conditions").OptionButton6.Value
If OB5 = True Then
OB5popisek = Worksheets("Conditions").OptionButton5.Caption
ElseIf OB6 = True Then
OB5popisek = Worksheets("Conditions").OptionButton6.Caption
End If
Kód: Vybrat vše
Sub NactiDataZAdresare()
Dim aWorkbook As Workbook, aRange As Range
Dim TWbk As String, SExN As String, SPath As String
Dim SFleFirst As Boolean, CntFFile As Integer, aIndex As Integer, MsgResponse As Byte
'
Application.ScreenUpdating = False
' nacist parametry
Set aRange = Range("G3:AG100")
TWbk = ThisWorkbook.Name ' nazev tohoto souboru
SPath = ThisWorkbook.Path & "\" ' cesta k souborum
SExN = "*.xlsm" ' koncovka soubor
aIndex = 1 ' radek od ktereho se vkladaji data
' v katalogu otevirat jednotlive soubory
SFleFirst = True
Do
If SFleFirst Then
SExN = Dir(SPath & SExN) ' prvni soubor v adresari
If SExN = vbNullString Then _
MsgResponse = MsgBox("Složka souborů: '" & SPath & "' je prázdná!", _
vbOKOnly + vbInformation): Exit Do
SFleFirst = False
Else
SExN = Dir ' dalsi soubory v adresari
End If
' vsechny soubory otevreny
If SExN = vbNullString Then _
MsgResponse = MsgBox("Ve složce nejsou již další soubory pro přejmenování.", _
vbOKOnly + vbInformation): Exit Do
' otevrit soubor
On Error GoTo err
' preskoc otevreni tohoto sesitu
If Not TWbk = SExN Then
Set aWorkbook = Workbooks.Open(SPath & SExN)
OB1 = Sheets("Conditions").OptionButton1.Value
OB6 = Sheets("Conditions").OptionButton6.Value
If OB1 = True Then
OB5popisek = Worksheets("Conditions").OptionButton1.Caption
ElseIf OB6 = True Then
OB5popisek = Worksheets("Conditions").OptionButton6.Caption
End If
aRange.Cells(aIndex, 1).Value = aWorkbook.Worksheets("Conditions").Range("B9")
aRange.Cells(aIndex, 2).Value = aWorkbook.Worksheets("Langmuir").Range("N26")
aRange.Cells(aIndex, 3).Value = aWorkbook.Worksheets("Langmuir").Range("N33")
aRange.Cells(aIndex, 4).Value = aWorkbook.Worksheets("DR and Medek").Range("P34")
aRange.Cells(aIndex, 5).Value = aWorkbook.Worksheets("DR and Medek").Range("P27")
aRange.Cells(aIndex, 6).Value = aWorkbook.Worksheets("DR and Medek").Range("Z27")
aRange.Cells(aIndex, 7).Value = aWorkbook.Worksheets("Micropores distribution").Range("N29")
aRange.Cells(aIndex, 8).Value = aWorkbook.Worksheets("Micropores distribution").Range("N36")
aRange.Cells(aIndex, 9).Value = aWorkbook.Worksheets("Langmuir").Range("N27")
aRange.Cells(aIndex, 10).Value = aWorkbook.Worksheets("Langmuir").Range("N34")
aRange.Cells(aIndex, 11).Value = aWorkbook.Worksheets("DR and Medek").Range("P35")
aRange.Cells(aIndex, 12).Value = aWorkbook.Worksheets("DR and Medek").Range("P28")
aRange.Cells(aIndex, 13).Value = aWorkbook.Worksheets("DR and Medek").Range("Z28")
aRange.Cells(aIndex, 14).Value = aWorkbook.Worksheets("Micropores distribution").Range("N30")
aRange.Cells(aIndex, 15).Value = aWorkbook.Worksheets("Micropores distribution").Range("N37")
aRange.Cells(aIndex, 16).Value = aWorkbook.Worksheets("Langmuir").Range("N28")
aRange.Cells(aIndex, 17).Value = aWorkbook.Worksheets("Langmuir").Range("N35")
aRange.Cells(aIndex, 18).Value = aWorkbook.Worksheets("DR and Medek").Range("P36")
aRange.Cells(aIndex, 19).Value = aWorkbook.Worksheets("DR and Medek").Range("P29")
aRange.Cells(aIndex, 20).Value = aWorkbook.Worksheets("DR and Medek").Range("Z29")
aRange.Cells(aIndex, 21).Value = aWorkbook.Worksheets("Micropores distribution").Range("N31")
aRange.Cells(aIndex, 22).Value = aWorkbook.Worksheets("Micropores distribution").Range("N38")
aRange.Cells(aIndex, 23).Value = aWorkbook.Worksheets("Conditions").OptionButton1
err:
aIndex = aIndex + 1
On Error GoTo 0
aWorkbook.Close False
Set aWorkbook = Nothing
End If
' DoEvents
Loop
Application.ScreenUpdating = True
End Sub
Kód: Vybrat vše
Sub NactiDataZAdresare()
Dim aWorkbook As Workbook, aRange As Range
Dim TWbk As String, SExN As String, SPath As String
Dim SFleFirst As Boolean, CntFFile As Integer, aIndex As Integer, MsgResponse As Byte
Dim OB1, OB2, OB3, OB4, OB5, OB6, OB7 As Boolean, OBpopisek, OBpopisek2 As Variant
'
Application.ScreenUpdating = False
' nacist parametry
Set aRange = Range("G3:AG100")
TWbk = ThisWorkbook.Name ' nazev tohoto souboru
SPath = ThisWorkbook.Path & "\" ' cesta k souborum
SExN = "*.xlsm" ' koncovka soubor
aIndex = 1 ' radek od ktereho se vkladaji data
' v katalogu otevirat jednotlive soubory
SFleFirst = True
Do
If SFleFirst Then
SExN = Dir(SPath & SExN) ' prvni soubor v adresari
If SExN = vbNullString Then _
MsgResponse = MsgBox("Složka souborů: '" & SPath & "' je prázdná!", _
vbOKOnly + vbInformation): Exit Do
SFleFirst = False
Else
SExN = Dir ' dalsi soubory v adresari
End If
' vsechny soubory otevreny
If SExN = vbNullString Then _
MsgResponse = MsgBox("Ve složce nejsou již další soubory pro přejmenování.", _
vbOKOnly + vbInformation): Exit Do
' otevrit soubor
On Error GoTo err
' preskoc otevreni tohoto sesitu
If Not TWbk = SExN Then
Set aWorkbook = Workbooks.Open(SPath & SExN)
OB1 = Sheets("Conditions").OptionButton1.Value
OB2 = Sheets("Conditions").OptionButton2.Value
OB3 = Sheets("Conditions").OptionButton3.Value
OB4 = Sheets("Conditions").OptionButton4.Value
OB5 = Sheets("Conditions").OptionButton5.Value
OB6 = Sheets("Conditions").OptionButton6.Value
OB7 = Sheets("Conditions").OptionButton7.Value
If OB1 = True Then
OBpopisek = Worksheets("Conditions").OptionButton1.Caption
ElseIf OB2 = True Then
OBpopisek = Worksheets("Conditions").OptionButton2.Caption
ElseIf OB3 = True Then
OBpopisek = Worksheets("Conditions").OptionButton3.Caption
ElseIf OB4 = True Then
OBpopisek = Worksheets("Conditions").TextBox1.Value
End If
If OB5 = True Then
OBpopisek2 = Worksheets("Conditions").OptionButton5.Caption
ElseIf OB6 = True Then
OBpopisek2 = Worksheets("Conditions").OptionButton6.Caption
ElseIf OB7 = True Then
OBpopisek2 = Worksheets("Conditions").OptionButton7.Caption
End If
aRange.Cells(aIndex, 1).Value = aWorkbook.Worksheets("Conditions").Range("B9")
aRange.Cells(aIndex, 2).Value = aWorkbook.Worksheets("Langmuir").Range("N26")
aRange.Cells(aIndex, 3).Value = aWorkbook.Worksheets("Langmuir").Range("N33")
aRange.Cells(aIndex, 4).Value = aWorkbook.Worksheets("DR and Medek").Range("P34")
aRange.Cells(aIndex, 5).Value = aWorkbook.Worksheets("DR and Medek").Range("P27")
aRange.Cells(aIndex, 6).Value = aWorkbook.Worksheets("DR and Medek").Range("Z27")
aRange.Cells(aIndex, 7).Value = aWorkbook.Worksheets("Micropores distribution").Range("N29")
aRange.Cells(aIndex, 8).Value = aWorkbook.Worksheets("Micropores distribution").Range("N36")
aRange.Cells(aIndex, 9).Value = aWorkbook.Worksheets("Langmuir").Range("N27")
aRange.Cells(aIndex, 10).Value = aWorkbook.Worksheets("Langmuir").Range("N34")
aRange.Cells(aIndex, 11).Value = aWorkbook.Worksheets("DR and Medek").Range("P35")
aRange.Cells(aIndex, 12).Value = aWorkbook.Worksheets("DR and Medek").Range("P28")
aRange.Cells(aIndex, 13).Value = aWorkbook.Worksheets("DR and Medek").Range("Z28")
aRange.Cells(aIndex, 14).Value = aWorkbook.Worksheets("Micropores distribution").Range("N30")
aRange.Cells(aIndex, 15).Value = aWorkbook.Worksheets("Micropores distribution").Range("N37")
aRange.Cells(aIndex, 16).Value = aWorkbook.Worksheets("Langmuir").Range("N28")
aRange.Cells(aIndex, 17).Value = aWorkbook.Worksheets("Langmuir").Range("N35")
aRange.Cells(aIndex, 18).Value = aWorkbook.Worksheets("DR and Medek").Range("P36")
aRange.Cells(aIndex, 19).Value = aWorkbook.Worksheets("DR and Medek").Range("P29")
aRange.Cells(aIndex, 20).Value = aWorkbook.Worksheets("DR and Medek").Range("Z29")
aRange.Cells(aIndex, 21).Value = aWorkbook.Worksheets("Micropores distribution").Range("N31")
aRange.Cells(aIndex, 22).Value = aWorkbook.Worksheets("Micropores distribution").Range("N38")
aRange.Cells(aIndex, 23).Value = OBpopisek
aRange.Cells(aIndex, 24).Value = OBpopisek2
err:
aIndex = aIndex + 1
On Error GoTo 0
aWorkbook.Close False
Set aWorkbook = Nothing
End If
' DoEvents
Loop
Application.ScreenUpdating = True
End Sub
Uživatelé prohlížející si toto fórum: Žádní registrovaní uživatelé a 12 hostů