Excel programming (C# + VBA) Part 1

Excel programming (C# + VBA)Here is the secario of a full process of solution to use excel to submit information.

Clarify the requirement and create the Excel template (VBA)

Generate an Excel template to fill with source data (C#)

Download the template from web site (C# + ASP.NET)

Fill in the template and verified the data via VBA (VBA)

Upload the template (Web) (ASP.NET)

Import data in Excel Template to database (C#)

1.Setp 1Create the Excel Template based on requirement

a. Clarify the requirement

b. Create hidden template sheet

i. Clarify the data type in each cell, fill in type (user input/list validation or a pop form) and the lock property.

c. Create hidden data source sheet

i. Clarify the source data in each column

ii. Create named range for list validation (If the data is already known)

d. Create Summary, Filling Sheet

i. Set the layout, add validation button and Add new button

e. VBA programming to add list validation or auto-filling

rng.Validation.AddxlValidateList,xlValidAlertStop,xlBetween,”=SpecialOfferType”rng.Validation.IgnoreBlank=Truerng.Validation.InCellDropdown=True

f. VBA programming to select data in pop form

WithfrmChoose.CCodeColumn=”AB”.CNameColumn=”B”.CRow=Target.Row.CCodeSourceColumn=”B”.CNameSourceColumn=”C”.CKeyWords=Me.Cells(iRow,iColumn).Text.Caption=Me.Cells(iRow,iColumn-1)Set.CWorksheet=Application.ActiveSheetSet.CSourceWorksheet=sourceSheet.ShowEndWith

In frmChoose, when the Ok button is clicked:

CWorksheet.UnprotectPasswordn=2If(CSourceWorksheetIsNothing)Thenname=wsDataSource.Range(CNameSourceColumn&n).TextElsename=CSourceWorksheet.Range(CNameSourceColumn&n).TextEndIfWhile(name<>””)Ifname=lstSelected.List(lstSelected.ListIndex)ThenIf(CSourceWorksheetIsNothing)Thencode=wsDataSource.Range(CCodeSourceColumn&n).TextElsecode=CSourceWorksheet.Range(CCodeSourceColumn&n).TextEndIfEndIfn=n+1If(CSourceWorksheetIsNothing)Thenname=wsDataSource.Range(CNameSourceColumn&n).TextElsename=CSourceWorksheet.Range(CNameSourceColumn&n).TextEndIfWendIf(CNameColumn<>””)ThenCWorksheet.Range(CNameColumn&CRow).Value2=lstSelected.List(lstSelected.ListIndex)EndIfIf(CCodeColumn<>””)ThenCWorksheet.Range(CCodeColumn&CRow).Value2=codeEndIfCWorksheet.ProtectPassword

g. VBA programming to validate the data filled in every sheets

IfNotCheckDateType(ws.Cells(i,2).Value)ThenbCheck=FalseMsgBoxws.Cells(i,1).Value&”ÄÚÊäÈëµÄ²»ÊÇÓÐÐ§Ê±¼ä”ws.Activatews.Cells(i,2).SelectExitSubEndIf…..FunctionCheckNumberic(sourceStringAsString)AsBooleanDimbOkAsBooleanbOk=TrueIfsourceString<>””AndNotIsNumeric(sourceString)ThenbOk=FalseEndIfCheckNumberic=bOkEndFunctionFunctionCheckDateType(sourceStringAsString)AsBooleanDimbOkAsBooleanbOk=FalseIfsourceString<>””AndIsDate(sourceString)ThenbOk=TrueEndIfCheckDateType=bOkEndFunction

人生就像是一场旅行，遇到的既有感人的，

相关文章：

你感兴趣的文章：

标签云：