Серый форум — VBS: не получается сумму с копейками записать в ячейку Excel как число

Re: VBS: не получается сумму с копейками записать в ячейку Excel как число

2015-01-09T09:43:32Z

И здесь нарисовался .

Re: VBS: не получается сумму с копейками записать в ячейку Excel как число

2014-12-08T08:52:33Z

Slav пишет:

Не проще брать число и писать число?

Попробовал - действительно проще ;-)
Здравствуйте все!
Прошу прощения за перерыв в сообщениях - сначала неделю готовился к отпуску, потом отпуск, потом неделю разгребал после отпуска накопившиеся дела. А тут подошло время отчёта за ноябрь - хочешь-не хочешь, а пришлось вернуться к теме ;-)
Нашёл, как решить проблему (пока "пытался выделить из всего большого скрипта маленький кусочек" ;-).
После того, как происходит заполнение листа книги числами, в скрипте ещё проходит замена шаблонных значений в строках на реальные значения (например, в шаблоне листа была строка "Отчёт за %mmmm_gggg%", и надо было в этой строке заменить "%mmmm_gggg%" на "ноябрь 2014").
Скрипт был написан в спешке и эта операция замены проделывалась над всеми ячейками листа:


   strWhat = "%" + CStr(Sheet1.Cells(constNomerZagolovka, jz).Value) + "%"    ' строка, которую заменяем
   strReplacement = CStr(Sheet1.Cells(i, jz).Value)                ' строка, на которую заменяем
   For iSvoInf = 1 To Sheet1SvoInf.UsedRange.Rows.Count
      For jSvoInf = 1 To Sheet1SvoInf.UsedRange.Columns.Count
         Sheet1SvoInf.Cells(iSvoInf, jSvoInf).Value = Replace(Sheet1SvoInf.Cells(iSvoInf, jSvoInf).Value, strWhat, strReplacement)        ' меняем 
      Next
   Next

Если же ограничить замену текста только ячейками, в которых действительно есть строка strWhat - то проблема решена - числа остаются числами - и целые, и с копейками.
Но загадка всё-таки осталась нерешённой - почему после замены текста целые числа остаются числами, а числа с копейками становятся текстом?
Прилагаю результат модифицированного (добавлена замена текста на текст по всем ячейкам листа, и шаг значения не 0.01, а 0.1) скрипта коллеги Slav.

+ открыть спойлер


    strWhat = "%mmmm_gggg%"    ' строка, которую заменяем
    strReplacement = "ноябрь 2014"    ' строка, на которую заменяем
    For iSvoInf = 1 To myWB.Worksheets(1).UsedRange.Rows.Count
       For jSvoInf = 1 To myWB.Worksheets(1).UsedRange.Columns.Count
          myWB.Worksheets(1).Cells(iSvoInf, jSvoInf).Value = Replace(myWB.Worksheets(1).Cells(iSvoInf, jSvoInf).Value, strWhat, strReplacement)        ' меняем 
       Next
    Next

Re: VBS: не получается сумму с копейками записать в ячейку Excel как число

2014-11-13T14:17:40Z

nicksh, присоединяюсь к словам коллеги Slav и в свою очередь добавлю - Вы в курсе, что Excel содержит встроенные механизмы для подключения к БД и выгрузки результатов запроса на лист ? При этом, если я не ошибаюсь, движок Excel-я автоматически определяет тип данных полей и соответственно форматирует тип ячеек.

Re: VBS: не получается сумму с копейками записать в ячейку Excel как число

2014-11-13T06:32:53Z

nicksh пишет:

...
Оба числа пишутся в ячейку как строки с точкой ("2.70" и "2.00").

nicksh,

ход Ваших мыслей для меня загадка.

Возвращаемся к исходной задаче - Вы переписываете из источника в приемник ("из БД в Excel").
В источнике Ваша "сумма с копейками" в каком формате хранится?
В числовом (float, double, ...) или строковом (string, varchar, ...)?
Всякое бывает, но я предположу что в числовом.

