Shulou(Shulou.com)06/02 Report--

Editor to share with you VB.NET how to use Excel files, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to understand it!

1. VB.NET Excel file function code:

View plaincopy to clipboardprint? Option Explicit Public Function MergeXlsFile (ByVal strPath As String, Optional ByVal SheetCount As Byte = 1) As Boolean Dim i As Integer Dim strSrcFile As String Dim nRows As Long, nCols As Long, nSheets As Byte, nNewRows () As Integer Dim xlApp As Object, xlSrcBook As Object, xlNewBook As Object, xlSheet As Object, xlRange As Object On Error Resume Next If Right (strPath 1) "\" Then strPathstrPath = strPath & "\" if the number of worksheets in the merged file is less than 1, exit If SheetCount < 1 Then Exit Function 'delete the original merged file If Dir (strPath & "merged file .xls") "" Then Kill strPath & "merged file .xls"' to get the first XLS file strSrcFile = Dir (strPath & "*. Xls ") 'exit If Len (strSrcFile) = 0 Then Exit Function' if the file does not exist create an Excel instance Set xlApp = CreateObject (" Excel.Application ") 'create a new workbook Set xlNewBook = xlApp.Workbooks.Add' adjust the number of worksheets in the new workbook ReDim nNewRows (1 To SheetCount) For I = 1 To SheetCount-xlNewBook.Sheets.Count xlNewBook.Sheets.Add XlNewBook.Sheets (xlNewBook.Sheets.Count) Next 'Loop find all XLS files under the current path Do' Open the found XLS file Set xlSrcBook = xlApp.Workbooks.Open (strPath & strSrcFile) 'Loop copy the worksheet nSheets = IIf (xlSrcBook.Sheets.Count < SheetCount, xlSrcBook.Sheets.Count) in the source XLS file SheetCount) For I = 1 To nSheets Set xlSheet = xlSrcBook.Sheets (I) 'get the number of rows and rows of the actual data of the first worksheet in the source XLS file nRows = xlSheet.UsedRange.Rows.Count nCols = xlSheet.UsedRange.Columns.Count' use range objects to paste the source XLS file data to the merge result file Set xlRange = xlSheet.Range (xlSheet.Cells (1,1), xlSheet.Cells (nRows) NCols)) xlRange.Select xlRange.Copy xlNewBook.Sheets (I) .cells (nNewRows (I) + 1 1) .PasteSpecial & HFFFFEFF8 'saves the number of rows of the first worksheet in the merged result file nNewRows (I) = xlNewBook.Sheets (1). UsedRange.Rows.Count Next' closes the open source XLS file xlSrcBook.Close 'and continues to find the next XLS file strSrcFile = Dir () Loop Until Len (strSrcFile) = 0' Save and close the merged result file xlNewBook.SaveAs strPath & "merged file .xls" xlNewBook .Close 'exit Excel instance xlApp.Quit' release resources Erase nNewRows Set xlRange = Nothing Set xlSheet = Nothing Set xlNewBook = Nothing Set xlSrcBook = Nothing If Err.Number = 0 Then MergeXlsFile = True End Function Option Explicit Public Function MergeXlsFile (ByVal strPath As String Optional ByVal SheetCount As Byte = 1) As Boolean Dim i As Integer Dim strSrcFile As String Dim nRows As Long, nCols As Long, nSheets As Byte, nNewRows () As Integer Dim xlApp As Object, xlSrcBook As Object, xlNewBook As Object, xlSheet As Object, xlRange As Object On Error Resume Next If Right (strPath 1) "\" Then strPathstrPath = strPath & "\" if the number of worksheets in the merged file is less than 1, exit If SheetCount < 1 Then Exit Function 'delete the original merged file If Dir (strPath & "merged file .xls") "" Then Kill strPath & "merged file .xls"' to get the first XLS file strSrcFile = Dir (strPath & "*. Xls") ")'if the file does not exist, exit If Len (strSrcFile) = 0 Then Exit Function 'create an Excel instance Set xlApp = CreateObject (" Excel.Application ")' create a new workbook Set xlNewBook = xlApp.Workbooks.Add 'adjust the number of worksheets in the new workbook ReDim nNewRows (1 To SheetCount) For I = 1 To SheetCount-xlNewBook.Sheets.Count xlNewBook.Sheets.Add XlNewBook.Sheets (xlNewBook.Sheets.Count) Next 'Loop find all XLS files under the current path Do' Open the found XLS file Set xlSrcBook = xlApp.Workbooks.Open (strPath & strSrcFile) 'Loop copy the worksheet nSheets = IIf (xlSrcBook.Sheets.Count < SheetCount, xlSrcBook.Sheets.Count) in the source XLS file SheetCount) For I = 1 To nSheets Set xlSheet = xlSrcBook.Sheets (I) 'get the number of rows and rows of the actual data of the first worksheet in the source XLS file nRows = xlSheet.UsedRange.Rows.Count nCols = xlSheet.UsedRange.Columns.Count' use range objects to paste the source XLS file data to the merge result file Set xlRange = xlSheet.Range (xlSheet.Cells (1,1), xlSheet.Cells (nRows) NCols)) xlRange.Select xlRange.Copy xlNewBook.Sheets (I) .cells (nNewRows (I) + 1 1) .PasteSpecial & HFFFFEFF8 'saves the number of rows of the first worksheet in the merged result file nNewRows (I) = xlNewBook.Sheets (1). UsedRange.Rows.Count Next' closes the open source XLS file xlSrcBook.Close 'and continues to find the next XLS file strSrcFile = Dir () Loop Until Len (strSrcFile) = 0' save and close the merged result file xlNewBook.SaveAs strPath & "merged file .xls" xlNewBook. Close 'exit Excel instance xlApp.Quit' release resources Erase nNewRows Set xlRange = Nothing Set xlSheet = Nothing Set xlNewBook = Nothing Set xlSrcBook = Nothing If Err.Number = 0 Then MergeXlsFile = True End Function

2. The calling method of VB.NET Excel file:

View plaincopy to clipboardprint? Sub main () If MergeXlsFile ("c:\ temp", 1) Then MsgBox "data has been successfully merged!", vbInformation, "prompt" Else MsgBox "data merge failed!", vbCritical, "prompt" above End If End Sub is all the contents of this article "how to use Excel files in VB.NET". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.