I want to input the value retrieved with SQL into Excel, but I don't know how to do it.
The data can be taken out.
If the previous row of the Excel cell is different from Wri (RowCnt, 0) fetched from SQL, enter it and then search for a column with the same value as Wri (RowCnt, 1) and Wri (RowCnt, 4) I want to input a value.
For i = 9 To UBound (Wri) will go, but it will not move forward with an error that an object is required after that.
Please teach me.
Public Function Output_202 (s () As String) As String Dim outputfile As String Dim sSQL As String Dim rst As ADODB.Recordset Dim sNen As String Dim sSin As String Dim sSgyo As String Dim sHday As String Dim Rec () As Variant Dim RowCnt As Long Dim ColCnt As Long Dim Wri () As Variant Dim wbk As Workbook Dim wks As Worksheet Dim i As Long Dim j As Long Dim c As Long 'Set of arguments sNen = Mid $(s (1), 7, 2) 'FY sSin = Mid $(s (1), 6, 1) 'New/Resending sSgyo = Mid $(s (1), 12, 4) 'working date sHday = s (2) 'Ship Date 'Prepare output file outputfile = sNen&"year" If sSin = "2" Then outputfile = outputfile&"_resend" outputfile = outputfile&"_Number table-Number of dispatches by course/vault_" outputfile = outputfile&Format $(Date $, "yyyymmdd")&Format $(Time $, "hhmmss")&".xlsx" If file_copy ("Z202.xlsx", sFolderFormatFile, outputfile, sFolderSaveFile) = 9 Then 'File copy error Output_202 = "Cannot get template file Z202.xlsx" Exit Function End If Application.ScreenUpdating = False Set wbk = Workbooks.Open (sFolderSaveFile&outputfile) Application.ScreenUpdating = True 'Read data 'On Error GoTo ERR_RTN: 'Get data from DB If access_mySQL_Server = False Then GoTo ERR_RTN End If With cmd sSQL = "SELECT concat (wfz_bkcode, ':', wfz_bkname), concat (wfz_kzcode, cast (wfz_h_flg as char)) as code, wfz_count, wfz_test, count (wfz_bkcode)" sSQL = sSQL&"from"&s (1)&", kzz" sSQL = sSQL&"where wfz_kzcode = kzz_kzcode" sSQL = sSQL&"group by wfz_bkcode, code" .ActiveConnection = cnn .CommandText = sSQL Set rst = .Execute sSQL = "" ColCnt = rst.Fields.Count RowCnt = rst.RecordCount If RowCnt>0 Then ReDim Rec (ColCnt-1, RowCnt-1) Rec = rst.GetRows End If ReDim Wri (RowCnt, ColCnt) Set wks = wbk.Worksheets (1) For RowCnt = 0 To UBound (Rec, 2) Wri (RowCnt, 0) = CStr (Rec (0, RowCnt)) Wri (RowCnt, 1) = CStr (Rec (1, RowCnt)) If Len (Wri (RowCnt, 2)) = 1 Then Wri (RowCnt, 2) = "No."&StrConv (Wri (RowCnt, 2), vbWide)&"Volume" ElseIf Len (Wri (RowCnt, 2)) = 2 Then Wri (RowCnt, 2) = StrConv (Mid $(Wri (RowCnt, 2), 1, 1), vbWide)&"/"&StrConv (Mid $(Wri (RowCnt, 2), 2, 1), vbWide)&"Volume" End If If Wri (RowCnt, 3)<>"" Then Wri (RowCnt, 3) = "issue"&CStr (Rec (3, RowCnt))&"times" Else Wri (RowCnt, 3) = "×" End If Wri (RowCnt, 4) = Rec (4, RowCnt) Next For i = 9 To UBound (Wri) If wks.Cells (i-1, 2) .Value<>Wri (RowCnt, 0) .Value Then wks.Cells (i, 2) .Value = Wri (RowCnt, 0) For c = 4 To 38 If wks.Cells (5, c) = Wri (RowCnt, 1) Then j = wks.Cells (5, c) .colomn Exit For End If Next wks.Cells (i, j) .Value = Wri (RowCnt, 4) .Value Next wks.Cells (1, 4) .Value = Format $(Date $, "yyyy/m/d") wks.Cells (3, 3) .Value = "20"&Format $(sSgyo, "## Year # Month") wks.Select wks.Cells (1, 1) .Select End With If close_mySQL_Server = False Then GoTo ERR_RTN End If Application.DisplayAlerts = False wbk.Save Application.DisplayAlerts = True wbk.Close Set wks = Nothing Set wbk = Nothing Output_202 = outputfile Exit Function ERR_RTN: If Len (sErr (0)) = 0 Then sErr (0) = CStr (Err.Number) sErr (1) = Err.Description sErr (2) = "Output_202" sErr (3) = sSQL End If End Function
Answer # 1
For i = 9 To UBound (Wri) will go, but you can't move forward with an error that an object is needed after that.
Wri ()is declared as Variant, but the assigned value is String?
If wks.Cells (i-1, 2) .Value<>Wri (RowCnt, 1) .Value Then wks.Cells (i, 2) .Value = Wri ( RowCnt, 1)
This ↓ also wks.Cells (i, j) .Value = Wri (RowCnt, 4) .Value
- compare excel vba, lists a and b, and if it is a new value, enter new
- excel vba i want to update the acquired query data
- excel vba cell time value to list box
- excel vba:how to stop when the value reaches 0 using autofill
- excel vba [i want to auto filter by the value of the list box]
- vba - about excel functions
- an error occurs when pasting the value of the vba pastespecial method
- excel drops when you make a graph with vba
- vba - fetch the row with the maximum value among multiple conditions
- vba - csv, excel import, i want to compare the two and extract the target data
- i want to connect to mysql with vba and extract the data to the excel file sheet
- questions about excel vba
- compile error - i want to send the acquired sensor value to line via ifttt using esp32
- vba - excel count function
- (python,xlwings) i want to get the "user defined" value (time) from excel with xlwings in the same format
- about clicking links with vba in excel
- i want to auto-fill the value acquired by php onclick
- get path with drag&drop without using excel vba userform
- vba - i want to search for a specific value and delete the value entered in all applicable cells
- vba - enter value in combo box with selenium basic xpath
- php - coincheck api authentication doesn't work
- php - i would like to introduce the coincheck api so that i can make payments with bitcoin on my ec site
- [php] i want to get account information using coincheck api
- python - you may need to restart the kernel to use updated packages error
- python 3x - typeerror: 'method' object is not subscriptable
- the emulator process for avd pixel_2_api_29 was killed occurred when the android studio emulator was started, so i would like to
- xcode - pod install [!] no `podfile 'found in the project directory
- vuejs - [vuetify] unable to locate target [data-app] i want to unit test to avoid warning
- android studio - unresolved reference comes out in kotlin