Persekitaran VBA | Bagaimana cara menggunakan Fungsi Environ dalam VBA Excel?

Excel VBA ENVIRON (Alam Sekitar)

Fungsi VBA ENVIRON (singkatan dari ALAM SEKITAR ) yang dapat dikategorikan sebagai Fungsi Maklumat kerana fungsi ini mengembalikan nilai untuk pemboleh ubah persekitaran sistem operasi. Pemboleh ubah persekitaran (Sistem Operasi) mengandungi maklumat mengenai profil semua pengguna, nama pengguna, profil pengguna, folder rumah untuk pengguna, dll. Fungsi ini mengembalikan nilai rentetan.

Sintaks

Fungsi ini hanya mempunyai satu argumen iaitu ' Ekspresi '. Kita dapat menentukan kedudukan angka (nilai integer) yang mewakili kedudukan angka pemboleh ubah persekitaran dalam jadual pemboleh ubah persekitaran atau nama pemboleh ubah itu sendiri.

Sekiranya kita menentukan kedudukan angka, maka fungsi mengembalikan pemboleh ubah persekitaran dan nilainya keduanya dengan tanda masuk yang sama di antara.

Sekiranya kita menentukan nama pemboleh ubah maka satu-satunya nilai dikembalikan oleh fungsi.

Pengeluaran:

Bagaimana cara menggunakan Fungsi Environ di VBA?

Anda boleh memuat turun Templat VBA ENVIRON Excel ini di sini - Templat VBA ENVIRON Excel

Contoh # 1

Buka Prompt Perintah menggunakan Fungsi ENVIRON di VBA .

Untuk melakukan perkara yang sama, langkah-langkahnya adalah:

Masukkan butang arahan menggunakan perintah 'Sisipkan' yang tersedia dalam kumpulan 'Kawalan' di tab 'Pembangun' atau gunakan kekunci pintasan pintas ( Alt + F11 ).

Sekiranya tab 'Pembangun' tidak kelihatan, gunakan langkah-langkah berikut untuk menjadikan tab kelihatan.

Klik pada 'Fail' dan pilih 'Pilihan' dari senarai.

Pilih 'Customize Ribbon' dari menu kiri dan tandai kotak untuk tab 'Developer' dan klik 'OK' .

Kini 'tab Pembangun' dapat dilihat.

Sambil memasukkan butang perintah, jika kita terus menekan tombol ALT , maka tepi butang perintah akan bersamaan dengan sel perbatasan. Ubah sifat butang perintah yang dimasukkan menggunakan menu kontekstual, yang kita dapat dengan mengklik kanan pada 'Command button' .

Tetingkap Properties

Untuk menulis kod VBA untuk butang arahan, kita perlu memilih butang perintah dan memilih 'Lihat Kod' dari menu kontekstual.

Tulis kodnya seperti berikut:

Kami telah memanggil fungsi 'Shell' untuk menaip perintah untuk menjalankan program (dalam kes kami adalah command prompt).

Kami telah menggunakan 'ComSpec' , singkatan dari 'Command Specifier' .

Sekarang keluar dari VBE dan klik pada butang arahan. Kami telah memaparkan command prompt.

Contoh # 2

Misalkan, kami ingin mengekstrak nama dan butiran fail untuk folder yang dipilih seperti berikut.

Langkah untuk melakukan perkara yang sama adalah:

Isi sel B2: H9 dengan warna oren muda.

Buat label menggunakan arahan 'Insert' dalam kumpulan 'Controls' di 'Developer'.

Sila buat label yang ditunjukkan di bawah ini dan edit sifat seperti kapsyen , BackColor , BackStyle , BorderStyle , Shadow

Buat Combo Box seperti berikut menggunakan perintah Combo Box (salah satu ActiveX Controls) yang tersedia dalam perintah Insert di Controls group di Developer.

Setelah membuat Combo Box di excel seperti berikut, kita dapat mengubah hartanah.

Kami akan menambahkan kod untuk senarai yang akan dipaparkan dalam kotak kombo menggunakan perintah Lihat Kod dalam menu kontekstual.

