IZONE- http://www.izcity.com/- бесплатный софт, вэб-сервисы, ресурсы для раскрутки, свежие номера журнала "Internet Zone".

Макрос для выгрузки таблицы в формате DBF файла

Владислав Корольков

Продолжая тему макросов для MS Excel, предлагаю еще один макрос, который позволяет выгружать информацию из рабочего листа MS Excel в файл заданной структуры формата dbf. Если учесть, что электронные таблицы это универсальное средство для создания и обработки информации, организованной в виде таблиц, то в условиях частых изменений в законодательной базе, касающихся учета и отчетности предприятий, MS Excel широко применяется на многих предприятиях, как средство быстрого формирования документов. Вместе с тем, в Украине все шире применяется форма получения информации от предприятий на дискетах в виде файлов формата dbf. Если при сохранении книги (листа) MS Excel выбрать режим "сохранить как …", то, кроме изменения имени и места расположения нового файла, имеется возможность и выбора типа файла DBaseIV. Но при этом полностью отсутствует возможность выбора формы представления полей данных, создаваемого файла. Это приводит к тому, что сохранить файл можно, но его структура не будет удовлетворять вашим требованиям. Средства, имеющиеся в MS Excel позволяют загружать файлы типа dbf и после редактирования выгружать их. Однако при такой процедуре форматы полей типа дата заменяются на текстовые. Это делает не возможным дальнейшее применение таких файлов в информационных системах.

Таким образом, если ставится задача сохранения информации рабочего листа MS Excel в формате файла типа dbf со строго оговоренной структурой полей и формой их представления, то выполнить ее без применения специальных программ вообще не возможно. Это объясняется тем, что нет однозначного соответствия между форматом поля определенным MS Excel и форматом соответствующего поля dbf файла.

Макрос выгрузки файла в формате Dbase IV из листа Excel

Предлагаемый макрос позволяет легко решить эту задачу. Для определения структуры будущего dbf файла данный макрос предполагает наличие на жестком диске пустого файла заданной структуры с именем STRUCT.DBF. Создать такой файл несложно при помощи утилит для обслуживания DBF файлов. Новый файл будет иметь имя книги Excel, а расширение dbf.

Чтобы воспользоваться данным макросом его необходимо импортировать в книгу MS Excel. Для удобства вызова данного макроса ему можно назначить "горячую клавишу". Это можно сделать следующим образом:

  • Выбрать макрос DBF_FILE (Сервис --> Макрос --> Макросы…);
  • Назначить "горячую клавишу" ( кнопка "параметры" ).

Следует заметить, что данный модуль использует библиотеку Microsoft DAO 2.5/3.5 Compatibility Library. Если эта библиотека не подключена, запуск макроса будет сопровождаться ошибкой компиляции из-за отсутствия ссылок на данную библиотеку. Для подключения этой библиотеки необходимо находясь в редакторе Visual Basic выполнить следующую последовательность команд: Сервис --> Ссылки --> Выбрать, и отметить библиотеку Microsoft DAO 2.5/3.5 --> ОК.