В приемнике Вы также хотите увидеть числовой формат - если я правильно понял.

Внимание вопрос: а зачем выполняя элементарное переписывание из числа в число Вы затеваете какие-то манипуляции на тему "как строки"?
Вступая на этот тернистый путь Вы действительно должны строго контролировать региональные настройки - причем с двух сторон: на сервере БД (источник) и в локальном Excel (приемник).
Более того - перейдя на соседний компьютер (где элементарно другие региональные настройки) Вы обнаружите что скрипт опять перестал работать.

Не проще брать число и писать число?

Re: VBS: не получается сумму с копейками записать в ячейку Excel как число

2014-11-12T23:52:53Z

nicksh пишет:

Пытаюсь выделить из всего большого (одновременная обработка четырёх файлов Excel) скрипта маленький кусочек, иллюстрирующий ошибку - пока не получается.

Ждём.

Re: VBS: не получается сумму с копейками записать в ячейку Excel как число

2014-11-12T16:38:11Z

nicksh пишет:

формирование ещё пары столбцов - когда значение в ячейку пишется как строка (с запятой) и строка (с точкой):

Так вот, в этом скрипте всё сработало так, как надо: в 4 столбце число с запятой выровнено как текст и перед ним есть предупреждение (восклицательный знак), в 5 столбце число с точкой записалось в ячейку как число и перед ним нет предупреждения.

А вот в реальном файле получается не так: число с копейками выглядит как число (и выровнено вправо), но перед ним есть предупреждение, а целое число - без предупреждения.
Оба числа пишутся в ячейку как строки с точкой ("2.70" и "2.00").

Re: VBS: не получается сумму с копейками записать в ячейку Excel как число

2014-11-12T16:26:30Z

Slav пишет:

3. C результатами - сюда.

Но в скрипт я добавил формирование ещё пары столбцов - когда значение в ячейку пишется как строка (с запятой) и строка (с точкой):


        myWB.Worksheets(1).Cells(iRow, 4).NumberFormat = "#,##0.00"
        myWB.Worksheets(1).Cells(iRow, 4).Value = CStr(testVal)

        myWB.Worksheets(1).Cells(iRow, 5).NumberFormat = "#,##0.00"
        myWB.Worksheets(1).Cells(iRow, 5).Value = replace(CStr(testVal), ",", ".")

Re: VBS: не получается сумму с копейками записать в ячейку Excel как число

2014-11-12T16:21:54Z

Slav пишет:

3. C результатами - сюда.

Re: VBS: не получается сумму с копейками записать в ячейку Excel как число

2014-11-12T15:25:47Z

nicksh пишет:

Пытаюсь выделить из всего большого (одновременная обработка четырёх файлов Excel) скрипта маленький кусочек, иллюстрирующий ошибку - пока не получается.
Пока маленький скрипт работает правильно - то есть оба числа - и целое, и с копейками пишутся как числа.

nicksh,

1. Скопируйте мой скрипт в vbs-файл.
2. Сделайте xls-шаблон Test01.xlsx в той же папке.
3. C результатами - сюда.