Ini adalah kod untuk "Pilih Folder" Kombo.

Ini adalah kod untuk " SortBy" ComboBox.

Ini adalah kod untuk "Pilih Pesanan" Kombo.

Kami akan membuat kotak senarai yang mengandungi semua jenis fail sehingga pengguna dapat memilihnya untuk mendapatkan hanya jenis fail dalam hasilnya. Untuk melakukan hal yang sama, pilih " List List (ActiveX Control) " dari perintah " Insert " dalam kumpulan " Controls " di tab " Developer ".

Seret kotak senarai seperti gambar di bawah.

Tukar sifat kotak senarai seperti berikut.

Untuk menambahkan jenis fail ke kotak senarai, gunakan kod berikut.

Tuliskan kod dalam "Buku kerja ini".

Kod:

 Private Sub Workbook_Open() Dim ArrFileType(25) As Variant ArrFileType(0) = "Microsoft Excel 97-2003 Worksheet(.xls)" ArrFileType(1) = "Microsoft Office Excel Worksheet(.xlsx)" ArrFileType(2) = "Microsoft Excel Macro-Enabled Worksheet(.xlsm)" ArrFileType(3) = "Word Document 97-2003(.doc)" ArrFileType(4) = "Word Document 2007-2010(.docx)" ArrFileType(5) = "Text Document(.txt)" ArrFileType(6) = "Adobe Acrobat Document(.pdf)" ArrFileType(7) = "Compressed (zipped) Folder(.Zip)" ArrFileType(8) = "WinRAR archive(.rar)" ArrFileType(9) = "Configuration settings(.ini)" ArrFileType(10) = "GIF File(.gif)" ArrFileType(11) = "PNG File(.png)" ArrFileType(12) = "JPG File(.jpg)" ArrFileType(13) = "MP3 Format Sound(.mp3)" ArrFileType(14) = "M3U File(.m3u)" ArrFileType(15) = "Rich Text Format(.rtf)" ArrFileType(16) = "MP4 Video(.mp4)" ArrFileType(17) = "Video Clip(.avi)" ArrFileType(18) = "Windows Media Player(.mkv)" ArrFileType(19) = "SRT File(.srt)" ArrFileType(20) = "PHP File(.php)" ArrFileType(21) = "Firefox HTML Document(.htm, .html)" ArrFileType(22) = "Cascading Style Sheet Document(.css)" ArrFileType(23) = "JScript Script File(.js)" ArrFileType(24) = "XML Document(.xml)" ArrFileType(25) = "Windows Batch File(.bat)" Sheet2.FileTypesListBox.List = ArrFileType End Sub

Masukkan kotak centang menggunakan perintah " Sisipkan " yang sama dalam kumpulan " Kontrol " di tab " Pembangun " dan ubah sifat untuk memasukkan "Kotak Centang" menggunakan perintah 'Properties' yang tersedia dalam kelompok yang sama setelah memilih objek.

Masukkan butang arahan menggunakan perintah 'Sisipkan' yang tersedia dalam kumpulan yang sama dan ubah sifat seperti kapsyen dan sifat lain juga.

Kami telah membentuk keseluruhan struktur. Sekarang kita perlu menulis kodnya.

Aktifkan 'Mode Reka Bentuk' dan klik kanan pada butang " Ambil semua butiran fail " untuk memilih " Lihat Kod " dari menu kontekstual untuk menambahkan kod untuk butang.

We will declare some variable first in the module.

Below is the code added to a “Fetch all files details” button.

