Help: vb save data to Excel, is there a quick way! ~

hataki

If the title:
If the ordinary method is too slow, is there a fast method to save the data obtained from vb in Germany to an Excel file. When reading, I used the method of opening the database. There is no problem in speed. Is there a similar method for saving? I tried, but the saved data is not from A1, and the saved data is not numeric, but character. Hope you guys would like to enlighten me!

giddens

Db.Execute "SELECT "&XsZd&" INTO [Excel 8.0;DATABASE="&CMG.Filename&"].[dcxx] FROM ["&Bm&"]"&SQLWhere&SQLOrder

XsZd refers to the field to be displayed
CMG.filename refers to the file name to be saved to
dcxx is the name of the cell table in EXCEL
Bm refers to the database table
sqlwhere refers to where condition
sqlorder refers to sorting conditions

This way is fast!

desperados

Upstairs, the landlord said it didn’t start from A1, you shouldn’t do this.

talent00168

sheet1.[d10].copyfromrecordset rst

kidsex

An example once written for the reference of the landlord, this speed is very fast! Even if you export 576*20 data in a few seconds

It is strongly recommended that the landlord use the CSV file format to export the table file.
It is a formatted text file, the general format is: the text line represents the table line
The contents separated by commas on the same line represent the contents of different fields.

The landlord can save an ordinary EXCEL file and choose to save it as a CSV format, then open the file in WordPad and take a look at the format to know, it is very simple. And the speed of doing so is also very fast, which is not comparable to writing data one by one.

When creating a data interface, you can create an array of string type, first put all the contents in the array, and then use the loop to write to the file.
The file processing time of hundreds of thousands of records is only a few seconds

To add: the CSV file you generated, the icon displayed on the system with OFFICE is an EXCEL icon (slightly different, there is a lowercase "a" under the icon), indicating that OFFICE has put this The file is registered as the default openable file type.



Need to study carefully, learn and apply! ! ! ! ! ! ! ! ! !



Rem quickly saves the data file format CSV, which can be opened with EXCEL


Private Sub MnuCsv_Click()
Dim i As Integer

'Form
Dim myPic As StdPicture
Set myPic = CapturePic(Picture1)
SavePicture myPic, "c:\myPic.bmp"

''Write a CSV file, a file that Excel can open

  Open "D:\11.csv" For Output As #1
  

        Print #1, "Step sequence number"; ",";''''''Here is the first line of CSV, fixed column header
     Print #1, "nx"; ",";
     Print #1, "αi"; ",";
     Print #1, "Turn radius of tooth tip"; ",";
     Print #1, "Fc"; ",";
     Print #1, "Fh"; ",";
     Print #1, "Fdt"; ",";
     Print #1, "Fdn"; ",";
     Print #1, "Fo"; ",";
     Print #1, vbNullString''''End newline
     
   ''''''data input
   For i = 1 To 546
     Print #1, Val(MSFlexGrid1.TextMatrix(i, 0)); ",";
     Print #1, Val(MSFlexGrid1.TextMatrix(i, 1)); ",";
     Print #1, Val(MSFlexGrid1.TextMatrix(i, 2)); ",";
     Print #1, Val(MSFlexGrid1.TextMatrix(i, 3)); ",";
     Print #1, Val(MSFlexGrid1.TextMatrix(i, 4)); ",";
     Print #1, Val(MSFlexGrid1.TextMatrix(i, 5)); ",";
     Print #1, Val(MSFlexGrid1.TextMatrix(i, 6)); ",";
     Print #1, Val(MSFlexGrid1.TextMatrix(i, 7)); ",";
     Print #1, Val(MSFlexGrid1.TextMatrix(i, 8)); ",";
     Print #1, vbNullString
   Next
   Close #1

End Sub




Open and save the file:
Rem quickly saves the data file format CSV, which can be opened with EXCEL


Private Sub MnuCsv_Click()
Dim i As Integer

'Form
Dim myPic As StdPicture
Set myPic = CapturePic(Picture1)
SavePicture myPic, "c:\myPic.bmp"

''Write a CSV file, a file that Excel can open

Dim FileName As String'''''''''''Save the data to an Excel table

CommDiag1.FileName = ""
CommDiag1.Filter = "CSV|*.csv"
CommDiag1.ShowSave
FileName = CommDiag1.FileName
If FileName = "" Then
  Exit Sub
End If

  Open FileName For Output As #1
  

     Print #1, "Step sequence number"; ",";''''''Here is the first line of CSV, fixed column header
     Print #1, "nx"; ",";
     Print #1, "αi"; ",";
     Print #1, "Turn radius of tooth tip"; ",";
     Print #1, "Fc"; ",";
     Print #1, "Fh"; ",";
     Print #1, "Fdt"; ",";
     Print #1, "Fdn"; ",";
     Print #1, "Fo"; ",";
     Print #1, vbNullString''''End newline
     
   ''''''data input
   For i = 1 To 546
     Print #1, Val(MSFlexGrid1.TextMatrix(i, 0)); ",";
     Print #1, Val(MSFlexGrid1.TextMatrix(i, 1)); ",";
     Print #1, Val(MSFlexGrid1.TextMatrix(i, 2)); ",";
     Print #1, Val(MSFlexGrid1.TextMatrix(i, 3)); ",";
     Print #1, Val(MSFlexGrid1.TextMatrix(i, 4)); ",";
     Print #1, Val(MSFlexGrid1.TextMatrix(i, 5)); ",";
     Print #1, Val(MSFlexGrid1.TextMatrix(i, 6)); ",";
     Print #1, Val(MSFlexGrid1.TextMatrix(i, 7)); ",";
     Print #1, Val(MSFlexGrid1.TextMatrix(i, 8)); ",";
     Print #1, vbNullString
   Next
   Close #1

