Уважаемый форум!
Я только начинаю разбираться с VB (Visual Studio 2010), поэтому просьба не пинать сильно.
Задача: Необходимо перелопатить несколько Эксель-файлов и перенести данные в другие Эксель-файлы. Не спрашивайте, зачем - так надо. Вопрос в другом. Слепил код:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
Imports Microsoft.Office.Interop
Module Module1
Dim xl As Excel.Application, xbk As Excel.Workbook, xsh As Excel.Worksheet, fl As String, mnt As String, cntry As String
Dim xl_read As Excel.Application, xbk_read As Excel.Workbook, xsh_read As Excel.Worksheet, mnt_no As String, yhr As String
Sub Main()
yhr = InputBox("Insert the year in format XXXX for which the reports are being loaded", "Year input", "")
If yhr = "" Or yhr < 2000 Then
MsgBox("Your input is not valid!", vbCritical, "Error")
Exit Sub
End If
fl = Dir("*.xls")
Do
If InStr(fl, "Operating Cost", vbTextCompare) > 0 Then
xl = CreateObject("Excel.application")
xl_read = CreateObject("Excel.application")
xbk_read = xl_read.Workbooks.Open(fl)
xsh_read = xbk_read.ActiveSheet
mnt = Right(xsh_read.Cells(5, 2).Value, Len(xsh_read.Cells(5, 2).Value) - 8)
Select Case mnt
Case "January"
mnt_no = "01"
Case "February"
mnt_no = "02"
Case "March"
mnt_no = "03"
Case "April"
mnt_no = "04"
Case "May"
mnt_no = "05"
Case "June"
mnt_no = "06"
Case "July"
mnt_no = "07"
Case "August"
mnt_no = "08"
Case "September"
mnt_no = "09"
Case "October"
mnt_no = "10"
Case "November"
mnt_no = "11"
Case "December"
mnt_no = "12"
End Select
cntry = Right(xsh_read.Cells(3, 2).Value, Len(xsh_read.Cells(3, 2).Value) - 8)
xbk = xl.Workbooks.Open("FI_data_Layout_for_upload.xls")
xbk.SaveAs("FI_data_Layout_for_upload_" & cntry & ".xls")
xbk.Close()
xbk = xl.Workbooks.Open("FI_data_Layout_for_upload_" & cntry & ".xls")
xsh = xbk.Worksheets(1)
xsh.Range("B2", "B49").Value = mnt_no & yhr
xsh.Range("C2", "C49").Value = cntry
xsh.Cells(2, 6).Value = xsh_read.Range("D10").Value <= разные варианты внесения
xsh.Cells(3, 6).Value = xsh_read.Range("D11").Value
xsh.Cells(4, 6).Value = xsh_read.Range("D12").Value
xsh.Cells(5, 6).Value = xsh_read.Range("D13").Value
xsh.Range("G6").Value = xsh_read.Range("D14").Value <= разные варианты внесения
xsh.Range("G7").Value = xsh_read.Range("D15").Value
xsh.Range("F8").Value = xsh_read.Range("D16").Value
xsh.Range("F9").Value = xsh_read.Range("D17").Value
xsh.Range("F10").Value = xsh_read.Range("D18").Value
xsh.Range("F11").Value = xsh_read.Range("D21").Value
xsh.Range("G12").Value = xsh_read.Range("D22").Value
xsh.Range("G13").Value = xsh_read.Range("D23").Value
xsh.Range("F14").Value = xsh_read.Range("D24").Value
xsh.Range("G15").Value = xsh_read.Range("D25").Value
xsh.Range("G16").Value = xsh_read.Range("D26").Value
xsh.Range("F17").Value = xsh_read.Range("D27").Value
xsh.Range("F18").Value = xsh_read.Range("D28").Value
xsh.Range("F19").Value = xsh_read.Range("D29").Value
xsh.Range("F20").Value = xsh_read.Range("D32").Value
xsh.Range("F21").Value = xsh_read.Range("D33").Value
xsh.Range("F22").Value = xsh_read.Range("D36").Value
xsh.Range("F23").Value = xsh_read.Range("D37").Value
xsh.Range("F24").Value = xsh_read.Range("D42").Value
xsh.Range("F25").Value = xsh_read.Range("D43").Value
xsh.Range("F26").Value = xsh_read.Range("H10").Value
xsh.Range("G27").Value = xsh_read.Range("H11").Value
xsh.Range("G28").Value = xsh_read.Range("H12").Value
xsh.Range("F29").Value = xsh_read.Range("H13").Value
xsh.Range("G30").Value = xsh_read.Range("H14").Value
xsh.Range("G31").Value = xsh_read.Range("H15").Value
xsh.Range("F32").Value = xsh_read.Range("H16").Value
xsh.Range("F33").Value = xsh_read.Range("H17").Value
xsh.Range("F34").Value = xsh_read.Range("H18").Value
xsh.Range("F35").Value = xsh_read.Range("H21").Value
xsh.Range("G36").Value = xsh_read.Range("H22").Value
xsh.Range("G37").Value = xsh_read.Range("H23").Value
xsh.Range("F38").Value = xsh_read.Range("H24").Value
xsh.Range("G39").Value = xsh_read.Range("H25").Value
xsh.Range("G40").Value = xsh_read.Range("H26").Value
xsh.Range("F41").Value = xsh_read.Range("H27").Value
xsh.Range("F42").Value = xsh_read.Range("H28").Value
xsh.Range("F43").Value = xsh_read.Range("H29").Value
xsh.Range("F44").Value = xsh_read.Range("H32").Value
xsh.Range("F45").Value = xsh_read.Range("H33").Value
xsh.Range("F46").Value = xsh_read.Range("H36").Value
xsh.Range("F47").Value = xsh_read.Range("H37").Value
xsh.Range("F48").Value = xsh_read.Range("H42").Value
xsh.Range("F49").Value = xsh_read.Range("H43").Value
xbk.Close(True)
xl.Quit()
xbk_read.Close(False)
xl_read.Quit()
xsh = Nothing
xsh_read = Nothing
xbk = Nothing
xbk_read = Nothing
xl_read = Nothing
xl = Nothing
End If
fl = Dir()
Loop Until fl = ""
MsgBox("Finished!")
End Sub
End Module
Но почему-то не работает внесение данных в целевой файл. Подскажите, плиз, че не так и куда копать. Если есть ссылки, где ВНЯТНО почитать про работу с файлами Эксель, ВКЛЮЧАЯ описание того, КАКИЕ БИБЛИОТЕКИ необходимо подключить для нормальной работы - буду очень благодарен. Спасибо заранее!