Code:

 Private Sub FetchFilesBtnCommandButton_Click() iRow = 14 fPath = Environ("HOMEPATH") & "\" & SelectTheFolderComboBox.Value If fPath  "" Then Set FSO = New Scripting.FileSystemObject If FSO.FolderExists(fPath)  False Then Set SourceFolder = FSO.GetFolder(fPath) If Sheet2.IncludingSubFoldersCheckBox.Value = True Then IsSubFolder = True Else IsSubFolder = False If SourceFolder.Files.Count = 0 Then MsgBox "No files exists in this Folder" & vbNewLine & vbNewLine & "Check your folder path and Try Again !!", vbInformation Exit Sub End If End If Call ClearResult If FetchAllTypesOfFilesCheckBox.Value = True Then Call ListFilesInFolder(SourceFolder, IsSubFolder) Call ResultSorting(xlAscending, "C14", "D14", "E14") Else Call ListFilesInFolderXtn(SourceFolder, IsSubFolder) Call ResultSorting(xlAscending, "C14", "D14", "E14") End If FilesCountLabel.Caption = iRow - 14 Else MsgBox "Selected Path Does Not Exist !!" & vbNewLine & vbNewLine & "Select Correct One and Try Again !!", vbInformation End If Else MsgBox "Folder Path Can not be Empty !!" & vbNewLine & vbNewLine & "", vbInformation End If End Sub

Define the ‘ClearResult’ function in the module. To insert the module, select ‘ThisWorkbook’ then ‘Insert’ and then ‘Module”’.

Write the following code in the module.

Code for ClearResult

There are more subroutines like ‘ListFilesInFolder’, ‘ListFilesInFolderXtn’, ‘ResultSorting’, we will define all these sub routines in the module.

‘ListFilesInFolder’