End Sub

hataki

kidsex
Using your method is indeed faster, but now there is a new problem. I can’t open the excel file saved in this way with the database method. It is estimated that the file format does not match. Is there any way to save it? What about the matching method of exporting excel files?
The traditional method is very slow, it should be the way to open the file!
Please enlighten me!

kidsex

You said you can't open the CSV file? Should I open it by writing a program or open it directly with access? Can you make it clear?

The traditional excel export data, there is no good way to read it quickly because it is generally read one by one. But at present there is also a block import method for Excel and database connection, I don’t know what your specific method is

hataki

The following method is found by me, and after testing, it is really fast
Dim i As Integer
Dim mondata(1799) As Single
Dim adoConnection As New ADODB.Connection
Dim adoRecordset As New ADODB.Recordset

adoConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=D:\ttt.csv;Extended Properties='Excel 8.0;HDR=Yes'"
adoRecordset.Open "select * from [sheet1$]", adoConnection, adOpenKeyset, adLockOptimistic
    Do Until adoRecordset.EOF
        mondata(i) = adoRecordset.Fields.Item(0).Value
        i = i + 1
     adoRecordset.MoveNext
    Loop
    'adoRecordset.Close
    'adoConnection.Close
But after saving with your method, an error is reported when you run the above program, as: the external table is not in the expected format

This is the traditional method, it's too slow
Dim newXls As Excel.Application
Dim newBook ​​As Excel.Workbook
Dim newSheet As Excel.Worksheet
Set newXls = CreateObject("Excel.Application")
Set newBook ​​= newXls.Workbooks.Open(d:\ttt.csv)'Open the existing EXCEL workbook file
    newXls.Visible = False'Set the EXCEL object to be visible (or invisible)
Set newSheet = newBook.Worksheets(command)'Set the active worksheet
    For i = 0 To 1799
        mondata(i) = Val(newSheet.Cells(i + 1, 1))'Assign a value to the cell (row, col)
    Next i
    newBook.Application.Quit
Set newXls = Nothing

jcsexy

Private Sub Cmd_export_Click()
Dim strSql As String
Dim keycode As String

On Error GoTo err
If Trim(Cbo_date1.Text) = "" Or Trim(Cbo_date2.Text) = "" Then
MsgBox "Please select the specific settlement date for export!", vbOKOnly + vbExclamation, "Warning"
Cbo_date1.SetFocus
End If

keycode = Trim(Cbo_date1.Text)&lpad(Trim(Cbo_date2.Text), 2, "0")

strSql = "SELECT * FROM t_monthtotal where total_no ='"&Trim(keycode)&"'"

ExportExcel (strSql)

fin: Exit Sub
err:
    MsgBox "There is an error, please check the data or check the program", vbOKOnly + vbExclamation, "Warning"
    Resume err

End Sub

'''---Quote Microsoft Excel 11.0 Object Library
   
Public Function ExportExcel(ByVal strSql As String)
      On Error GoTo err
      'Define Excel object
      Dim priXLS As Excel.Application
      Dim priWorkbook As Excel.Workbook
      Dim priSheet As Excel.Worksheet
      'Rs temporary record set
      Dim Rs As New ADODB.Recordset
      Dim lngRow As Long, lngRows As Long, intField As Integer, intFields As Integer
      
      Screen.MousePointer = vbHourglass
      'Open the record set, get the data, and import the data into the Excel table
      
      Dim cnn As ADODB.Connection
      Set cnn = New ADODB.Connection
          cnn.Provider = "SQLOLEDB"
          cnn.Open ConnectString
         
      If Rs.State Then Rs.Close
      Rs.Open strSql, cnn, adOpenKeyset, adLockOptimistic
      If Rs.RecordCount = 0 Then GoTo err
   
      Set priXLS = New Excel.Application
      Set priWorkbook = priXLS.Workbooks.Add
      Set priSheet = priXLS.Sheets(1)
      With priSheet
          intFields = Rs.Fields.Count
          ''' to the field header
          For intField = 1 To intFields
              .Cells(1, intField) = "'"&Rs(intField-1).Name
          Next
          Rs.MoveLast
          lngCount = Rs.RecordCount
          Rs.MoveFirst
          ''' to the value of the field
          For lngID = 1 To lngCount
              For intField = 1 To intFields
                  .Cells(lngID + 1, intField) = "'"&Rs(intField-1).Value
              Next
              Rs.MoveNext
          Next
      End With
      priXLS.Visible = True
err:
      Screen.MousePointer = 0
End Function

jcsexy

Public Function ConnectString() _
   As String
'returns a DB ConnectString
   ConnectString = "Server=(local);Database=fin;Uid=sa;Pwd="
End Function