使用visual basci 2017，我需要加载并显示现有的excel 2016工作簿 [英] using visual basci 2017, i need to load and display an existing excel 2016 workbook

问题描述

我最近升级到Microsoft Office 2016专业版。 我的日常文件可以追溯到2003年。我使用了一个基本版本的2003程序，我后来写回来确定是否已创建每日文件或从Carb&如果尚未创建每日文件，则为Insulin.xlsm
模板。  Office 2016女士并不是一个艰难的过渡，但是获得我的Load Form计划让我完全迷失了。旧程序提供错误消息我几乎在每行
时都不知道它们为什么或它们的含义当我将它输入Visual Studo 2017时。 我怀疑我的主要问题是我不再知道重写程序所需的流行语，并且无法找到Visual Basic参考手册。

我输入了以下代码（主要是基于旧程序中的代码）。

选项显式

进口系统

进口系统.IO

模块模块

Public Sub Main（） 

Dim CurrentFile as String =" ; C：\Carb&胰岛素和糖尿病胰岛素" &安培; DateTime.Now.ToString（QUOT; MM-DD-YY）" '当前数据文件的路径

将文件路径调暗为新的目录信息（CurrentFile）  '当前数据文件位置的目录列表的新实例

Dim FileList（）为FileInfo = FilePath.GetFiles（" C：\Carb& Insulin \"） ;）  c：\ carb&中的文件列表胰岛素目录

Dim System.IO.File.Exixts（CurrentFile）= true  '如果为真，则当前数据文件列在C：\Carb&那里的胰岛素目录。

将MyXl作为对象调暗   '变量用于保存当前数据文件或最终保存模板

MyXl = GetObject（FilePath）'每日文件退出所以加载日常工作簿

其他  '文件不存在所以加载Carb& Insulin.xlsm模板

MyXl GetObject（" C：\Carb& Insulin; \\ Carb& Insulin.xlsm"）  'daily carb&胰岛素模板

MyXlWorksheets（" Sheet1"）。cells（2,8）.Value = date.now.tostring（" mm / dd / yy"）" ;每日电子表格的发布日期

MyXlWorksheets（" Sheet1"）。cells（2,9）.Value = date.now.tostring（" dddd"）" ;每日电子表格的发布日期

结束如果

其余代码激活电子表格，单击命令按钮sheet1从sheet2加载含有食物和营养的组合框，并使工作簿可见

 

解决方案

您好
ADeanB，

我尝试在我身边测试你的代码，我发现有这么多代码中的语法，逻辑错误。

您的目标是加载并显示现有的Excel 2016工作簿。

您可以参考以下代码，对您有用。

 Imports System.IO 
 Imports Excel = Microsoft.Office.Interop.Excel 
 
 
 
 Private Sub Button2_Click（sender As Object，e As EventArgs）处理Button2.Click 
 Dim xlApp As Excel.Application 
 Dim xlWorkBook As Excel.Workbook 
 Dim xlWorkSheet As Excel.Worksheet 
 
 xlApp =新Excel.Application 
 xlApp.Visible = True 
 xlWorkBook = xlApp.Workbooks.Open（" C：\ Users\v-padee \Desktop\demo.xlsx" ;）
 xlWorkSheet = xlWorkBook.Worksheets（" Sheet1"）
'显示单元格值B2 
 MsgBox（xlWorkSheet.Cells（2,2）.value）
'编辑具有新值的单元格
 xlWorkSheet.Cells（2,2）=" demo value" 
 xlWorkBook.Save（）
 xlWorkBook.Close（）
 xlApp.Quit（）
 
 releaseObject（xlApp）
 releaseObject（xlWorkBook）
 releaseObject（xlWorkSheet）
 
 End Sub 
 
 
 
 Private Sub releaseObject（ByVal obj As Object）
尝试
系统.Runtime.InteropServices.Marshal.ReleaseComObject（obj）
 obj = Nothing 
 Catch ex As Exception 
 obj = Nothing 
最后
 GC.Collect（）
结束尝试
结束子

                                  

您可以根据自己的要求修改代码。

问候

Deepak

I have recently upgraded to Microsoft Office 2016 Pro.  I have daily files dating back to 2003. i have used a visual basic version 2003 program I wrote back then to determine if a daily file has been created or create one from a Carb & Insulin.xlsm template if the daily file has not been created.  Ms Office 2016 was not a difficult transition but getting my Load Form program has me totally lost. The old program gives error messages I have no idea of why or the meaning of them on almost every line when i typed it into Visual Studo 2017.  I suspect my major problem is that i no longer know the buzz words needed to rewrite the program and have been unable to locate a Visual Basic Reference Manual.

I have entered the following code (mostly based on the code in the old program).

Option Explicit on

Imports system

imports System.IO

Module Modul1

Public Sub Main() 

Dim CurrentFile as String = "C:\Carb & Insulin\Carb & insulin" & DateTime.Now.ToString("mm-dd-yy)" 'Path to current data file

Dim FilePath as New DirectoryInfo(CurrentFile)  'New instance of the directory listing for the location of the Current data file

Dim FileList() as FileInfo = FilePath.GetFiles("C:\Carb & Insulin\")  'list of files in the c:\carb & insulin directory

Dim System.IO.File.Exixts(CurrentFile) = true   'if true the current data file listed in the C:\Carb & insulin directory there.

Dim MyXl as object    'variable to hold the current data file or eventually the template if necessary

MyXl = GetObject(FilePath) 'the daily file exits so load the daily workbook

Else   'The file doesn't exist so load the Carb & Insulin.xlsm template

MyXl GetObject("C:\Carb & Insulin\Carb & Insulin.xlsm")   'daily carb & insulin template

MyXlWorksheets("Sheet1").cells(2,8).Value = date.now.tostring("mm/dd/yy") "post date for daily spreadsheet

MyXlWorksheets("Sheet1").cells(2,9).Value = date.now.tostring("dddd") "post day of ween for daily spreadsheet

End If

The rest of the code activates the spreadsheet, clicks a command button on sheet1 to load a combo box with food and nutrition from sheet2, and makes the workbook visible

 

解决方案

Hi ADeanB,

I try to test your code on my side and I find that there is so many syntax, logical error in your code.

your goal is to load and display an existing excel 2016 workbook.

you can refer code below will work for you.

Imports System.IO
Imports Excel = Microsoft.Office.Interop.Excel



 Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet

        xlApp = New Excel.Application
        xlApp.Visible = True
        xlWorkBook = xlApp.Workbooks.Open("C:\Users\v-padee\Desktop\demo.xlsx")
        xlWorkSheet = xlWorkBook.Worksheets("Sheet1")
        'display the cells value B2
        MsgBox(xlWorkSheet.Cells(2, 2).value)
        'edit the cell with new value
        xlWorkSheet.Cells(2, 2) = "demo value"
        xlWorkBook.Save()
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

    End Sub



    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

                                 

further you can modify the code as per your requirement.

Regards

Deepak

这篇关于使用visual basci 2017，我需要加载并显示现有的excel 2016工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！