Листинг Модуля

  • Attribute VB_Name = "Модуль1"
  • ' Выгрузка информации листа Excel в файл формат DBF
  • ' Макрос записан 20.12.99 (Владислав Корольков)
  • Sub DBF_FILE()
  • Application.ScreenUpdating = False
  • ' Определение имени и пути
  • ' сохранения нового файла
  • A$ = ActiveWorkbook.FullName
  • ChDrive Mid(A$, 1, 1)
  • ChDir ActiveWorkbook.Path
  • n$ = ActiveWorkbook.Name
  • im$ = Mid(n$, 1, Len(n$) - 4) + ".dbf"
  • ' Проверить наличие файла с таким именем на диске
  • ' Если есть запросить подтверждение на его удаление
  • fl = True
  • If Dir(im$) <> "" Then
  • response = MsgBox("Обнаружен файл " + im$ + " Удалить его?", _
  • vbQuestion + vbYesNo, "Формирование файла в DBF формате")
  • If response = vbYes Then
  • Kill im$
  • Else
  • fl = False
  • End If
  • End If
  • If fl = True Then
  • ' Создать файл заданной структуры
  • FileCopy "struct.dbf", im$
  • P$ = ActiveWorkbook.Path
  • Dim db As Database, rs As Recordset, categorycell As Range
  • Set db = OpenDatabase(P$, False, False, "dBASE IV")
  • Set rs = db.OpenRecordset(im$, dbOpenDynaset)
  • it = rs.Fields.Count ' определить количество полей
  • ' переписать информацию с активного рабочего листа в файл
  • With ActiveSheet
  • j = 2 ' первая строка содержит наименования полей
  • If Not .Cells(j, 1) = Empty Then
  • Do While Not .Cells(j, 1) = Empty
  • rs.AddNew
  • For i = 0 To it - 1
  • ' j - номер строки
  • ' i - номер столбца
  • rs.Fields(i) = .Cells(j, i + 1)
  • Next i
  • rs.Update
  • j = j + 1
  • Loop
  • End If
  • End With
  • ' Закрыть файл и завершить работу
  • rs.Close
  • db.Close
  • Application.ScreenUpdating = True
  • response = MsgBox("Создан файл " + im$, _
  • vbInformation, "Формирование файла в DBF формате")
  • End If
  • End Sub

Вначале модуля определяется директория, из которой загружена книга и в которой должен находиться файл структуры. Затем формируется имя DBF файла. Перед созданием требуемого файла проверяется наличие файла с таким же именем на диске в данной директории и, если такой файл имеется, то запрашивается согласие на его удаление. После этого файл структуры переписывается под требуемым именем. Далее следуют операторы открытия файла формата "dBASE IV". Оператор

it = rs.Fields.Count

определяет количество полей в файле. Далее выполняется настройка на активный рабочий лист и выполняется его построчное сканирование. Для каждой строки добавляется новая запись в файле. Оператор

rs.Fields(i) = .Cells(j, i + 1)

выполняет по реквизитную пересылку полей из заданной строки в запись файла. Именно при выполнении этого оператора выполняется преобразование форматов. Признаком завершения информации является пустая ячейка в первом столбце.

Рассмотрим информационную технологию выгрузки файла в формате DBF на примере формирования формы 8-ДР бухгалтерской отчетности о доходах работников предприятия с применением данного макроса. Первоначально создадим файл STRUCT.DBF требуемой структуры.

Структура файла
Имя поля Тип поля Ширина Десят.зн.
NP Numeric 5 0
PERIOD Numeric 1 0
RIK Numeric 4 0
KOD Character 10
TYP Numeric 1 0
TIN Character 10
S_DOX Numeric 12 2
S_TAX Numeric 12 2
OZN_DOX Numeric 2 0
D_PRIYN Date 8
D_ZVILN Date 8
OZN_PILG Numeric 2 0
OZNAKA Numeric 1 0

Создадим книгу MS Excel с требуемым именем DAXXXX, импортируем в нее макрос и назначим "горячую клавишу". На первом листе книги в первой строке разместим все наименования полей. После этого в таблицу можно вносить всю требуемую информацию средствами MS Excel. Для выгрузки информации в формате DBF необходимо только нажать "горячую клавишу".

Следует отметить, что корректность введенных данных и соответствие форме представления производится средствами MS Excel в момент выгрузки данных. При обнаруженном несоответствии формы представления данных будет выдана информация о соответствующей ошибке. Если в предложенном диалоговом окне выбрать режим отладка, то останов произойдет на операторе:

rs.Fields(i) = .Cells(j, i + 1)

При этом по значению j и i можно однозначно определить поле с некорректным значением.

P.S. О макросах Excel и примере информационной системы предприятия, разработанной с применение макросов можно ознакомиться на странице http://www.vikor.zp.ua

 


Copyright © "Internet Zone", http://www.izcity.com/, info@izcity.com