Convert VBA Excel UserForm to VB

vba excel userform to vb

Open the FRM file with a text editor,

VERSION 5.00

Begin {C62A69F0-16DC-11CE-9E98-00AA00574A4F} UserForm1 'This needs to be modified to Begin VB.Form Form1

Caption = "UserForm1"

ClientHeight = 3120

ClientLeft = 45

ClientTop = 435

ClientWidth = 4710

OleObjectBlob = "UserForm1.frx":0000 'Remove this line

StartUpPosition = 1 'Owner Center

End

Attribute VB_Name = "UserForm1"

Attribute VB_GlobalNameSpace = False

Attribute VB_Creatable = False

Attribute VB_PredeclaredId = True

Attribute VB_Exposed = False

'Other objects also make similar modifications

Import data from excel into VB

To call Excel in VB, you need to open the "Reference" item in the "Project" menu of the VB programming environment and select the "MicrosoftExcel 11.0 object library" item in the project. Since your Excel version is different, the version number of this option is also different.

Because EXCEL organizes objects in a hierarchical structure, its object model contains many different object elements.

First layer: Application object, that is, Excel itself;

The second layer: workbooks object set, refers to the Excel workbook file

The third layer: the worksheets object set, which represents a worksheet in Excel;

The fourth layer: Cells and Range objects, pointing to cells in the Excel worksheet.

Dim xlapp As Excel.Application 'Excel object

Dim xlbook As Excel.Workbook 'Workbook

Dim xlsheet As Excel.Worksheet 'Worksheet

Set xlapp = CreateObject("Excel.Application") 'Create EXCEL object

Set xlbook = xlapp.Workbooks.Open("D:\data.xls") 'Open the existing data.xls workbook file

xlapp.Visible = True 'Set the EXCEL object to be visible (or invisible)

Set xlsheet = xlbook.Worksheets(1) 'Set active worksheet''

''~~~The first sheet of the current workbook, you can also change it to "table name" such as "Sheet1"

[Define the array part yourself]

Assign the value in a cell in the table to a variable, such as an element in an array

arr(1,1)=xlsheet.Range(＂B2＂).Value

……

Last close:

xlbook.Close

xlapp.quit

Then set the three objects into nothing

How to introduce excel data into vb two-dimensional array

Use database query method

Read it and put it into a two-dimensional array

Dim cnn2 As New ADODB.Connection

Dim rs2 As New ADODB.Recordset

cnn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=＂ & Text1.Text & ";Extended＂

rs2.Open "Select name, gender From [sheet1$]", cnn2, adOpenKeyset, adLockOptimistic

i=0

s = rs2.ields.Item(0).Value 'Read the name and gender in sheet1

If IsNull(s) Then Exit Do

a(i)=rs2.fields.Item(0).Value 'Name

b(i)=rs2.Fields.Item(1).Value 'Gender

i=i 1

rs2.MoveNext

Loop

Set rs2 = Nothing

Set cnn2 = Nothing

VB experts give me advice: How to import data from spreadsheets into VB

Code to read cell A2: (The data is in the variable R, MICROSOFT EXCEL OBJECT LIBRARY needs to be quoted before the project)

Private excelApp As excel.Application

Private Sub Command2_Click()

Set excelApp = New excel.Application

excelApp.Visible = True

excelApp.Workbooks.Open FileName:=＂C:\student.xls＂

excelApp.Range(＂A2＂).Select

r = excelApp.ActiveCell.FormulaR1C1

Debug.Print r

excelApp.Quit

Set excelApp = Nothing

End Sub

'If you don't want everyone to see the EXCEL interface, just remove excelApp.Visible = True.

The above is the detailed content of Convert VBA Excel UserForm to VB. For more information, please follow other related articles on the PHP Chinese website!