'
' тестируем формат
'

    rootDir = Left(WScript.ScriptFullName, instrrev(WScript.ScriptFullName,"\"))

    FileXLS = rootDir & "Test01.xlsx"

' открываем в EXCEL    
    Set objExcel = CreateObject("Excel.Application")
        objExcel.Visible = True
        objExcel.displayAlerts = False
    Set myWB  = objExcel.Workbooks.Open(FileXLS)
    
    iRow = 0
    Do While iRow < 500  '  максимум строк
        iRow = iRow + 1
        testVal = iRow/100.00
        
        myWB.Worksheets(1).Cells(iRow, 1).Select  '  встаем в текущую строку
        
        ' десятичное число
        myWB.Worksheets(1).Cells(iRow, 1).NumberFormat = "#,##0.00"
        myWB.Worksheets(1).Cells(iRow, 1).Value = testVal
        
        ' целое
        myWB.Worksheets(1).Cells(iRow, 2).NumberFormat = "0"
        myWB.Worksheets(1).Cells(iRow, 2).Value = testVal
        
        ' текст
        myWB.Worksheets(1).Cells(iRow, 3).NumberFormat = "@"
        myWB.Worksheets(1).Cells(iRow, 3).Value = testVal
    Loop  '  конец цикла по строкам

    myWB.save()
    objExcel.quit()

    Set objExcel = Nothing

WScript.Quit 0

Re: VBS: не получается сумму с копейками записать в ячейку Excel как число

2014-11-12T13:54:18Z

Slav пишет:

myWB.Worksheets(1).Cells(i_row,i_col).NumberFormat = "#,##0.00"
myWB.Worksheets(1).Cells(i_row,i_col).Value = 2.7
Гарантировано представит с 2-мя знаками после запятой, да еще и на разряды разделит.

Пока не помогло.
Пытаюсь выделить из всего большого (одновременная обработка четырёх файлов Excel) скрипта маленький кусочек, иллюстрирующий ошибку - пока не получается.
Пока маленький скрипт работает правильно - то есть оба числа - и целое, и с копейками пишутся как числа.

Re: VBS: не получается сумму с копейками записать в ячейку Excel как число

2014-11-12T11:59:28Z

В крайнем случае, можно наверное, выполнить такую функцию в VBA и получить текущий разделитель для Excel.


Function GetDecimalSplitter()
    GetDecimalSplitter = Mid(1/2,2,1)
End Function

Re: VBS: не получается сумму с копейками записать в ячейку Excel как число

2014-11-12T11:17:40Z

nicksh пишет:

...
хотя весь столбец отформатирован как числовой с двумя знаками после запятой
...
Как "надёжно" записать число в ячейку - чтобы число оставалось числом всегда, независимо от мнения Excel по этому поводу?

nicksh,

1. Не надейтесь на предварительное форматирование xls-шаблона при заполнении из внешнего скрипта - при записи в каждую ячейку из своего скрипта сами гарантируйте тот формат, который хотите видеть.

myWB.Worksheets(1).Cells(i_row,i_col).NumberFormat = "#,##0.00"
myWB.Worksheets(1).Cells(i_row,i_col).Value = 2.7

Гарантировано представит с 2-мя знаками после запятой, да еще и на разряды разделит.

В эксельных форматах есть одна особенность, которая меня поначалу удивляла - они имеют фиксированные разделители, независимо от региональных настроек.

Re: VBS: не получается сумму с копейками записать в ячейку Excel как число

2014-11-11T22:49:28Z

В скрипте vbs пишу данные из БД в файл Excel.

И где Ваш код? Где исходные данные, на которых можно его проверить?

Re: VBS: не получается сумму с копейками записать в ячейку Excel как число

2014-11-11T20:25:52Z

По ходу дело в разделителе дробной и целой части. Региональные настройки влияют.

VBS: не получается сумму с копейками записать в ячейку Excel как число

2014-11-11T15:08:01Z

Здравствуйте всем!
В скрипте vbs пишу данные из БД в файл Excel.
В одной строке пишу в ячейку сумму с копейками (2.70), в другой строке в том же столбце - сумму без копеек (2.00).
Потом открываю файл в Excelе, и вижу сумму без копеек как число (2,00), а сумму с копейками - как текст (2,7), хотя весь столбец отформатирован как числовой с двумя знаками после запятой.
При выделении ячейки с суммой с копейками слева появляется квадратик с восклицательным знаком и текстом "Число в этой ячейке отформатировано как текст, или перед ним стоит апостроф".
Как "надёжно" записать число в ячейку - чтобы число оставалось числом всегда, независимо от мнения Excel по этому поводу?