Code:

 Public Sub ListFilesInFolder(SourceFolder As Scripting.Folder, IncludeSubfolders As Boolean ) On Error Resume Next For Each FileItem In SourceFolder.Files ' display file properties Cells(iRow, 2).Formula = iRow - 13 Cells(iRow, 3).Formula = FileItem.Name Cells(iRow, 4).Formula = FileItem.Path Cells(iRow, 5).Formula = Int(FileItem.Size / 1024) Cells(iRow, 6).Formula = FileItem.Type Cells(iRow, 7).Formula = FileItem.DateLastModified Cells(iRow, 8).Select Selection.Hyperlinks.Add Anchor:=Selection, Address:= _ FileItem.Path, TextToDisplay:="Click Here to Open" 'Cells(iRow, 8).Formula = "=HYPERLINK(""" & FileItem.Path & """,""" & "Click Here to Open" & """)" iRow = iRow + 1 ' next row number Next FileItem If IncludeSubfolders Then For Each SubFolder In SourceFolder.SubFolders ListFilesInFolder SubFolder, True Next SubFolder End If Set FileItem = Nothing Set SourceFolder = Nothing Set FSO = Nothing End Sub

‘ListFilesInFolderXtn’

 Public Sub ListFilesInFolderXtn(SourceFolder As Scripting.Folder, IncludeSubfolders As Boolean ) On Error Resume Next Dim FileArray As Variant FileArray = Get_File_Type_Array For Each FileItem In SourceFolder.Files Call ReturnFileType(FileItem.Type, FileArray) If IsFileTypeExists = True Then Cells(iRow, 2).Formula = iRow - 13 Cells(iRow, 3).Formula = FileItem.Name Cells(iRow, 4).Formula = FileItem.Path Cells(iRow, 5).Formula = Int(FileItem.Size / 1024) Cells(iRow, 6).Formula = FileItem.Type Cells(iRow, 7).Formula = FileItem.DateLastModified Cells(iRow, 8).Select Selection.Hyperlinks.Add Anchor:=Selection, Address:= _ FileItem.Path, TextToDisplay:="Click Here to Open" 'Cells(iRow, 8).Formula = "=HYPERLINK(""" & FileItem.Path & """,""" & "Click Here to Open" & """)" iRow = iRow + 1 ' next row number End If Next FileItem If IncludeSubfolders Then For Each SubFolder In SourceFolder.SubFolders ListFilesInFolderXtn SubFolder, True Next SubFolder End If Set FileItem = Nothing Set SourceFolder = Nothing Set FSO = Nothing End Sub

‘ResultSorting’

 Sub ResultSorting(xlSortOrder As String , sKey1 As String , sKey2 As String , sKey3 As String ) Range("C13").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Sort Key1:=Range(sKey1), Order1:=xlSortOrder, Key2:=Range(sKey2 _ ), Order2:=xlAscending, Key3:=Range(sKey3), Order3:=xlSortOrder, Header _ :=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _ , DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _ xlSortNormal Range("B14").Select End Sub

In ‘ListFilesInFolderXtn’ subroutine, we have called a function named ‘ReturnFileType’ and ‘GetFileTypeArray’, we need to define the functions in the same module.

‘ReturnFileType’

Code:

 Public Function ReturnFileType(fileType As String , FileArray As Variant ) As Boolean Dim i As Integer IsFileTypeExists = False For i = 1 To UBound (FileArray) + 1 If FileArray(i - 1) = fileType Then IsFileTypeExists = True Exit For Else IsFileTypeExists = False End If Next End Function

‘GetFileTypeArray’

Code:

Public Function Get_File_Type_Array() As Variant Dim i, j, TotalSelected As Integer Dim arrList() As String TotalSelected = 0 For i = 0 To Sheet2.FileTypesListBox.ListCount - 1 If Sheet2.FileTypesListBox.Selected(i) = True Then TotalSelected = TotalSelected + 1 End If Next ReDim arrList(0 To TotalSelected - 1) As String j = 0 i = 0 For i = 0 To Sheet2.FileTypesListBox.ListCount - 1 If Sheet2.FileTypesListBox.Selected(i) = True Then arrList(j) = Left(Sheet2.FileTypesListBox.List(i), InStr(1, Sheet2.FileTypesListBox.List(i), "(") - 1) j = j + 1 End If Next Get_File_Type_Array = arrList End Function

We have a command button captioned as ‘Export to Excel File’, we need to write the code for this button as follows:

In Module, define the subroutine named ‘Export_to_excel’.

Code:

 Sub Export_to_excel() On Error GoTo err Dim xlApp As New Excel.Application Dim xlWB As New Workbook Set xlWB = xlApp.Workbooks.Add 'xlWB.Add xlApp.Visible = False ThisWorkbook.Activate Range("B13").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy xlApp.Visible = True xlWB.Activate xlWB.Sheets("Sheet1").Select xlWB.Sheets("Sheet1").Range("B2").PasteSpecial Paste:=xlPasteValues xlWB.Sheets("Sheet1").Cells.Select xlWB.Sheets("Sheet1").Cells.EntireColumn.AutoFit xlWB.Sheets("Sheet1").Range("B2").Select Exit Sub err: MsgBox ("Error Occured while exporting. Try again") End Sub

We have one more command button captioned as ‘Export to Text File’. We will write the code for the command button as follows:

In this code, we can see that we have a user form, which we need to design using the following steps:

Right Click on ‘Sheet2 (Example2)’ sheet and choose ‘Insert’ and then ‘UserForm’ from the menu.

Design the UserForm using tools from the toolbox.

We have used ‘Labels’, ‘Combo Box’, ‘Text Box’ and ‘Command buttons’ for the Userform and have changed caption and name for all the components.

For the first command button (OK), we have to write the code as follows:

Code:

 Private Sub CommandButton1_Click() Dim iSeperator As String If ComboBox1.Value = "Other" Then iSeperator = TextBox1.Value Else iSeperator = ComboBox1.Value End If If iSeperator = "" Then If MsgBox("Hello You have not selected any delimeter." & vbNewLine & vbNewLine & _ " It will be very difficult to read the Text file, without specific delimiter", vbYesNo) = vbYes Then Call textfile(iSeperator) Else Exit Sub End If Else Select Case ComboBox1.ListIndex Case 0: iSeperator = "," Case 1: iSeperator = "|" Case 2: iSeperator = "vbTab" Case 3: iSeperator = ";" End Select Call textfile(iSeperator) Unload Me End If End Sub

We have called ‘textfile’ function in the subroutine for the command button that is why we need to define ‘textfile’ function in the module.

Code:

 Sub textfile(iSeperator As String ) Dim iRow, iCol Dim iLine, f ThisWorkbook.Activate Range("B13").Select TotalRowNumber = Range(Selection, Selection.End(xlDown)).Count - 12 If iSeperator  "vbTab" Then Open ThisWorkbook.Path & "\File1.txt" For Output As #1 Print #1, "" Close #1 Open ThisWorkbook.Path & "\File1.txt" For Append As #1 For iRow = 13 To TotalRowNumber iLine = "" For iCol = 2 To 7 iLine = iLine & iSeperator & Cells(iRow, iCol).Value Next Print #1, iLine Next Close #1 Else Open ThisWorkbook.Path & "\File1.txt" For Output As #1 Print #1, "" Close #1 Open ThisWorkbook.Path & "\File1.txt" For Append As #1 For iRow = 13 To TotalRowNumber iLine = "" For iCol = 2 To 7 iLine = iLine & vbTab & Cells(iRow, iCol).Value Next Print #1, iLine Next Close #1 End If f = Shell("C:\WINDOWS\notepad.exe " & ThisWorkbook.Path & "\File1.txt", vbMaximizedFocus) MsgBox "Your File is saved in " & ThisWorkbook.Path & "\File1.txt" End Sub

For command button 2 (Cancel), we need to write the following code. Double click on the cancel button to write the code.

For the Combo Box for selecting a specifier, write the following code.

For the UserForm, write the following code.

For the ‘Fetch all type of files’ checkbox, write the following code.

For the ‘ListBox’ for file types, write the following code.

For ‘SelectTheOrder’ combo box, write the following code.

Code:

 Private Sub SelectTheOrderComboBox_Change() Select Case (SelectTheOrderComboBox.Value) Case "Ascending" If SortByComboBox.Value = "File Name" Then Call ResultSorting(xlAscending, "C14", "E14", "G14") End If If SortByComboBox.Value = "File Type" Then Call ResultSorting(xlAscending, "F14", "E14", "C14") End If If SortByComboBox.Value = "File Size" Then Call ResultSorting(xlAscending, "E14", "C14", "G14") End If If SortByComboBox.Value = "Last Modified" Then Call ResultSorting(xlAscending, "G14", "C14", "E14") End If Case "Descending" If SortByComboBox.Value = "File Name" Then Call ResultSorting(xlDescending, "C14", "E14", "G14") End If If SortByComboBox.Value = "File Type" Then Call ResultSorting(xlDescending, "F14", "E14", "C14") End If If SortByComboBox.Value = "File Size" Then Call ResultSorting(xlDescending, "E14", "C14", "G14") End If If SortByComboBox.Value = "Last Modified" Then Call ResultSorting(xlDescending, "G14", "C14", "E14") End If Case Default Exit Sub End Select End Sub

For ‘Sortby’ combo box, we will write the following code.

Code:

 Private Sub SortByComboBox_Change() Select Case (SelectTheOrderComboBox.Value) Case "Ascending" If SortByComboBox.Value = "File Name" Then Call ResultSorting(xlAscending, "C14", "E14", "G14") End If If SortByComboBox.Value = "File Type" Then Call ResultSorting(xlAscending, "F14", "E14", "C14") End If If SortByComboBox.Value = "File Size" Then Call ResultSorting(xlAscending, "E14", "C14", "G14") End If If SortByComboBox.Value = "Last Modified" Then Call ResultSorting(xlAscending, "G14", "C14", "E14") End If Case "Descending" If SortByComboBox.Value = "File Name" Then Call ResultSorting(xlDescending, "C14", "E14", "G14") End If If SortByComboBox.Value = "File Type" Then Call ResultSorting(xlDescending, "F14", "E14", "C14") End If If SortByComboBox.Value = "File Size" Then Call ResultSorting(xlDescending, "E14", "C14", "G14") End If If SortByComboBox.Value = "Last Modified" Then Call ResultSorting(xlDescending, "G14", "C14", "E14") End If Case Default Exit Sub End Select End Sub

Now we have written the entire code. Now we can select the desired folder and file type and find out the list for files, which we can sort, by ‘File Name’, ‘File Type’, ‘File Size’ or ‘Last-Modified’ and can export the list to excel or text file.