API tools faq
paste
Guest User

Untitled

a guest
Oct 6th, 2016
85
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 19.27 KB | None | 0 0
raw download clone embed print report
  1. Imports Excel = Microsoft.Office.Interop.Excel
  2. Imports System.Data
  3. Public Class Form1
  4. Dim APP As New Excel.Application
  5. Dim worksheet As Excel.Worksheet
  6. Dim workbook As Excel.Workbook
  7. Dim TotalEntriesTemp As Long = 0
  8. Dim Entries As Long = 0
  9. Dim TotalEntries As Long = 0
  10. Dim State As String = ""
  11.  
  12. Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  13. 'Add Filters to ComboBox
  14. Filter1.Items.Add("STATE")
  15. End Sub
  16.  
  17. Private Sub btnLoad_Click(sender As Object, e As EventArgs) Handles btnLoad.Click
  18. 'Make State(String) = Filter2.Text
  19. State = Filter2.Text
  20.  
  21. 'Make TextBoxes visible=false
  22. txtBN1.Visible = False
  23. txtFACID1.Visible = False
  24. txtMGMT1.Visible = False
  25. txtCOMMENT1.Visible = False
  26. txtCITY1.Visible = False
  27. txtPM1.Visible = False
  28. txtTYPE1.Visible = False
  29. txtACTIVITY1.Visible = False
  30. txtBN2.Visible = False
  31. txtFACID2.Visible = False
  32. txtMGMT2.Visible = False
  33. txtCOMMENT2.Visible = False
  34. txtCITY2.Visible = False
  35. txtPM2.Visible = False
  36. txtTYPE2.Visible = False
  37. txtACTIVITY2.Visible = False
  38. txtBN3.Visible = False
  39. txtFACID3.Visible = False
  40. txtMGMT3.Visible = False
  41. txtCOMMENT3.Visible = False
  42. txtCITY3.Visible = False
  43. txtPM3.Visible = False
  44. txtTYPE3.Visible = False
  45. txtACTIVITY3.Visible = False
  46. txtBN4.Visible = False
  47. txtFACID4.Visible = False
  48. txtMGMT4.Visible = False
  49. txtCOMMENT4.Visible = False
  50. txtCITY4.Visible = False
  51. txtPM4.Visible = False
  52. txtTYPE4.Visible = False
  53. txtACTIVITY4.Visible = False
  54. txtBN5.Visible = False
  55. txtFACID5.Visible = False
  56. txtMGMT5.Visible = False
  57. txtCOMMENT5.Visible = False
  58. txtCITY5.Visible = False
  59. txtPM5.Visible = False
  60. txtTYPE5.Visible = False
  61. txtACTIVITY5.Visible = False
  62. txtBN6.Visible = False
  63. txtFACID6.Visible = False
  64. txtMGMT6.Visible = False
  65. txtCOMMENT6.Visible = False
  66. txtCITY6.Visible = False
  67. txtPM6.Visible = False
  68. txtTYPE6.Visible = False
  69. txtACTIVITY6.Visible = False
  70. txtBN7.Visible = False
  71. txtFACID7.Visible = False
  72. txtMGMT7.Visible = False
  73. txtCOMMENT7.Visible = False
  74. txtCITY7.Visible = False
  75. txtPM7.Visible = False
  76. txtTYPE7.Visible = False
  77. txtACTIVITY7.Visible = False
  78. txtBN8.Visible = False
  79. txtFACID8.Visible = False
  80. txtMGMT8.Visible = False
  81. txtCOMMENT8.Visible = False
  82. txtCITY8.Visible = False
  83. txtPM8.Visible = False
  84. txtTYPE8.Visible = False
  85. txtACTIVITY8.Visible = False
  86.  
  87. 'workbook = APP.Workbooks.Open("Path\AZ.xlsx")
  88.  
  89. 'Load the correct worksheet
  90. workbook = APP.Workbooks.Open("C:\Users\USER\Desktop\" & State & ".xlsx")
  91. worksheet = workbook.Worksheets("Sheet1")
  92. 'Count entries
  93. TotalEntries = worksheet.Range("B1048576").End(Excel.XlDirection.xlUp).Row
  94. TotalEntriesTemp = TotalEntries
  95.  
  96. If TotalEntries > 7 Then
  97. Entries = 7
  98. TotalEntriesTemp = TotalEntriesTemp - (TotalEntriesTemp - Entries)
  99. btnNext.Visible = True
  100. Else
  101. Entries = TotalEntries
  102. End If
  103.  
  104. 'Display corrent number of tables
  105. If Entries = 0 Then
  106. MsgBox("No Entries!")
  107. End If
  108. If Entries <= 2 Then
  109. txtBN1.Visible = True
  110. txtFACID1.Visible = True
  111. txtMGMT1.Visible = True
  112. txtCOMMENT1.Visible = True
  113. txtCITY1.Visible = True
  114. txtPM1.Visible = True
  115. txtTYPE1.Visible = True
  116. txtACTIVITY1.Visible = True
  117. End If
  118. If Entries = 2 Then
  119. txtBN2.Visible = True
  120. txtFACID2.Visible = True
  121. txtMGMT2.Visible = True
  122. txtCOMMENT2.Visible = True
  123. txtCITY2.Visible = True
  124. txtPM2.Visible = True
  125. txtTYPE2.Visible = True
  126. txtACTIVITY2.Visible = True
  127. txtBN1.Visible = True
  128. txtFACID1.Visible = True
  129. txtMGMT1.Visible = True
  130. txtCOMMENT1.Visible = True
  131. txtCITY1.Visible = True
  132. txtPM1.Visible = True
  133. txtTYPE1.Visible = True
  134. txtACTIVITY1.Visible = True
  135. End If
  136. If Entries = 3 Then
  137. txtBN3.Visible = True
  138. txtFACID3.Visible = True
  139. txtMGMT3.Visible = True
  140. txtCOMMENT3.Visible = True
  141. txtCITY3.Visible = True
  142. txtPM3.Visible = True
  143. txtTYPE3.Visible = True
  144. txtACTIVITY3.Visible = True
  145. txtBN2.Visible = True
  146. txtFACID2.Visible = True
  147. txtMGMT2.Visible = True
  148. txtCOMMENT2.Visible = True
  149. txtCITY2.Visible = True
  150. txtPM2.Visible = True
  151. txtTYPE2.Visible = True
  152. txtACTIVITY2.Visible = True
  153. txtBN1.Visible = True
  154. txtFACID1.Visible = True
  155. txtMGMT1.Visible = True
  156. txtCOMMENT1.Visible = True
  157. txtCITY1.Visible = True
  158. txtPM1.Visible = True
  159. txtTYPE1.Visible = True
  160. txtACTIVITY1.Visible = True
  161. End If
  162. If Entries = 4 Then
  163. txtBN4.Visible = True
  164. txtFACID4.Visible = True
  165. txtMGMT4.Visible = True
  166. txtCOMMENT4.Visible = True
  167. txtCITY4.Visible = True
  168. txtPM4.Visible = True
  169. txtTYPE4.Visible = True
  170. txtACTIVITY4.Visible = True
  171. txtBN3.Visible = True
  172. txtFACID3.Visible = True
  173. txtMGMT3.Visible = True
  174. txtCOMMENT3.Visible = True
  175. txtCITY3.Visible = True
  176. txtPM3.Visible = True
  177. txtTYPE3.Visible = True
  178. txtACTIVITY3.Visible = True
  179. txtBN2.Visible = True
  180. txtFACID2.Visible = True
  181. txtMGMT2.Visible = True
  182. txtCOMMENT2.Visible = True
  183. txtCITY2.Visible = True
  184. txtPM2.Visible = True
  185. txtTYPE2.Visible = True
  186. txtACTIVITY2.Visible = True
  187. txtBN1.Visible = True
  188. txtFACID1.Visible = True
  189. txtMGMT1.Visible = True
  190. txtCOMMENT1.Visible = True
  191. txtCITY1.Visible = True
  192. txtPM1.Visible = True
  193. txtTYPE1.Visible = True
  194. txtACTIVITY1.Visible = True
  195. End If
  196. If Entries = 5 Then
  197. txtBN5.Visible = True
  198. txtFACID5.Visible = True
  199. txtMGMT5.Visible = True
  200. txtCOMMENT5.Visible = True
  201. txtCITY5.Visible = True
  202. txtPM5.Visible = True
  203. txtTYPE5.Visible = True
  204. txtACTIVITY5.Visible = True
  205. txtBN4.Visible = True
  206. txtFACID4.Visible = True
  207. txtMGMT4.Visible = True
  208. txtCOMMENT4.Visible = True
  209. txtCITY4.Visible = True
  210. txtPM4.Visible = True
  211. txtTYPE4.Visible = True
  212. txtACTIVITY4.Visible = True
  213. txtBN3.Visible = True
  214. txtFACID3.Visible = True
  215. txtMGMT3.Visible = True
  216. txtCOMMENT3.Visible = True
  217. txtCITY3.Visible = True
  218. txtPM3.Visible = True
  219. txtTYPE3.Visible = True
  220. txtACTIVITY3.Visible = True
  221. txtBN2.Visible = True
  222. txtFACID2.Visible = True
  223. txtMGMT2.Visible = True
  224. txtCOMMENT2.Visible = True
  225. txtCITY2.Visible = True
  226. txtPM2.Visible = True
  227. txtTYPE2.Visible = True
  228. txtACTIVITY2.Visible = True
  229. txtBN1.Visible = True
  230. txtFACID1.Visible = True
  231. txtMGMT1.Visible = True
  232. txtCOMMENT1.Visible = True
  233. txtCITY1.Visible = True
  234. txtPM1.Visible = True
  235. txtTYPE1.Visible = True
  236. txtACTIVITY1.Visible = True
  237. End If
  238. If Entries = 6 Then
  239. txtBN6.Visible = True
  240. txtFACID6.Visible = True
  241. txtMGMT6.Visible = True
  242. txtCOMMENT6.Visible = True
  243. txtCITY6.Visible = True
  244. txtPM6.Visible = True
  245. txtTYPE6.Visible = True
  246. txtACTIVITY6.Visible = True
  247. txtBN5.Visible = True
  248. txtFACID5.Visible = True
  249. txtMGMT5.Visible = True
  250. txtCOMMENT5.Visible = True
  251. txtCITY5.Visible = True
  252. txtPM5.Visible = True
  253. txtTYPE5.Visible = True
  254. txtACTIVITY5.Visible = True
  255. txtBN4.Visible = True
  256. txtFACID4.Visible = True
  257. txtMGMT4.Visible = True
  258. txtCOMMENT4.Visible = True
  259. txtCITY4.Visible = True
  260. txtPM4.Visible = True
  261. txtTYPE4.Visible = True
  262. txtACTIVITY4.Visible = True
  263. txtBN3.Visible = True
  264. txtFACID3.Visible = True
  265. txtMGMT3.Visible = True
  266. txtCOMMENT3.Visible = True
  267. txtCITY3.Visible = True
  268. txtPM3.Visible = True
  269. txtTYPE3.Visible = True
  270. txtACTIVITY3.Visible = True
  271. txtBN2.Visible = True
  272. txtFACID2.Visible = True
  273. txtMGMT2.Visible = True
  274. txtCOMMENT2.Visible = True
  275. txtCITY2.Visible = True
  276. txtPM2.Visible = True
  277. txtTYPE2.Visible = True
  278. txtACTIVITY2.Visible = True
  279. txtBN1.Visible = True
  280. txtFACID1.Visible = True
  281. txtMGMT1.Visible = True
  282. txtCOMMENT1.Visible = True
  283. txtCITY1.Visible = True
  284. txtPM1.Visible = True
  285. txtTYPE1.Visible = True
  286. txtACTIVITY1.Visible = True
  287. End If
  288. If Entries = 7 Then
  289. txtBN7.Visible = True
  290. txtFACID7.Visible = True
  291. txtMGMT7.Visible = True
  292. txtCOMMENT7.Visible = True
  293. txtCITY7.Visible = True
  294. txtPM7.Visible = True
  295. txtTYPE7.Visible = True
  296. txtACTIVITY7.Visible = True
  297. txtBN6.Visible = True
  298. txtFACID6.Visible = True
  299. txtMGMT6.Visible = True
  300. txtCOMMENT6.Visible = True
  301. txtCITY6.Visible = True
  302. txtPM6.Visible = True
  303. txtTYPE6.Visible = True
  304. txtACTIVITY6.Visible = True
  305. txtBN5.Visible = True
  306. txtFACID5.Visible = True
  307. txtMGMT5.Visible = True
  308. txtCOMMENT5.Visible = True
  309. txtCITY5.Visible = True
  310. txtPM5.Visible = True
  311. txtTYPE5.Visible = True
  312. txtACTIVITY5.Visible = True
  313. txtBN4.Visible = True
  314. txtFACID4.Visible = True
  315. txtMGMT4.Visible = True
  316. txtCOMMENT4.Visible = True
  317. txtCITY4.Visible = True
  318. txtPM4.Visible = True
  319. txtTYPE4.Visible = True
  320. txtACTIVITY4.Visible = True
  321. txtBN3.Visible = True
  322. txtFACID3.Visible = True
  323. txtMGMT3.Visible = True
  324. txtCOMMENT3.Visible = True
  325. txtCITY3.Visible = True
  326. txtPM3.Visible = True
  327. txtTYPE3.Visible = True
  328. txtACTIVITY3.Visible = True
  329. txtBN2.Visible = True
  330. txtFACID2.Visible = True
  331. txtMGMT2.Visible = True
  332. txtCOMMENT2.Visible = True
  333. txtCITY2.Visible = True
  334. txtPM2.Visible = True
  335. txtTYPE2.Visible = True
  336. txtACTIVITY2.Visible = True
  337. txtBN1.Visible = True
  338. txtFACID1.Visible = True
  339. txtMGMT1.Visible = True
  340. txtCOMMENT1.Visible = True
  341. txtCITY1.Visible = True
  342. txtPM1.Visible = True
  343. txtTYPE1.Visible = True
  344. txtACTIVITY1.Visible = True
  345. End If
  346.  
  347. txtBN8.Visible = True
  348. txtFACID8.Visible = True
  349. txtMGMT8.Visible = True
  350. txtCOMMENT8.Visible = True
  351. txtCITY8.Visible = True
  352. txtPM8.Visible = True
  353. txtTYPE8.Visible = True
  354. txtACTIVITY8.Visible = True
  355.  
  356. Call DisplayInfo()
  357. End Sub
  358.  
  359. Sub DisplayInfo()
  360. txtBN1.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 1), 3).Value
  361. txtFACID1.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 1), 2).Value
  362. txtMGMT1.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 1), 4).Value
  363. txtCOMMENT1.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 1), 10).Value
  364. txtCITY1.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 1), 5).Value
  365. txtPM1.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 1), 6).Value
  366. txtTYPE1.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 1), 7).value
  367. txtACTIVITY1.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 1), 8).value
  368. txtBN2.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 2), 3).Value
  369. txtFACID2.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 2), 2).Value
  370. txtMGMT2.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 2), 4).Value
  371. txtCOMMENT2.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 2), 10).Value
  372. txtCITY2.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 2), 5).Value
  373. txtPM2.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 2), 6).Value
  374. txtTYPE2.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 2), 7).value
  375. txtACTIVITY2.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 2), 8).value
  376. txtBN3.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 3), 3).Value
  377. txtFACID3.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 3), 2).Value
  378. txtMGMT3.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 3), 4).Value
  379. txtCOMMENT3.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 3), 10).Value
  380. txtCITY3.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 3), 5).Value
  381. txtPM3.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 3), 6).Value
  382. txtTYPE3.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 3), 7).value
  383. txtACTIVITY3.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 3), 8).value
  384. txtBN4.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 4), 3).Value
  385. txtFACID4.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 4), 2).Value
  386. txtMGMT4.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 4), 4).Value
  387. txtCOMMENT4.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 4), 10).Value
  388. txtCITY4.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 4), 5).Value
  389. txtPM4.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 4), 6).Value
  390. txtTYPE4.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 4), 7).value
  391. txtACTIVITY4.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 4), 8).value
  392. txtBN5.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 5), 3).Value
  393. txtFACID5.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 5), 2).Value
  394. txtMGMT5.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 5), 4).Value
  395. txtCOMMENT5.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 5), 10).Value
  396. txtCITY5.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 5), 5).Value
  397. txtPM5.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 5), 6).Value
  398. txtTYPE5.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 5), 7).value
  399. txtACTIVITY5.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 5), 8).value
  400. txtBN6.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 6), 3).Value
  401. txtFACID6.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 6), 2).Value
  402. txtMGMT6.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 6), 4).Value
  403. txtCOMMENT6.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 6), 10).Value
  404. txtCITY6.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 6), 5).Value
  405. txtPM6.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 6), 6).Value
  406. txtTYPE6.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 6), 7).value
  407. txtACTIVITY6.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 6), 8).value
  408. txtBN7.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 7), 3).Value
  409. txtFACID7.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 7), 2).Value
  410. txtMGMT7.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 7), 4).Value
  411. txtCOMMENT7.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 7), 10).Value
  412. txtCITY7.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 7), 5).Value
  413. txtPM7.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 7), 6).Value
  414. txtTYPE7.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 7), 7).value
  415. txtACTIVITY7.Text = worksheet.Cells(TotalEntriesTemp - (TotalEntriesTemp - 7), 8).value
  416.  
  417. End Sub
  418.  
  419. Private Sub Filter1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles Filter1.SelectedIndexChanged
  420. Filter2.Visible = True
  421. If Filter1.Text = "STATE" Then
  422. Filter2.Items.Add("AR")
  423. Filter2.Items.Add("AZ")
  424. Filter2.Items.Add("CO")
  425. Filter2.Items.Add("FL")
  426. Filter2.Items.Add("LA")
  427. Filter2.Items.Add("GA")
  428. Filter2.Items.Add("NC")
  429. Filter2.Items.Add("SC")
  430. Filter2.Items.Add("OR")
  431. Filter2.Items.Add("TN")
  432. Filter2.Items.Add("TX")
  433. Filter2.Items.Add("WA")
  434. End If
  435. End Sub
  436.  
  437. Private Sub Filter2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles Filter2.SelectedIndexChanged
  438. btnLoad.Visible = True
  439. End Sub
  440.  
  441. Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
  442. worksheet.Cells(1, 3).Value = txtBN1.Text
  443. worksheet.Cells(1, 2).Value = txtFACID1.Text
  444. worksheet.Cells(1, 4).Value = txtMGMT1.Text
  445. worksheet.Cells(1, 10).value = txtCOMMENT1.Text
  446. workbook.Save()
  447. workbook.Close()
  448. End Sub
  449.  
  450. Private Sub Form1_FormClosed(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles MyBase.FormClosed
  451. APP.Quit()
  452. End Sub
  453.  
  454. Private Sub btnNext_Click(sender As Object, e As EventArgs) Handles btnNext.Click
  455. If TotalEntriesTemp > Entries Then
  456. TotalEntriesTemp = TotalEntriesTemp - (TotalEntriesTemp - Entries)
  457. Else
  458. TotalEntriesTemp = Entries
  459. End If
  460.  
  461. Call DisplayInfo()
  462. End Sub
  463. End Class
Advertisement
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!