How to read data stored excel file different worksheet-CodePudding

every once in a while, get a set of data, the deposit to the program directory xx. The.xls files (if not, then new) under the first worksheet, line 2, line 1 is the name of each column has built good data; After a period of time, another set of data into the same workbook under the second worksheet, is also in line 2; It through the same time, and then save to the first worksheet under the line 3 of... So, could you tell me how to achieve?
Thank you very much!

CodePudding user response:

There are several worksheets? The first, second, whether there is a third, fourth,...

Excel table there is a limit to the amount of work...

CodePudding user response:

reference 1st floor Topc008 response:

how many worksheet? The first, second, whether there is a third, fourth,...

Excel table there is a limit to the amount of work...

only a workbook, two work tables, work for the first time in the first table, two second time, then it is the first, second, in turn,

CodePudding user response:

Open the Excel as an external database (in the case of creating another, need Dir function to check whether there is any, and call the Excel object to establish an empty file,), use SQL statements insert records,

Dim cn As New ADODB. Connection
Cn. The Open ". The Provider=Microsoft Jet. The OLEDB. 4.0; The Data Source=C: \ Book1. XLS; Extended Properties="" Excel 8.0;" "
"Cn. The Execute "Insert Into [Sheet1 $] (fieldname1 fieldname2) Values (fieldvalue1 fieldvalue2)"
Cn. Close
The Set cn=Nothing

If you are able to take advantage of time determine to write the name of the table, If direct use to judge choose working table; Otherwise you may need to maintain a record, such as the work written to some of the information table 3,

CodePudding user response:

Refer to the most basic code below, according to the need to modify,

 

Option Explicit 
Private Xls As Excel Application, xBook As Excel. The Workbook, xSht As Excel. The Worksheet 
Private bFile As String 
Private Sub Command1_Click () 
' 'start work... 
On the Error Resume Next 
The Set Xls=New Excel. Application 
BFile="e: \ 123. XLS" 
The Set xBook=Xls. Workbooks. Open (bFile) 
If xBook Is Nothing Then 
The Set xBook=Xls. Workbooks. Add 
XBook. SaveAs bFile 
End the If 
If xBook Is Nothing Then 
MsgBox "exists unknown error! To open or create workbook!" 
The Exit Sub 
End the If 
'judgment work table number 
If xBook. Worksheets. Count & lt; 2 Then 
"' if the number of working table is not enough, no matter 3721 add 2 
XBook. Worksheets. Add 
XBook. Worksheets. Add 
End the If 
'set 2 header 
XBook. Worksheets (1). The Range (" a1 ")="time" 
XBook. Worksheets (2). The Range (" a1 ")="time" 
Set xSht=xBook. Worksheets (1) "for the first time working with table 1 
'set the timer 
Timer1. Interval=5000 
Timer1. Enabled=True 
End Sub 
Private Sub Form_Unload (Cancel As Integer) 
If Not xBook Is Nothing Then xBook. Close True 
The Set xBook=Nothing 
If Not Xls Is Nothing Then Xls. Quit 
The Set Xls=Nothing 
End Sub 
Private Sub Timer1_Timer () 
Dim As Long I 
I=xSht. Range (" a1 "). The End (xlDown). Row 
If I=xSht. Rows. Count Then I=1 
XSht. Range (" a "& amp; (I + 1))=Timer 'temporary time value was used as data storage 
'change XHST 
The Set xSht=xBook. Worksheets (IIf (xSht Is xBook. Worksheets (1), 2, 1)) 
XBook. Save 
End Sub