Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Opening an Excel Spreadsheet

I have an application where I want to import an excel spreadsheet into a Pervasive Table. I am getting the Spreadsheet from the client so I do not know whether the Sheet Name will be constant or not so I can not hard code it. Is there anyway to get the sheet name from the file I just opened so I can copy the data into the Pervasive table or is there another way for me to import the data.
Thanks,
Mike

Comments

  • jr_runnfooljr_runnfool Member Posts: 1
    : I have an application where I want to import an excel spreadsheet into a Pervasive Table. I am getting the Spreadsheet from the client so I do not know whether the Sheet Name will be constant or not so I can not hard code it. Is there anyway to get the sheet name from the file I just opened so I can copy the data into the Pervasive table or is there another way for me to import the data.
    : Thanks,
    : Mike
    :

    If you open Excel with automation you can get the name of sheets(1), then use this in your query. No way I know of to just do it with ODBC. Hope that helps.
    JR
  • webmaster220webmaster220 Member Posts: 3
    : I have an application where I want to import an excel spreadsheet into a Pervasive Table. I am getting the Spreadsheet from the client so I do not know whether the Sheet Name will be constant or not so I can not hard code it. Is there anyway to get the sheet name from the file I just opened so I can copy the data into the Pervasive table or is there another way for me to import the data.
    : Thanks,
    : Mike
    :
    I would link the excel spreadsheet to an access database or sql server database, then I would drag the sheet within the linked database onto the visual basic form to establish a data connection to the vb .net in the code. There are harder ways to create the connection thru typing in all the code, but if dragging it from the server explorer window onto the form works and the code is built and editable, why not do it?
    The following resource discusses establishing a data connection in vb.net:
    Sams Teach Yourself Microsoft Visual Basic .NET 2003 in 24 Hours Complete Starter Kit
    http://www.samspublishing.com/bookstore/product.asp?isbn=0672325373&rl=1

    Hour 21. Working with a Database.
    Introducing ADO.NET.
    Manipulating Data.
    Using the Data Form Wizard.
    _______________________________________________________
    The enclosed code was just generated on the fly by me dragging some sql server stuff onto my vb .net form. I found it inside the
    #Region " Windows Form Designer generated code "
    As you can see, dragging generates all the code you will need to establish the connection, and from there you bind the data to the various textboxes and labels of the vb.net form using properties maintenance. I am sorry that I have never heard of "Pervasive Tables" so my answer to your inquiry is based on my having found your post on the "advanced vb.net programming" messageboard.
    '
    'SqlSelectCommand1
    '
    Me.SqlSelectCommand1.CommandText = "SELECT Date FROM [5468_892005_143816]"
    Me.SqlSelectCommand1.Connection = Me.SqlConnection1
    '
    'SqlInsertCommand1
    '
    Me.SqlInsertCommand1.CommandText = "INSERT INTO [5468_892005_143816] (Date) VALUES (@Date); SELECT Date FROM [5468_89" & _
    "2005_143816]"
    Me.SqlInsertCommand1.Connection = Me.SqlConnection1
    Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Date", System.Data.SqlDbType.NVarChar, 255, "Date"))
    '
    'SqlConnection1
    '
    Me.SqlConnection1.ConnectionString = "workstation id=RLIVERMORE;packet size=4096;integrated security=SSPI;data source=R" & _
    "LIVERMORE;persist security info=True;initial catalog=(database table name goes here)"
    '
    'SqlDataAdapter1
    '
    Me.SqlDataAdapter1.InsertCommand = Me.SqlInsertCommand1
    Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
    Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "5468_892005_143816", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("Date", "Date")})})
    '
    'SqlSelectCommand2
    '
    Me.SqlSelectCommand2.CommandText = "SELECT accesskey, recordnum, LibraryCardData, LibraryCallID FROM dvdsheet"
    Me.SqlSelectCommand2.Connection = Me.SqlConnection2
    '
    'SqlInsertCommand2
    '
    Me.SqlInsertCommand2.CommandText = "INSERT INTO dvdsheet(accesskey, recordnum, LibraryCardData, LibraryCallID) VALUES" & _
    " (@accesskey, @recordnum, @LibraryCardData, @LibraryCallID); SELECT accesskey, r" & _
    "ecordnum, LibraryCardData, LibraryCallID FROM dvdsheet"
    Me.SqlInsertCommand2.Connection = Me.SqlConnection2
    Me.SqlInsertCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@accesskey", System.Data.SqlDbType.Float, 8, "accesskey"))
    Me.SqlInsertCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@recordnum", System.Data.SqlDbType.NVarChar, 255, "recordnum"))
    Me.SqlInsertCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@LibraryCardData", System.Data.SqlDbType.NVarChar, 1073741823, "LibraryCardData"))
    Me.SqlInsertCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@LibraryCallID", System.Data.SqlDbType.NVarChar, 255, "LibraryCallID"))
    '
    'SqlConnection2
    '
    Me.SqlConnection2.ConnectionString = "workstation id=RLIVERMORE;packet size=4096;integrated security=SSPI;data source=R" & _
    "LIVERMORE;persist security info=True;initial catalog=master"
    '
    'SqlDataAdapter2
    '
    Me.SqlDataAdapter2.InsertCommand = Me.SqlInsertCommand2
    Me.SqlDataAdapter2.SelectCommand = Me.SqlSelectCommand2
    Me.SqlDataAdapter2.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "dvdsheet", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("accesskey", "accesskey"), New System.Data.Common.DataColumnMapping("recordnum", "recordnum"), New System.Data.Common.DataColumnMapping("LibraryCardData", "LibraryCardData"), New System.Data.Common.DataColumnMapping("LibraryCallID", "LibraryCallID")})})
    '
  • lionblionb Member Posts: 1,688 ✭✭
    : I have an application where I want to import an excel spreadsheet into a Pervasive Table. I am getting the Spreadsheet from the client so I do not know whether the Sheet Name will be constant or not so I can not hard code it. Is there anyway to get the sheet name from the file I just opened so I can copy the data into the Pervasive table or is there another way for me to import the data.
    : Thanks,
    : Mike
    :
    Try this link http://support.microsoft.com/default.aspx?scid=kb;[LN];Q316934
    In case link won't work cut and paste this link to the address bar
  • webmaster220webmaster220 Member Posts: 3
    : : I have an application where I want to import an excel spreadsheet into a Pervasive Table. I am getting the Spreadsheet from the client so I do not know whether the Sheet Name will be constant or not so I can not hard code it. Is there anyway to get the sheet name from the file I just opened so I can copy the data into the Pervasive table or is there another way for me to import the data.
    : : Thanks,
    : : Mike
    : :
    : Try this link http://support.microsoft.com/default.aspx?scid=kb;[LN];Q316934
    : In case link won't work cut and paste this link to the address bar
    :
    The following is a follow up comment from Webmaster220:
    I tried the link and it didn't work until I used the cut and paste into the address bar method.
    try this instead:
    http://support.microsoft.com/kb/316934
    _______________________________________________________________________
    on the very bottom of that document is the following block:

    REFERENCES
    For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
    http://support.microsoft.com/kb/316756/ PRB: Error Occurs When You Use ADO.NET OLEDbDataAdapter to Modify Excel Workbook
    _______________________________________________________________________
    http://support.microsoft.com/kb/257819/EN-US/ How To Use ADO with Excel Data from Visual Basic or VBA
    _______________________________________________________________________
    http://support.microsoft.com/kb/306022/ How To Transfer Data to an Excel Workbook by Using Visual Basic .NET
    _______________________________________________________________________
    http://support.microsoft.com/kb/306023/ How To Transfer Data to an Excel Workbook Using Visual C# .NET
    _______________________________________________________________________
    http://support.microsoft.com/kb/311731/ How To Query and Display Excel Data by Using ASP.NET, ADO.NET and Visual Basic .NET
    _______________________________________________________________________
    http://support.microsoft.com/kb/306572/ How To Query and Display Excel Data by Using ASP.NET, ADO.NET, and Visual C# .NET
    _______________________________________________________________________
    http://support.microsoft.com/kb/278973/ SAMPLE: ExcelADO Demonstrates How to Use ADO to Read and Write Data in Excel Workbooks
    _______________________________________________________________________








Sign In or Register to comment.