Last active
March 10, 2019 12:18
-
-
Save ezhov-da/2eeb3ebdedd007efcca486cb8ef03fa5 to your computer and use it in GitHub Desktop.
vba выгрузка данных на лист
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Private Sub unloadDataFromDb(query As String, startRow As Long, ado As ADODB.connection) | |
Dim newWb As Workbook | |
Set newWb = Application.Workbooks.Add | |
newWb.Activate | |
Dim myRecordset As ADODB.Recordset | |
Set myRecordset = ado.execute(query) | |
Dim row As Long | |
row = startRow | |
Dim column As Long | |
Dim fld | |
Do Until myRecordset.EOF | |
column = 1 | |
For Each fld In myRecordset.Fields | |
'так как храним числа как строки, то возникают проблемы с преобразованием | |
'в форматы, сдесь в ручную проверям и вставляем свой разделитель | |
'смотрим можем ли преобразовать эти данные число | |
Dim d As Double | |
Dim val As Variant | |
val = fld.Value | |
On Error Resume Next | |
d = CDbl(val) | |
If Err.Number > 0 Then | |
'не можем преобразовать в число | |
Cells(row, column).Value = val | |
Else | |
Dim systemSep As String | |
systemSep = Util.getSystemSeparator | |
Dim sepForReplace As String | |
If (systemSep = ",") Then | |
sepForReplace = "." | |
Else | |
sepForReplace = "," | |
End If | |
val = Replace(val, sepForReplace, systemSep) | |
Cells(row, column).Value = CDbl(val) | |
End If | |
On Error GoTo 0 | |
column = column + 1 | |
Next fld | |
row = row + 1 | |
myRecordset.MoveNext | |
Loop | |
myRecordset.Close | |
Set myRecordset = Nothing | |
End Sub |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment