如何在VBA表单上显示部分Excel [英] How To Display Part of Excel on VBA Form

问题描述

我有一个.csv格式的文件，从A-S列，它有一些记录像一个表。我的完整程序将插入/删除/删除/添加一些行，列和编辑单元格值等。我设法编写了我需要的所有操作，现在我试图将它与gui集成。

我想要的是将单元格从Ax1显示到VBA用户窗体上记录的最后一列。我可以这样做吗？

* ps：再次，我的文件格式为.csv，我正在使用Excel 2007

解决方案

您可以使用多列列表框显示数据。

LOGIC

1. 在临时表中导入文本（Csv）


2. 显示多列Listbox中的数据


3. 删除Userform卸载事件中的临时表

导入文本（Csv ）文件

  Private Sub CommandButton1_Click（）
 Dim wb As Workbook，wbTemp As工作簿
 Dim wsTemp As Worksheet 
 
设置wb = ThisWorkbook 
设置wbTemp = Workbooks.Open（C：\MyCsv.Csv）
 
 wbTemp.Sheets（1）.Copy After：= wb.Sheets（wb.Sheets.Count）
 
设置wsTemp = ActiveSheet 
 
 wbTemp.Close SaveChanges：= False 
 End Sub 
  

现在你可以在多列列表框中的数据。

显示多列列表框中的数据

我正在举一个3列的例子，直到拖曳20.更改适用

  Private Sub CommandButton1_Click（）
 Dim wb As Workbook，wbTemp As Workbook 
 Dim wsTemp As Worksheet 
 
设置wb = ThisWorkbook 
设置wbTemp = Workbooks.Open（C：\MyCsv.Csv ）
 
 wbTemp.Sheets（1）.Copy After：= wb.Sheets（wb.Sheets.Count）
 
 
设置wsTemp = ActiveSheet 
 
 wbTemp.Close SaveChanges：= False 
 
 With ListBox1 
 .ColumnCount = 3 
 .ColumnWidths =50; 50; 50
 .RowSource = wsTemp.Range（A1：C20）。地址
结束
结束子
  

SCREENSHOT

删除t在Userform卸载事件中的emp表单

要删除临时表，请在该表上声明 wsTemp 代码的顶部，以便您可以在 UserForm_QueryClose 事件中访问该代码。看到这个完整的例子

  Option Explicit 
 
 Dim wsTemp As Worksheet 
 
 Private Sub CommandButton1_Click（）
 Dim wb As Workbook，wbTemp As Workbook 
 
 
设置wb = ThisWorkbook 
设置wbTemp = Workbooks.Open（C：\MyCsv .Csv）
 
 wbTemp.Sheets（1）.Copy After：= wb.Sheets（wb.Sheets.Count）
 
 
设置wsTemp = ActiveSheet 
 
 wbTemp.Close SaveChanges：= False 
 
 With ListBox1 
 .ColumnCount = 3 
 .ColumnWidths =50; 50; 50
 .RowSource = wsTemp.Range（A1：C20）。地址
 End with 
 End Sub 
 
 Private Sub UserForm_QueryClose（Cancel As Integer，CloseMode As Integer）
 Application.DisplayAlerts = False 
 wsTemp.Delete 
 Application.DisplayAlerts = True 
 End Sub 
  

HTH

I have a file on .csv format and from A-S columns, it has some records like a table. My complete program will insert/remove/delete/add some rows, columns and editing cell values etc. I managed to code all the operations that i need, now i'm trying to integrate it with a gui.

What I want is to display cells from Ax1 to the last column that has record on VBA user form. How can i do that?

*ps: again, my file's format is .csv and I am using Excel 2007

解决方案

You can use a multi column Listbox to show the data.

LOGIC

1. Import the text (Csv) file in the temp sheet
2. Show that data in the multicolumn Listbox
3. Delete the temp sheet in the Userform unload event

Import the text (Csv) file in the temp sheet

Private Sub CommandButton1_Click()
    Dim wb As Workbook, wbTemp As Workbook
    Dim wsTemp As Worksheet

    Set wb = ThisWorkbook
    Set wbTemp = Workbooks.Open("C:\MyCsv.Csv")

    wbTemp.Sheets(1).Copy After:=wb.Sheets(wb.Sheets.Count)

    Set wsTemp = ActiveSheet

    wbTemp.Close SaveChanges:=False
End Sub

And now you can display that data in a multicolumn listbox.

Show that data in the multicolumn Listbox

I am taking an example of 3 Columns and up till tow 20. Change as applicable

Private Sub CommandButton1_Click()
    Dim wb As Workbook, wbTemp As Workbook
    Dim wsTemp As Worksheet

    Set wb = ThisWorkbook
    Set wbTemp = Workbooks.Open("C:\MyCsv.Csv")

    wbTemp.Sheets(1).Copy After:=wb.Sheets(wb.Sheets.Count)


    Set wsTemp = ActiveSheet

    wbTemp.Close SaveChanges:=False

    With ListBox1
        .ColumnCount = 3
        .ColumnWidths = "50;50;50"
        .RowSource = wsTemp.Range("A1:C20").Address
    End With
End Sub

SCREENSHOT

Delete the temp sheet in the Userform unload event

To Delete the temp sheet, declare the wsTemp on the top of the code so that you can access that in the UserForm_QueryClose event. See this complete example

Option Explicit

Dim wsTemp As Worksheet

Private Sub CommandButton1_Click()
    Dim wb As Workbook, wbTemp As Workbook


    Set wb = ThisWorkbook
    Set wbTemp = Workbooks.Open("C:\MyCsv.Csv")

    wbTemp.Sheets(1).Copy After:=wb.Sheets(wb.Sheets.Count)


    Set wsTemp = ActiveSheet

    wbTemp.Close SaveChanges:=False

    With ListBox1
        .ColumnCount = 3
        .ColumnWidths = "50;50;50"
        .RowSource = wsTemp.Range("A1:C20").Address
    End With
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Application.DisplayAlerts = False
    wsTemp.Delete
    Application.DisplayAlerts = True
End Sub

HTH

这篇关于如何在VBA表单上显示部分Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！