Need Help writing to Excel

Yahoo Archive
1
I have the opposite problem. I'm trying to transfer order lines from an excel spreadsheet into the Transfer Order screen.
I can get it as far as creating the excel object, creating a dataset of the spreadsheet contents, but how do I set the datasource of the transfer order lines grid in the summary screen to use my dataset ? Is that even possible ?

Tx
B

--- In vantage@yahoogroups.com, "dgodfrey_amc" <dgodfrey_amc@...> wrote:
>
> I am trying to read and write to an excel file through the Customization editor. I am using an OleDbConnection. I am able to read from the excel file this way but I can not write to a file. Does anyone have any examples of how to write back a dataset or even just a row using OleDbConnection OR
>
> does anyone have a better way to do this I would really appreciate it.
>
2
I am trying to read and write to an excel file through the Customization editor. I am using an OleDbConnection. I am able to read from the excel file this way but I can not write to a file. Does anyone have any examples of how to write back a dataset or even just a row using OleDbConnection OR

does anyone have a better way to do this I would really appreciate it.
3
I have used your approach only to read information from a very structured Excel Workbook.

To read AND write to Excel I use the Microsoft.Office.Interop.Excel assembly. This exposes all of the capabilities of Excel and lets me manipulate the spreadsheet anyway I need to.

You may need to copy Microsoft.Office.Interop.Excel.dll into the Epicor /client directory. After that use the add custom assembly and the appropriate using/imports Microsoft.Office.Interop.Excel statement

Since I don't do this that often I usually Google on how to call the various methods of Microsoft.Office.Interop.Excel.

Jim Kinneman
Senior Consultant
Encompass Solutions

--- In vantage@yahoogroups.com, "dgodfrey_amc" <dgodfrey_amc@...> wrote:
>
> I am trying to read and write to an excel file through the Customization editor. I am using an OleDbConnection. I am able to read from the excel file this way but I can not write to a file. Does anyone have any examples of how to write back a dataset or even just a row using OleDbConnection OR
>
> does anyone have a better way to do this I would really appreciate it.
>
4
I will save you the google step. This will export your materials out of a quote.

Tom Osen


Imports Microsoft.Office.Interop.Excel

Private Sub CostedBOM()
'// ** Place Event Handling Code Here **


Dim edvQuoteMtlView As EpiDataView = CType(oTrans.EpiDataViews("JobMtl"), EpiDataView)
Dim edvVendPartView AS EpiDataView = CType(oTrans.EpiDataViews("VendPart"), EpiDataView)
Dim edvVendPBrkView AS EpiDataView = CType(oTrans.EpiDataViews("VendPBrk"), EpiDataView)
Dim edvQuoteHead As EpiDataView = CType(oTrans.EpiDataViews("QuoteHed"), EpiDataView)

Dim inQuoteNum, inQuoteLine, inNetWeight As Integer
Dim workbookLocation, strIncoTerms, strVendor As String

workbookLocation = "\\fileserver\doclinks\quote\" + CStr(edvQuoteMtlView.dataView(edvQuoteMtlView.Row)("QuoteNum")) + "_QuoteWorkbook.xls"

' check to see if the file is not open and exists
If System.IO.File.Exists(workbookLocation) = True _
And Not IsFileOpen(workbookLocation) Then

Dim oExcel As Microsoft.Office.Interop.Excel.Application
oExcel = CreateObject("Excel.Application")

oExcel.Application.Visible = False

Dim oWorkbooks As Object = oExcel.Application.Workbooks.Open(WorkbookLocation)

Dim workbookRow As Integer = 15
Dim strRange, quoteTeam, strClass As String
Dim inLeadTime, inMOQ, inColumn As Integer
Dim inOnHandQty, inAveCost As Decimal
Dim decPriceBreak(8, 1) As Decimal
Dim blSPL, blWholeUnit As Boolean

oWorkbooks = oWorkbooks.Worksheets("Costed BOM")
oExcel.Application.Sheets("Costed BOM").Select

' Set the part weight to the FG part
quoteTeam = edvQuoteHead.dataView(0)("Character08")
PartInfo(oExcel.Application.Cells(1, 5).Value, inLeadTime, inOnHandQty, inNetWeight, blWholeUnit, quoteTeam)
inLeadTime = 0
oExcel.Application.Cells(4, 5).Value = inNetWeight
oExcel.Application.Cells(8, 14).Value = System.DateTime.Now

' msgbox("Lead time at Start = " &amp; CStr(inLeadtime))

For i as integer = 0 to edvQuoteMtlView.dataView.Count - 1
' If edvQuoteMtlView.dataView(edvQuoteMtlView.Row)("QuoteNum") = inQuoteNum _
' And edvQuoteMtlView.dataView(edvQuoteMtlView.Row)("QuoteLine") = inQuoteLine Then

' copy and paste the row so we don't care how many parts this BOM has. We can go to the limit of Excel rows
oExcel.Application.Range("15:15").Select
oExcel.Application.Selection.Copy
workbookRow += 1
strRange = CStr(workbookRow) + ":" + CStr(workbookRow)
oExcel.Application.Range(strRange).Select
oExcel.Application.Selection.Insert(-4121)

' Initalize the variables for this part
inLeadTime = 0
inMOQ = 0
inOnHandQty = 0
inAveCost = 0

PartCost(edvQuoteMtlView.dataView(i)("PartNum"), inAveCost)

'Find the supplier price list information
If edvQuoteMtlView.dataView(i)("BuyIt") _
And edvQuoteMtlView.dataView(i)("VendorNum") <> 0 Then
SupplierPriceList(edvQuoteMtlView.dataView(i)("VendorNum"), edvQuoteMtlView.dataView(i)("PartNum"), inLeadTime, inMOQ, blSPL)

For idxVendPart As Integer = 0 to edvVendPartView.dataView.Count - 1
' inLeadTime = edvVendPartView.dataView(idxVendPart)("LeadTime")
strIncoTerms = edvVendPartView.dataView(idxVendPart)("ShortChar01")
strVendor = edvVendPartView.dataView(idxVendPart)("VendorName")

' For inBrk As Integer = 0 to 8
' decPriceBreak(inBrk, 1) = edvVendPartView.dataView(inBrk)("BaseUnitPrice")
' Next
Next

End If 'edvQuoteMtlView.dataView(i)("VendorNum") &lt;&gt; 0
' msgbox( edvQuoteMtlView.dataView(i)("PartNum") &amp; " Lead time after SPL = " &amp; CStr(inLeadtime) &amp; " " &amp; quoteTeam)
PartInfo(edvQuoteMtlView.dataView(i)("PartNum"), inLeadTime, inOnHandQty, inNetWeight, blWholeUnit, quoteTeam)
' msgbox("Lead time after PartInfo = " &amp; CStr(inLeadtime) &amp; " " &amp; quoteTeam)
If inLeadTime = 0 Then
inLeadTime = edvQuoteMtlView.dataView(i)("LeadTime")
' msgbox("Lead time after screen = " &amp; CStr(inLeadtime) &amp; " " &amp; quoteTeam)
End If

oExcel.Application.Cells(workbookRow, 1).Value = Math.Ceiling((inLeadTime / 7)) ' RoundUp((inLeadTime / 7)) 'A
' oExcel.Application.Cells(workbookRow, 2).Value = quoteTeam 'B
oExcel.Application.Cells(workbookRow, 3).Value = edvQuoteMtlView.dataView(i)("PartNum") 'C
oExcel.Application.Cells(workbookRow, 4).Value = edvQuoteMtlView.dataView(i)("Description") 'D
oExcel.Application.Cells(workbookRow, 5).Value = edvQuoteMtlView.dataView(i)("QtyPer") 'E
oExcel.Application.Cells(workbookRow, 6).Value = edvQuoteMtlView.dataView(i)("IUM") 'F
If blWholeUnit Then
oExcel.Application.Cells(workbookRow, 7).Value = "Yes" 'G
Else
oExcel.Application.Cells(workbookRow, 7).Value = "No"
End If
oExcel.Application.Cells(workbookRow, 8).Value = inMOQ 'H
oExcel.Application.Cells(workbookRow, 9).Value = edvQuoteMtlView.dataView(i)("EstScrap") / 100 'I
' oExcel.Application.Cells(workbookRow, 10).Value = edvQuoteMtlView.dataView(i)("Number13") '
oExcel.Application.Cells(workbookRow, 12).Value = inAveCost 'L
' oExcel.Application.Cells(workbookRow, 11).Value = edvQuoteMtlView.dataView(i)("Number15") '
oExcel.Application.Cells(workbookRow, 14).Value = inOnHandQty 'N
' If SPL then use the price breaks for the data else use the average cost

oExcel.Application.Cells(workbookRow, 18).Value = edvQuoteMtlView.dataView(i)("Number11") 'R
oExcel.Application.Cells(workbookRow, 24).Value = edvQuoteMtlView.dataView(i)("Number12") 'X
oExcel.Application.Cells(workbookRow, 30).Value = edvQuoteMtlView.dataView(i)("Number13") 'AD
oExcel.Application.Cells(workbookRow, 36).Value = edvQuoteMtlView.dataView(i)("Number14") 'AJ
oExcel.Application.Cells(workbookRow, 42).Value = edvQuoteMtlView.dataView(i)("Number15") 'AP
oExcel.Application.Cells(workbookRow, 48).Value = edvQuoteMtlView.dataView(i)("Number16") 'AV
oExcel.Application.Cells(workbookRow, 54).Value = edvQuoteMtlView.dataView(i)("Number17") 'BB
oExcel.Application.Cells(workbookRow, 60).Value = edvQuoteMtlView.dataView(i)("Number18") 'BH
oExcel.Application.Cells(workbookRow, 66).Value = edvQuoteMtlView.dataView(i)("Number19") 'BN
oExcel.Application.Cells(workbookRow, 71).Value = strVendor 'BS
oExcel.Application.Cells(workbookRow, 72).Value = strIncoTerms 'BT

Next

oExcel.Application.ActiveWorkbook.Save
oExcel.Application.Workbooks.Close()
RCO(oWorkbooks)


oExcel.Application.Quit()
RCO(oExcel)

oWorkbooks = Nothing
oExcel = Nothing

GC.Collect()

MsgBox("Costed BOM has finished downloading into the quote workbook." , MsgBoxStyle.OkOnly , "Costed BOM" )

Else
MsgBox("something is wrong with the file " & vbcrlf & "File location is " & workbookLocation _
& vbcrlf & "Is the file open " & CStr(IsFileOpen(workbookLocation)), MsgBoxStyle.OkOnly or MsgBoxStyle.Critical, "Quote Workbook Information" )
End If


edvQuoteMtlView = Nothing
edvVendPartView = Nothing
edvVendPBrkView = Nothing
edvQuoteHead = Nothing

End Sub

Public Sub RCO(obj As Object)
' This releases all the com objects that where created
Try
Dim intRel As Integer = 0
Do
intRel = System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)

Loop While intRel > 0

Catch ex As Exception
msgbox("Error releasing object" & ex.ToString,vbOKOnly,"Close Excel Error")
obj = Nothing
End Try

End Sub
--- In vantage@yahoogroups.com, "jckinneman" <jckinneman@...> wrote:
>
> I have used your approach only to read information from a very structured Excel Workbook.
>
> To read AND write to Excel I use the Microsoft.Office.Interop.Excel assembly. This exposes all of the capabilities of Excel and lets me manipulate the spreadsheet anyway I need to.
>
> You may need to copy Microsoft.Office.Interop.Excel.dll into the Epicor /client directory. After that use the add custom assembly and the appropriate using/imports Microsoft.Office.Interop.Excel statement
>
> Since I don't do this that often I usually Google on how to call the various methods of Microsoft.Office.Interop.Excel.
>
> Jim Kinneman
> Senior Consultant
> Encompass Solutions
>
> --- In vantage@yahoogroups.com, "dgodfrey_amc" <dgodfrey_amc@> wrote:
> >
> > I am trying to read and write to an excel file through the Customization editor. I am using an OleDbConnection. I am able to read from the excel file this way but I can not write to a file. Does anyone have any examples of how to write back a dataset or even just a row using OleDbConnection OR
> >
> > does anyone have a better way to do this I would really appreciate it.
> >
>
5
I can't seem to find the appropriate dll. I did a search for just "Interop" and received nothing.

--- In vantage@yahoogroups.com, "jckinneman" <jckinneman@...> wrote:
>
> I have used your approach only to read information from a very structured Excel Workbook.
>
> To read AND write to Excel I use the Microsoft.Office.Interop.Excel assembly. This exposes all of the capabilities of Excel and lets me manipulate the spreadsheet anyway I need to.
>
> You may need to copy Microsoft.Office.Interop.Excel.dll into the Epicor /client directory. After that use the add custom assembly and the appropriate using/imports Microsoft.Office.Interop.Excel statement
>
> Since I don't do this that often I usually Google on how to call the various methods of Microsoft.Office.Interop.Excel.
>
> Jim Kinneman
> Senior Consultant
> Encompass Solutions
>
> --- In vantage@yahoogroups.com, "dgodfrey_amc" <dgodfrey_amc@> wrote:
> >
> > I am trying to read and write to an excel file through the Customization editor. I am using an OleDbConnection. I am able to read from the excel file this way but I can not write to a file. Does anyone have any examples of how to write back a dataset or even just a row using OleDbConnection OR
> >
> > does anyone have a better way to do this I would really appreciate it.
> >
>
6
The Microsoft Office dll's live in the Global Assembly Cache (GAC), which is not accessible through normal windows explorer methods. You can use command prompt to get it at though, and copy the file elsewhere.

If you open command prompt, make sure your in the C: directory, and cd to Windows\assembly\GAC\Microsoft.Office.Interop.Excel, and then dir the directory, you should see a few folders. Depending on the version of Excel you have, the folder names will be different. Ours is 2007, so the folder is 12.0.0.0__71e9bce111e9429c. If you then cd there, and dir that directory, you will see the Microsoft.Office.Interop.Excell.dll file. You can then copy it wherever you like.


--- In vantage@yahoogroups.com, "dgodfrey_amc" <dgodfrey_amc@...> wrote:
>
> I can't seem to find the appropriate dll. I did a search for just "Interop" and received nothing.
>
> --- In vantage@yahoogroups.com, "jckinneman" <jckinneman@> wrote:
> >
> > I have used your approach only to read information from a very structured Excel Workbook.
> >
> > To read AND write to Excel I use the Microsoft.Office.Interop.Excel assembly. This exposes all of the capabilities of Excel and lets me manipulate the spreadsheet anyway I need to.
> >
> > You may need to copy Microsoft.Office.Interop.Excel.dll into the Epicor /client directory. After that use the add custom assembly and the appropriate using/imports Microsoft.Office.Interop.Excel statement
> >
> > Since I don't do this that often I usually Google on how to call the various methods of Microsoft.Office.Interop.Excel.
> >
> > Jim Kinneman
> > Senior Consultant
> > Encompass Solutions
> >
> > --- In vantage@yahoogroups.com, "dgodfrey_amc" <dgodfrey_amc@> wrote:
> > >
> > > I am trying to read and write to an excel file through the Customization editor. I am using an OleDbConnection. I am able to read from the excel file this way but I can not write to a file. Does anyone have any examples of how to write back a dataset or even just a row using OleDbConnection OR
> > >
> > > does anyone have a better way to do this I would really appreciate it.
> > >
> >
>
7
OK, am really having a difficult time accessing an Excel file. I have been able to get it to work on my machine and thought everything was great until I deployed it. A user tried the customization and got a

-- "Format of the initialization string does not conform to specification starting at index ..." error.

my code was

'-- MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source='" & myPath & " '; " & "Extended Properties=Excel 8.0;")
'-- MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [sheet1$] where InActive= False", MyConnection)
'-- MyCommand.Fill(dsECODataSet, "ECORecords")

I starting looking in the history threads here and found someone talking about using this string

-- Imports Microsoft.Office.Interop.Excel

So I tried and tried using the Excel.Application and had no success compiling
Error: BC30002 - line 1572 (2186) - Type 'Excel.Application' is not defined.
Error: BC30002 - line 1573 (2187) - Type 'Excel.Workbook' is not defined.
Error: BC30002 - line 1574 (2188) - Type 'Excel.Worksheet' is not defined.
Error: BC30002 - line 1575 (2189) - Type 'Excel.Range' is not defined.
Error: BC30002 - line 1581 (2195) - Type 'Excel.ApplicationClass' is not defined.
Error: BC30002 - line 1589 (2203) - Type 'Excel.Range' is not defined.

here was my test subroutine:
Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim xcFileDialog As New OpenFileDialog()

xcFileDialog.Filter = "Excel Spreadsheet Files|*.xls"
xcFileDialog.Title = "Select excel spreadsheet file!"
xcFileDialog.CheckFileExists() = True

If xcFileDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
If Not String.IsNullOrEmpty(xcFileDialog.FileName) Then
TextBox1.Text = xcFileDialog.FileName
End If
End If
If Not String.IsNullOrEmpty(TextBox1.Text) Then
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim range As Excel.Range
Dim rCnt As Integer
Dim cCnt As Integer
Dim Obj As Object
Dim msg As String

xlApp = New Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Open(TextBox1.Text)
xlWorkSheet = xlWorkBook.Worksheets("sheet1")

range = xlWorkSheet.UsedRange

For rCnt = 1 To range.Rows.Count
For cCnt = 1 To range.Columns.Count
Obj = CType(range.Cells(rCnt, cCnt), Excel.Range)
msg = msg & Obj.value & "; "
Next
msg = msg & Chr(13) & Chr(10)
Next
MsgBox(msg)

xlWorkBook.Close()
xlApp.Quit()

releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)
End If
MsgBox("Test Complete")

End Sub

I know this is long but I am wanting to give as much info so hopefully someone can help explain where I am going wrong.

Dan
8
Based on how your code is setup, when you import the Excel namespace in the line Imports Microsoft.Office.Interop.Excel, and then use Excel.Application to declare some of your variables using Excel.Application, you are in essence trying to use it twice. When I use Excel, I only use Imports Microsoft.Office.Interop. Then, your variable declarations should work just fine. You could also keep your current Imports, and change all declarations to remove the Excel., but it's nice to do it the other way in case you are also using Word, Outlook, etc.


--- In vantage@yahoogroups.com, "dgodfrey_amc" <dgodfrey_amc@...> wrote:
>
> OK, am really having a difficult time accessing an Excel file. I have been able to get it to work on my machine and thought everything was great until I deployed it. A user tried the customization and got a
>
> -- "Format of the initialization string does not conform to specification starting at index ..." error.
>
> my code was
>
> '-- MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source='" & myPath & " '; " & "Extended Properties=Excel 8.0;")
> '-- MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [sheet1$] where InActive= False", MyConnection)
> '-- MyCommand.Fill(dsECODataSet, "ECORecords")
>
> I starting looking in the history threads here and found someone talking about using this string
>
> -- Imports Microsoft.Office.Interop.Excel
>
> So I tried and tried using the Excel.Application and had no success compiling
> Error: BC30002 - line 1572 (2186) - Type 'Excel.Application' is not defined.
> Error: BC30002 - line 1573 (2187) - Type 'Excel.Workbook' is not defined.
> Error: BC30002 - line 1574 (2188) - Type 'Excel.Worksheet' is not defined.
> Error: BC30002 - line 1575 (2189) - Type 'Excel.Range' is not defined.
> Error: BC30002 - line 1581 (2195) - Type 'Excel.ApplicationClass' is not defined.
> Error: BC30002 - line 1589 (2203) - Type 'Excel.Range' is not defined.
>
> here was my test subroutine:
> Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
> Dim xcFileDialog As New OpenFileDialog()
>
> xcFileDialog.Filter = "Excel Spreadsheet Files|*.xls"
> xcFileDialog.Title = "Select excel spreadsheet file!"
> xcFileDialog.CheckFileExists() = True
>
> If xcFileDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
> If Not String.IsNullOrEmpty(xcFileDialog.FileName) Then
> TextBox1.Text = xcFileDialog.FileName
> End If
> End If
> If Not String.IsNullOrEmpty(TextBox1.Text) Then
> Dim xlApp As Excel.Application
> Dim xlWorkBook As Excel.Workbook
> Dim xlWorkSheet As Excel.Worksheet
> Dim range As Excel.Range
> Dim rCnt As Integer
> Dim cCnt As Integer
> Dim Obj As Object
> Dim msg As String
>
> xlApp = New Excel.ApplicationClass
> xlWorkBook = xlApp.Workbooks.Open(TextBox1.Text)
> xlWorkSheet = xlWorkBook.Worksheets("sheet1")
>
> range = xlWorkSheet.UsedRange
>
> For rCnt = 1 To range.Rows.Count
> For cCnt = 1 To range.Columns.Count
> Obj = CType(range.Cells(rCnt, cCnt), Excel.Range)
> msg = msg & Obj.value & "; "
> Next
> msg = msg & Chr(13) & Chr(10)
> Next
> MsgBox(msg)
>
> xlWorkBook.Close()
> xlApp.Quit()
>
> releaseObject(xlApp)
> releaseObject(xlWorkBook)
> releaseObject(xlWorkSheet)
> End If
> MsgBox("Test Complete")
>
> End Sub
>
> I know this is long but I am wanting to give as much info so hopefully someone can help explain where I am going wrong.
>
> Dan
>
9
I use syncfusion.XlsIO imports that is included with Vantage. The dll's are in
the client folder.


This is an example that will export the Part number and description to Excell.
(I limited this example to Partnum < "8")
There was 2 checkboxes on the form checkbox 1 to save the excel in csv format
and checkbox 2 to save in Excel worksheet

I had to use the Option Explicit line above the imports or I got a lot of the
undefined also.


'//**************************************************
'// Custom VB.NET code for UD01Form
'//**************************************************
Option Explicit
Imports System
Imports System.Data
Imports System.Diagnostics
Imports System.Windows.Forms
Imports System.ComponentModel
Imports Microsoft.VisualBasic
Imports Epicor.Mfg.UI
Imports Epicor.Mfg.UI.FrameWork
Imports Epicor.Mfg.UI.ExtendedProps
Imports Epicor.Mfg.UI.FormFunctions
Imports Epicor.Mfg.UI.Customization
Imports Epicor.Mfg.UI.Adapters
Imports Epicor.Mfg.UI.Searches
Imports Epicor.Mfg.BO
Imports syncfusion.XlsIO


   Private Sub btnEpiCustom2_Click(ByVal Sender As Object, ByVal Args As
System.EventArgs) Handles btnEpiCustom2.Click
          '// ** Place Event Handling Code Here **
     Dim xl As ExcelEngine = New ExcelEngine()
                Dim xlApp As IApplication = xl.Excel
                Dim wkbk As IWorkbook = xl.Excel.Workbooks.Create(1)
                Dim sht1 As IWorksheet = wkbk.Worksheets(0)
                sht1.Range("A1").Text = "Part Number"
                sht1.Range("B1").Text = "Description"
         Dim whereClause As String = "PartNum < '8' "
            Dim recSelected As Boolean
            Dim dsSearch As DataSet
             Dim cnt as integer
        Dim xlcnt as Integer = 2
        Dim ddpart as string
        Dim ddpartdesc as string
        dsSearch =
Epicor.Mfg.UI.FormFunctions.SearchFunctions.listLookup(oTrans,"PartAdapter",
recSelected, false, whereClause)
           If recSelected Then
      For cnt = 0 to dsSearch.Tables(0).rows.count - 1
                ddpart = dsSearch.Tables(0).Rows(cnt)("PartNum")
               ddpartDesc =
dsSearch.Tables(0).Rows(cnt)("PartDescription")
     sht1.Range("A"+xlcnt.tostring()).Text = ddpart
     sht1.Range("B"+xlcnt.tostring()).Text = ddpartdesc
     xlcnt = xlcnt + 1
      next
                  end if
    if txtEpiCustom2.text <> "" then
              if chkEpiCustom1.checked then
               wkbk.SaveAs(txtEpiCustom2.text & ".csv",",")
             ' wkbk.SaveAs("c:\test.csv",",")
              end if
       if chkEpiCustom2.checked then
              wkbk.SaveAs(txtEpiCustom2.text & ".xls",
ExcelSaveType.SaveAsXLS)
            ' wkbk.SaveAs("c:\test.xls", ExcelSaveType.SaveAsXLS)
          end if
  end if
                wkbk.close()
                xl.ThrowNotSavedOnDestroy = False
                xl.dispose()
    Messagebox.show("Finished writing to Excel")
    End Sub




Â



________________________________
From: bobschnable <bschnable@...>
To: vantage@yahoogroups.com
Sent: Mon, February 28, 2011 8:18:06 AM
Subject: [Vantage] Re: Need Help writing to Excel

Â
Based on how your code is setup, when you import the Excel namespace in the line
Imports Microsoft.Office.Interop.Excel, and then use Excel.Application to
declare some of your variables using Excel.Application, you are in essence
trying to use it twice. When I use Excel, I only use Imports
Microsoft.Office.Interop. Then, your variable declarations should work just
fine. You could also keep your current Imports, and change all declarations to
remove the Excel., but it's nice to do it the other way in case you are also
using Word, Outlook, etc.

--- In vantage@yahoogroups.com, "dgodfrey_amc" <dgodfrey_amc@...> wrote:
>
> OK, am really having a difficult time accessing an Excel file. I have been able
>to get it to work on my machine and thought everything was great until I
>deployed it. A user tried the customization and got a
>
>
> -- "Format of the initialization string does not conform to specification
>starting at index ..." error.
>
>
> my code was
>
> '-- MyConnection = New
>System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data
>source='" & myPath & " '; " & "Extended Properties=Excel 8.0;")
> '-- MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [sheet1$]
>where InActive= False", MyConnection)
> '-- MyCommand.Fill(dsECODataSet, "ECORecords")
>
> I starting looking in the history threads here and found someone talking about
>using this string
>
> -- Imports Microsoft.Office.Interop.Excel
>
> So I tried and tried using the Excel.Application and had no success compiling
> Error: BC30002 - line 1572 (2186) - Type 'Excel.Application' is not defined.
> Error: BC30002 - line 1573 (2187) - Type 'Excel.Workbook' is not defined.
> Error: BC30002 - line 1574 (2188) - Type 'Excel.Worksheet' is not defined.
> Error: BC30002 - line 1575 (2189) - Type 'Excel.Range' is not defined.
> Error: BC30002 - line 1581 (2195) - Type 'Excel.ApplicationClass' is not
>defined.
> Error: BC30002 - line 1589 (2203) - Type 'Excel.Range' is not defined.
>
> here was my test subroutine:
> Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As
>System.EventArgs) Handles Button1.Click
> Dim xcFileDialog As New OpenFileDialog()
>
> xcFileDialog.Filter = "Excel Spreadsheet Files|*.xls"
> xcFileDialog.Title = "Select excel spreadsheet file!"
> xcFileDialog.CheckFileExists() = True
>
> If xcFileDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
> If Not String.IsNullOrEmpty(xcFileDialog.FileName) Then
> TextBox1.Text = xcFileDialog.FileName
> End If
> End If
> If Not String.IsNullOrEmpty(TextBox1.Text) Then
> Dim xlApp As Excel.Application
> Dim xlWorkBook As Excel.Workbook
> Dim xlWorkSheet As Excel.Worksheet
> Dim range As Excel.Range
> Dim rCnt As Integer
> Dim cCnt As Integer
> Dim Obj As Object
> Dim msg As String
>
> xlApp = New Excel.ApplicationClass
> xlWorkBook = xlApp.Workbooks.Open(TextBox1.Text)
> xlWorkSheet = xlWorkBook.Worksheets("sheet1")
>
> range = xlWorkSheet.UsedRange
>
> For rCnt = 1 To range.Rows.Count
> For cCnt = 1 To range.Columns.Count
> Obj = CType(range.Cells(rCnt, cCnt), Excel.Range)
> msg = msg & Obj.value & "; "
> Next
> msg = msg & Chr(13) & Chr(10)
> Next
> MsgBox(msg)
>
> xlWorkBook.Close()
> xlApp.Quit()
>
> releaseObject(xlApp)
> releaseObject(xlWorkBook)
> releaseObject(xlWorkSheet)
> End If
> MsgBox("Test Complete")
>
> End Sub
>
> I know this is long but I am wanting to give as much info so hopefully someone
>can help explain where I am going wrong.
>
> Dan
>







[Non-text portions of this message have been removed]
10
Was there any referencing that you needed to do for this to work. I pasted in your code and it is not working for me. Here was the error.

Error: BC40056 - line 28 (28) - Namespace or type specified in the Imports 'syncfusion.XlsIO' doesn't contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member. Make sure the imported element name doesn't use any aliases.
Error: BC30002 - line 1573 (2187) - Type 'ExcelEngine' is not defined.
Error: BC30002 - line 1574 (2188) - Type 'IApplication' is not defined.
Error: BC30002 - line 1575 (2189) - Type 'IWorkbook' is not defined.
Error: BC30002 - line 1576 (2190) - Type 'IWorksheet' is not defined.

I am working in 8.03.407C

________________________________
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of DD
Sent: Monday, February 28, 2011 7:13 AM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Re: Need Help writing to Excel



I use syncfusion.XlsIO imports that is included with Vantage. The dll's are in
the client folder.

This is an example that will export the Part number and description to Excell.
(I limited this example to Partnum < "8")
There was 2 checkboxes on the form checkbox 1 to save the excel in csv format
and checkbox 2 to save in Excel worksheet

I had to use the Option Explicit line above the imports or I got a lot of the
undefined also.

'//**************************************************
'// Custom VB.NET code for UD01Form
'//**************************************************
Option Explicit
Imports System
Imports System.Data
Imports System.Diagnostics
Imports System.Windows.Forms
Imports System.ComponentModel
Imports Microsoft.VisualBasic
Imports Epicor.Mfg.UI
Imports Epicor.Mfg.UI.FrameWork
Imports Epicor.Mfg.UI.ExtendedProps
Imports Epicor.Mfg.UI.FormFunctions
Imports Epicor.Mfg.UI.Customization
Imports Epicor.Mfg.UI.Adapters
Imports Epicor.Mfg.UI.Searches
Imports Epicor.Mfg.BO
Imports syncfusion.XlsIO

Private Sub btnEpiCustom2_Click(ByVal Sender As Object, ByVal Args As
System.EventArgs) Handles btnEpiCustom2.Click
'// ** Place Event Handling Code Here **
Dim xl As ExcelEngine = New ExcelEngine()
Dim xlApp As IApplication = xl.Excel
Dim wkbk As IWorkbook = xl.Excel.Workbooks.Create(1)
Dim sht1 As IWorksheet = wkbk.Worksheets(0)
sht1.Range("A1").Text = "Part Number"
sht1.Range("B1").Text = "Description"
Dim whereClause As String = "PartNum < '8' "
Dim recSelected As Boolean
Dim dsSearch As DataSet
Dim cnt as integer
Dim xlcnt as Integer = 2
Dim ddpart as string
Dim ddpartdesc as string
dsSearch =
Epicor.Mfg.UI.FormFunctions.SearchFunctions.listLookup(oTrans,"PartAdapter",
recSelected, false, whereClause)
If recSelected Then
For cnt = 0 to dsSearch.Tables(0).rows.count - 1
ddpart = dsSearch.Tables(0).Rows(cnt)("PartNum")
ddpartDesc =
dsSearch.Tables(0).Rows(cnt)("PartDescription")
sht1.Range("A"+xlcnt.tostring()).Text = ddpart
sht1.Range("B"+xlcnt.tostring()).Text = ddpartdesc
xlcnt = xlcnt + 1
next
end if
if txtEpiCustom2.text <> "" then
if chkEpiCustom1.checked then
wkbk.SaveAs(txtEpiCustom2.text & ".csv",",")
' wkbk.SaveAs("c:\test.csv",",")
end if
if chkEpiCustom2.checked then
wkbk.SaveAs(txtEpiCustom2.text & ".xls",
ExcelSaveType.SaveAsXLS)
' wkbk.SaveAs("c:\test.xls", ExcelSaveType.SaveAsXLS)
end if
end if
wkbk.close()
xl.ThrowNotSavedOnDestroy = False
xl.dispose()
Messagebox.show("Finished writing to Excel")
End Sub



________________________________
From: bobschnable <bschnable@...<mailto:bschnable%40deephole.com>>
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
Sent: Mon, February 28, 2011 8:18:06 AM
Subject: [Vantage] Re: Need Help writing to Excel


Based on how your code is setup, when you import the Excel namespace in the line
Imports Microsoft.Office.Interop.Excel, and then use Excel.Application to
declare some of your variables using Excel.Application, you are in essence
trying to use it twice. When I use Excel, I only use Imports
Microsoft.Office.Interop. Then, your variable declarations should work just
fine. You could also keep your current Imports, and change all declarations to
remove the Excel., but it's nice to do it the other way in case you are also
using Word, Outlook, etc.

--- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>, "dgodfrey_amc" <dgodfrey_amc@...> wrote:
>
> OK, am really having a difficult time accessing an Excel file. I have been able
>to get it to work on my machine and thought everything was great until I
>deployed it. A user tried the customization and got a
>
>
> -- "Format of the initialization string does not conform to specification
>starting at index ..." error.
>
>
> my code was
>
> '-- MyConnection = New
>System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data
>source='" & myPath & " '; " & "Extended Properties=Excel 8.0;")
> '-- MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [sheet1$]
>where InActive= False", MyConnection)
> '-- MyCommand.Fill(dsECODataSet, "ECORecords")
>
> I starting looking in the history threads here and found someone talking about
>using this string
>
> -- Imports Microsoft.Office.Interop.Excel
>
> So I tried and tried using the Excel.Application and had no success compiling
> Error: BC30002 - line 1572 (2186) - Type 'Excel.Application' is not defined.
> Error: BC30002 - line 1573 (2187) - Type 'Excel.Workbook' is not defined.
> Error: BC30002 - line 1574 (2188) - Type 'Excel.Worksheet' is not defined.
> Error: BC30002 - line 1575 (2189) - Type 'Excel.Range' is not defined.
> Error: BC30002 - line 1581 (2195) - Type 'Excel.ApplicationClass' is not
>defined.
> Error: BC30002 - line 1589 (2203) - Type 'Excel.Range' is not defined.
>
> here was my test subroutine:
> Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As
>System.EventArgs) Handles Button1.Click
> Dim xcFileDialog As New OpenFileDialog()
>
> xcFileDialog.Filter = "Excel Spreadsheet Files|*.xls"
> xcFileDialog.Title = "Select excel spreadsheet file!"
> xcFileDialog.CheckFileExists() = True
>
> If xcFileDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
> If Not String.IsNullOrEmpty(xcFileDialog.FileName) Then
> TextBox1.Text = xcFileDialog.FileName
> End If
> End If
> If Not String.IsNullOrEmpty(TextBox1.Text) Then
> Dim xlApp As Excel.Application
> Dim xlWorkBook As Excel.Workbook
> Dim xlWorkSheet As Excel.Worksheet
> Dim range As Excel.Range
> Dim rCnt As Integer
> Dim cCnt As Integer
> Dim Obj As Object
> Dim msg As String
>
> xlApp = New Excel.ApplicationClass
> xlWorkBook = xlApp.Workbooks.Open(TextBox1.Text)
> xlWorkSheet = xlWorkBook.Worksheets("sheet1")
>
> range = xlWorkSheet.UsedRange
>
> For rCnt = 1 To range.Rows.Count
> For cCnt = 1 To range.Columns.Count
> Obj = CType(range.Cells(rCnt, cCnt), Excel.Range)
> msg = msg & Obj.value & "; "
> Next
> msg = msg & Chr(13) & Chr(10)
> Next
> MsgBox(msg)
>
> xlWorkBook.Close()
> xlApp.Quit()
>
> releaseObject(xlApp)
> releaseObject(xlWorkBook)
> releaseObject(xlWorkSheet)
> End If
> MsgBox("Test Complete")
>
> End Sub
>
> I know this is long but I am wanting to give as much info so hopefully someone
>can help explain where I am going wrong.
>
> Dan
>

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
11
I don't know what I am doing worng but there must be something either systemiatically wrong with my machine or I am missing some steps that are basic to VB that I am not familiar with because I did remove the ".Excel" from the import line and still I am erroring out.

I tried the other suggestion as well and nothing in that front also. Do you reference anything specific as well?

Dan

________________________________
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of bobschnable
Sent: Monday, February 28, 2011 6:18 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Need Help writing to Excel



Based on how your code is setup, when you import the Excel namespace in the line Imports Microsoft.Office.Interop.Excel, and then use Excel.Application to declare some of your variables using Excel.Application, you are in essence trying to use it twice. When I use Excel, I only use Imports Microsoft.Office.Interop. Then, your variable declarations should work just fine. You could also keep your current Imports, and change all declarations to remove the Excel., but it's nice to do it the other way in case you are also using Word, Outlook, etc.

--- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>, "dgodfrey_amc" <dgodfrey_amc@...> wrote:
>
> OK, am really having a difficult time accessing an Excel file. I have been able to get it to work on my machine and thought everything was great until I deployed it. A user tried the customization and got a
>
> -- "Format of the initialization string does not conform to specification starting at index ..." error.
>
> my code was
>
> '-- MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source='" & myPath & " '; " & "Extended Properties=Excel 8.0;")
> '-- MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [sheet1$] where InActive= False", MyConnection)
> '-- MyCommand.Fill(dsECODataSet, "ECORecords")
>
> I starting looking in the history threads here and found someone talking about using this string
>
> -- Imports Microsoft.Office.Interop.Excel
>
> So I tried and tried using the Excel.Application and had no success compiling
> Error: BC30002 - line 1572 (2186) - Type 'Excel.Application' is not defined.
> Error: BC30002 - line 1573 (2187) - Type 'Excel.Workbook' is not defined.
> Error: BC30002 - line 1574 (2188) - Type 'Excel.Worksheet' is not defined.
> Error: BC30002 - line 1575 (2189) - Type 'Excel.Range' is not defined.
> Error: BC30002 - line 1581 (2195) - Type 'Excel.ApplicationClass' is not defined.
> Error: BC30002 - line 1589 (2203) - Type 'Excel.Range' is not defined.
>
> here was my test subroutine:
> Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
> Dim xcFileDialog As New OpenFileDialog()
>
> xcFileDialog.Filter = "Excel Spreadsheet Files|*.xls"
> xcFileDialog.Title = "Select excel spreadsheet file!"
> xcFileDialog.CheckFileExists() = True
>
> If xcFileDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
> If Not String.IsNullOrEmpty(xcFileDialog.FileName) Then
> TextBox1.Text = xcFileDialog.FileName
> End If
> End If
> If Not String.IsNullOrEmpty(TextBox1.Text) Then
> Dim xlApp As Excel.Application
> Dim xlWorkBook As Excel.Workbook
> Dim xlWorkSheet As Excel.Worksheet
> Dim range As Excel.Range
> Dim rCnt As Integer
> Dim cCnt As Integer
> Dim Obj As Object
> Dim msg As String
>
> xlApp = New Excel.ApplicationClass
> xlWorkBook = xlApp.Workbooks.Open(TextBox1.Text)
> xlWorkSheet = xlWorkBook.Worksheets("sheet1")
>
> range = xlWorkSheet.UsedRange
>
> For rCnt = 1 To range.Rows.Count
> For cCnt = 1 To range.Columns.Count
> Obj = CType(range.Cells(rCnt, cCnt), Excel.Range)
> msg = msg & Obj.value & "; "
> Next
> msg = msg & Chr(13) & Chr(10)
> Next
> MsgBox(msg)
>
> xlWorkBook.Close()
> xlApp.Quit()
>
> releaseObject(xlApp)
> releaseObject(xlWorkBook)
> releaseObject(xlWorkSheet)
> End If
> MsgBox("Test Complete")
>
> End Sub
>
> I know this is long but I am wanting to give as much info so hopefully someone can help explain where I am going wrong.
>
> Dan
>





[Non-text portions of this message have been removed]
12
In customization select tools / assembly reference manager
choose the Add Custom Reference button.
Use the dropdown on the bottom right to view all files instead of Application
Assemblies

add the following 2 dll's
syncfusion.XlsIO.Base.dll
syncfusion.XlsIO.windows.dll

I am on 8.03.409A and this was also in 8.03.405 so should work for you.

If you are still having trouble then I will send my contact info to your email
offline.

Doug






________________________________
From: Dan Godfrey <dgodfrey@...>
To: "vantage@yahoogroups.com" <vantage@yahoogroups.com>
Sent: Mon, February 28, 2011 11:59:15 AM
Subject: RE: [Vantage] Re: Need Help writing to Excel

Â
Was there any referencing that you needed to do for this to work. I pasted in
your code and it is not working for me. Here was the error.

Error: BC40056 - line 28 (28) - Namespace or type specified in the Imports
'syncfusion.XlsIO' doesn't contain any public member or cannot be found. Make
sure the namespace or the type is defined and contains at least one public
member. Make sure the imported element name doesn't use any aliases.
Error: BC30002 - line 1573 (2187) - Type 'ExcelEngine' is not defined.
Error: BC30002 - line 1574 (2188) - Type 'IApplication' is not defined.
Error: BC30002 - line 1575 (2189) - Type 'IWorkbook' is not defined.
Error: BC30002 - line 1576 (2190) - Type 'IWorksheet' is not defined.

I am working in 8.03.407C

________________________________
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of DD
Sent: Monday, February 28, 2011 7:13 AM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Re: Need Help writing to Excel

I use syncfusion.XlsIO imports that is included with Vantage. The dll's are in
the client folder.

This is an example that will export the Part number and description to Excell.
(I limited this example to Partnum < "8")
There was 2 checkboxes on the form checkbox 1 to save the excel in csv format
and checkbox 2 to save in Excel worksheet

I had to use the Option Explicit line above the imports or I got a lot of the
undefined also.

'//**************************************************
'// Custom VB.NET code for UD01Form
'//**************************************************
Option Explicit
Imports System
Imports System.Data
Imports System.Diagnostics
Imports System.Windows.Forms
Imports System.ComponentModel
Imports Microsoft.VisualBasic
Imports Epicor.Mfg.UI
Imports Epicor.Mfg.UI.FrameWork
Imports Epicor.Mfg.UI.ExtendedProps
Imports Epicor.Mfg.UI.FormFunctions
Imports Epicor.Mfg.UI.Customization
Imports Epicor.Mfg.UI.Adapters
Imports Epicor.Mfg.UI.Searches
Imports Epicor.Mfg.BO
Imports syncfusion.XlsIO

Private Sub btnEpiCustom2_Click(ByVal Sender As Object, ByVal Args As
System.EventArgs) Handles btnEpiCustom2.Click
'// ** Place Event Handling Code Here **
Dim xl As ExcelEngine = New ExcelEngine()
Dim xlApp As IApplication = xl.Excel
Dim wkbk As IWorkbook = xl.Excel.Workbooks.Create(1)
Dim sht1 As IWorksheet = wkbk.Worksheets(0)
sht1.Range("A1").Text = "Part Number"
sht1.Range("B1").Text = "Description"
Dim whereClause As String = "PartNum < '8' "
Dim recSelected As Boolean
Dim dsSearch As DataSet
Dim cnt as integer
Dim xlcnt as Integer = 2
Dim ddpart as string
Dim ddpartdesc as string
dsSearch =
Epicor.Mfg.UI.FormFunctions.SearchFunctions.listLookup(oTrans,"PartAdapter",
recSelected, false, whereClause)
If recSelected Then
For cnt = 0 to dsSearch.Tables(0).rows.count - 1
ddpart = dsSearch.Tables(0).Rows(cnt)("PartNum")
ddpartDesc =
dsSearch.Tables(0).Rows(cnt)("PartDescription")
sht1.Range("A"+xlcnt.tostring()).Text = ddpart
sht1.Range("B"+xlcnt.tostring()).Text = ddpartdesc
xlcnt = xlcnt + 1
next
end if
if txtEpiCustom2.text <> "" then
if chkEpiCustom1.checked then
wkbk.SaveAs(txtEpiCustom2.text & ".csv",",")
' wkbk.SaveAs("c:\test.csv",",")
end if
if chkEpiCustom2.checked then
wkbk.SaveAs(txtEpiCustom2.text & ".xls",
ExcelSaveType.SaveAsXLS)
' wkbk.SaveAs("c:\test.xls", ExcelSaveType.SaveAsXLS)
end if
end if
wkbk.close()
xl.ThrowNotSavedOnDestroy = False
xl.dispose()
Messagebox.show("Finished writing to Excel")
End Sub

________________________________
From: bobschnable <bschnable@...<mailto:bschnable%40deephole.com>>
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
Sent: Mon, February 28, 2011 8:18:06 AM
Subject: [Vantage] Re: Need Help writing to Excel

Based on how your code is setup, when you import the Excel namespace in the line
Imports Microsoft.Office.Interop.Excel, and then use Excel.Application to
declare some of your variables using Excel.Application, you are in essence
trying to use it twice. When I use Excel, I only use Imports
Microsoft.Office.Interop. Then, your variable declarations should work just
fine. You could also keep your current Imports, and change all declarations to
remove the Excel., but it's nice to do it the other way in case you are also
using Word, Outlook, etc.

--- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>, "dgodfrey_amc"
<dgodfrey_amc@...> wrote:
>
> OK, am really having a difficult time accessing an Excel file. I have been
able
>to get it to work on my machine and thought everything was great until I
>deployed it. A user tried the customization and got a
>
>
> -- "Format of the initialization string does not conform to specification
>starting at index ..." error.
>
>
> my code was
>
> '-- MyConnection = New
>System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data
>source='" & myPath & " '; " & "Extended Properties=Excel 8.0;")
> '-- MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from
[sheet1$]
>where InActive= False", MyConnection)
> '-- MyCommand.Fill(dsECODataSet, "ECORecords")
>
> I starting looking in the history threads here and found someone talking about
>using this string
>
> -- Imports Microsoft.Office.Interop.Excel
>
> So I tried and tried using the Excel.Application and had no success compiling
> Error: BC30002 - line 1572 (2186) - Type 'Excel.Application' is not defined.
> Error: BC30002 - line 1573 (2187) - Type 'Excel.Workbook' is not defined.
> Error: BC30002 - line 1574 (2188) - Type 'Excel.Worksheet' is not defined.
> Error: BC30002 - line 1575 (2189) - Type 'Excel.Range' is not defined.
> Error: BC30002 - line 1581 (2195) - Type 'Excel.ApplicationClass' is not
>defined.
> Error: BC30002 - line 1589 (2203) - Type 'Excel.Range' is not defined.
>
> here was my test subroutine:
> Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As
>System.EventArgs) Handles Button1.Click
> Dim xcFileDialog As New OpenFileDialog()
>
> xcFileDialog.Filter = "Excel Spreadsheet Files|*.xls"
> xcFileDialog.Title = "Select excel spreadsheet file!"
> xcFileDialog.CheckFileExists() = True
>
> If xcFileDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
> If Not String.IsNullOrEmpty(xcFileDialog.FileName) Then
> TextBox1.Text = xcFileDialog.FileName
> End If
> End If
> If Not String.IsNullOrEmpty(TextBox1.Text) Then
> Dim xlApp As Excel.Application
> Dim xlWorkBook As Excel.Workbook
> Dim xlWorkSheet As Excel.Worksheet
> Dim range As Excel.Range
> Dim rCnt As Integer
> Dim cCnt As Integer
> Dim Obj As Object
> Dim msg As String
>
> xlApp = New Excel.ApplicationClass
> xlWorkBook = xlApp.Workbooks.Open(TextBox1.Text)
> xlWorkSheet = xlWorkBook.Worksheets("sheet1")
>
> range = xlWorkSheet.UsedRange
>
> For rCnt = 1 To range.Rows.Count
> For cCnt = 1 To range.Columns.Count
> Obj = CType(range.Cells(rCnt, cCnt), Excel.Range)
> msg = msg & Obj.value & "; "
> Next
> msg = msg & Chr(13) & Chr(10)
> Next
> MsgBox(msg)
>
> xlWorkBook.Close()
> xlApp.Quit()
>
> releaseObject(xlApp)
> releaseObject(xlWorkBook)
> releaseObject(xlWorkSheet)
> End If
> MsgBox("Test Complete")
>
> End Sub
>
> I know this is long but I am wanting to give as much info so hopefully someone
>can help explain where I am going wrong.
>
> Dan
>

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]







[Non-text portions of this message have been removed]
13
Here is a sample of my code that works.
My Imports statements:
Imports System
Imports System.Data
Imports System.Diagnostics
Imports System.Windows.Forms
Imports System.ComponentModel
Imports Microsoft.VisualBasic
Imports Epicor.Mfg.UI
Imports Epicor.Mfg.UI.FrameWork
Imports Epicor.Mfg.UI.ExtendedProps
Imports Epicor.Mfg.UI.FormFunctions
Imports Epicor.Mfg.UI.Customization
Imports Epicor.Mfg.UI.Adapters
Imports Epicor.Mfg.UI.Searches
Imports Epicor.Mfg.BO
Imports Microsoft.Office.Interop
Imports Microsoft.VisualBasic.Strings


Instantiating Word for the first time, and declaring some Word documents
Dim wrd As New Word.Application
Dim Quote As Word.Document
Dim QuoteLineAdd As Word.Document
wrd.Visible = True
Quote = wrd.Documents.Open("Y:\Customers\Quotes\Quote Form.docx")

Later in the code, I also instantiate Outlook, and create a new message
Dim olk As New Outlook.Application
Dim olkMsg As Outlook.MailItem = olk.CreateItem(Outlook.OlItemType.olMailItem)


Like I said in my earlier post, I use Imports Microsoft.Office.Interop only, and that way I can use both Word and Outlook. The same goes for Excel. The only custom assemblies I have added are Microsoft.Office.Interop.Outlook and Microsoft.Office.Interop.Word, as highlighted in the procedure I posted last week.

--- In vantage@yahoogroups.com, Dan Godfrey <dgodfrey@...> wrote:
>
> I don't know what I am doing worng but there must be something either systemiatically wrong with my machine or I am missing some steps that are basic to VB that I am not familiar with because I did remove the ".Excel" from the import line and still I am erroring out.
>
> I tried the other suggestion as well and nothing in that front also. Do you reference anything specific as well?
>
> Dan
>
> ________________________________
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of bobschnable
> Sent: Monday, February 28, 2011 6:18 AM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Re: Need Help writing to Excel
>
>
>
> Based on how your code is setup, when you import the Excel namespace in the line Imports Microsoft.Office.Interop.Excel, and then use Excel.Application to declare some of your variables using Excel.Application, you are in essence trying to use it twice. When I use Excel, I only use Imports Microsoft.Office.Interop. Then, your variable declarations should work just fine. You could also keep your current Imports, and change all declarations to remove the Excel., but it's nice to do it the other way in case you are also using Word, Outlook, etc.
>
> --- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>, "dgodfrey_amc" <dgodfrey_amc@> wrote:
> >
> > OK, am really having a difficult time accessing an Excel file. I have been able to get it to work on my machine and thought everything was great until I deployed it. A user tried the customization and got a
> >
> > -- "Format of the initialization string does not conform to specification starting at index ..." error.
> >
> > my code was
> >
> > '-- MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source='" & myPath & " '; " & "Extended Properties=Excel 8.0;")
> > '-- MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [sheet1$] where InActive= False", MyConnection)
> > '-- MyCommand.Fill(dsECODataSet, "ECORecords")
> >
> > I starting looking in the history threads here and found someone talking about using this string
> >
> > -- Imports Microsoft.Office.Interop.Excel
> >
> > So I tried and tried using the Excel.Application and had no success compiling
> > Error: BC30002 - line 1572 (2186) - Type 'Excel.Application' is not defined.
> > Error: BC30002 - line 1573 (2187) - Type 'Excel.Workbook' is not defined.
> > Error: BC30002 - line 1574 (2188) - Type 'Excel.Worksheet' is not defined.
> > Error: BC30002 - line 1575 (2189) - Type 'Excel.Range' is not defined.
> > Error: BC30002 - line 1581 (2195) - Type 'Excel.ApplicationClass' is not defined.
> > Error: BC30002 - line 1589 (2203) - Type 'Excel.Range' is not defined.
> >
> > here was my test subroutine:
> > Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
> > Dim xcFileDialog As New OpenFileDialog()
> >
> > xcFileDialog.Filter = "Excel Spreadsheet Files|*.xls"
> > xcFileDialog.Title = "Select excel spreadsheet file!"
> > xcFileDialog.CheckFileExists() = True
> >
> > If xcFileDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
> > If Not String.IsNullOrEmpty(xcFileDialog.FileName) Then
> > TextBox1.Text = xcFileDialog.FileName
> > End If
> > End If
> > If Not String.IsNullOrEmpty(TextBox1.Text) Then
> > Dim xlApp As Excel.Application
> > Dim xlWorkBook As Excel.Workbook
> > Dim xlWorkSheet As Excel.Worksheet
> > Dim range As Excel.Range
> > Dim rCnt As Integer
> > Dim cCnt As Integer
> > Dim Obj As Object
> > Dim msg As String
> >
> > xlApp = New Excel.ApplicationClass
> > xlWorkBook = xlApp.Workbooks.Open(TextBox1.Text)
> > xlWorkSheet = xlWorkBook.Worksheets("sheet1")
> >
> > range = xlWorkSheet.UsedRange
> >
> > For rCnt = 1 To range.Rows.Count
> > For cCnt = 1 To range.Columns.Count
> > Obj = CType(range.Cells(rCnt, cCnt), Excel.Range)
> > msg = msg & Obj.value & "; "
> > Next
> > msg = msg & Chr(13) & Chr(10)
> > Next
> > MsgBox(msg)
> >
> > xlWorkBook.Close()
> > xlApp.Quit()
> >
> > releaseObject(xlApp)
> > releaseObject(xlWorkBook)
> > releaseObject(xlWorkSheet)
> > End If
> > MsgBox("Test Complete")
> >
> > End Sub
> >
> > I know this is long but I am wanting to give as much info so hopefully someone can help explain where I am going wrong.
> >
> > Dan
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
14
I am having another problem. I am using the Excel.Application option to read and hopefully write to the spreadsheet. As of right now I can not even open the spreadsheet. All of the code compiles fine, but I get runtime errors. Right now I am getting

"Attempted to read or write protected memory. This is often an indication that other memory is corrupt."

When I execute xlWorkBook = xlApp.Workbooks.Open(txtECOFile.Text)

Here is my code. Does anyone see anything that wrong with what I am coding? The file is definitely NOT protected and it is sitting on my C drive.

Private Sub TestingExcelApplication()
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim range As Excel.Range
Dim rCnt As Integer
Dim cCnt As Integer
Dim Obj As Object
Dim msg As String = ""
Try
If System.IO.File.Exists(txtECOFile.Text) = True Then

xlApp = New Excel.Application
xlApp.visible = False
xlApp.UserControl = False
xlWorkBook = xlApp.Workbooks.Open(txtECOFile.Text)

xlWorkSheet = xlWorkBook.Worksheets("Sheet1")

range = xlWorkSheet.UsedRange
'dsECODataSet = xlWorkSheet.ExportDataTable(xlWorkSheet.UsedRange, ExcelExportDataTableOptions.ColumnNames)
For rCnt = 1 To range.Rows.Count
For cCnt = 1 To range.Columns.Count
Obj = CType(range.Cells(rCnt, cCnt), Excel.Range)
msg = msg & Obj.value & "; "
Next
msg = msg & Chr(13) & Chr(10)
Next

xlWorkBook.Close()
xlApp.Quit()


Message")
dsECODataSet.tables.Clear()
End If
Catch ex As Exception
Dim er AS String = TRIM(Ex.Message)
MyMessageBox(er, True, MsgBoxStyle.OKOnly, "Testing OleDB Connection string")
Finally
releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)
End Try
End Sub


[Non-text portions of this message have been removed]
15
Have you tried testing your code using another file? If it works, then there is an issue with the file you are using, which seems more likely to me, as your code looks like it should work.


--- In vantage@yahoogroups.com, Dan Godfrey <dgodfrey@...> wrote:
>
> I am having another problem. I am using the Excel.Application option to read and hopefully write to the spreadsheet. As of right now I can not even open the spreadsheet. All of the code compiles fine, but I get runtime errors. Right now I am getting
>
> "Attempted to read or write protected memory. This is often an indication that other memory is corrupt."
>
> When I execute xlWorkBook = xlApp.Workbooks.Open(txtECOFile.Text)
>
> Here is my code. Does anyone see anything that wrong with what I am coding? The file is definitely NOT protected and it is sitting on my C drive.
>
> Private Sub TestingExcelApplication()
> Dim xlApp As Excel.Application
> Dim xlWorkBook As Excel.Workbook
> Dim xlWorkSheet As Excel.Worksheet
> Dim range As Excel.Range
> Dim rCnt As Integer
> Dim cCnt As Integer
> Dim Obj As Object
> Dim msg As String = ""
> Try
> If System.IO.File.Exists(txtECOFile.Text) = True Then
>
> xlApp = New Excel.Application
> xlApp.visible = False
> xlApp.UserControl = False
> xlWorkBook = xlApp.Workbooks.Open(txtECOFile.Text)
>
> xlWorkSheet = xlWorkBook.Worksheets("Sheet1")
>
> range = xlWorkSheet.UsedRange
> 'dsECODataSet = xlWorkSheet.ExportDataTable(xlWorkSheet.UsedRange, ExcelExportDataTableOptions.ColumnNames)
> For rCnt = 1 To range.Rows.Count
> For cCnt = 1 To range.Columns.Count
> Obj = CType(range.Cells(rCnt, cCnt), Excel.Range)
> msg = msg & Obj.value & "; "
> Next
> msg = msg & Chr(13) & Chr(10)
> Next
>
> xlWorkBook.Close()
> xlApp.Quit()
>
>
> Message")
> dsECODataSet.tables.Clear()
> End If
> Catch ex As Exception
> Dim er AS String = TRIM(Ex.Message)
> MyMessageBox(er, True, MsgBoxStyle.OKOnly, "Testing OleDB Connection string")
> Finally
> releaseObject(xlApp)
> releaseObject(xlWorkBook)
> releaseObject(xlWorkSheet)
> End Try
> End Sub
>
>
> [Non-text portions of this message have been removed]
>
16
I changed my open statement to 'xlWorkBook = xlApp.Workbooks._Open(txtECOFile.Text, 2, False, 5)' and it worked. All other lines of code has passed as well.

I don't know what the difference is but it works. Now I need to figure out how to get the excel data into a dataset.

Thanks for you help and time.

Dan

________________________________
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of bobschnable
Sent: Tuesday, March 01, 2011 11:11 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Need Help writing to Excel



Have you tried testing your code using another file? If it works, then there is an issue with the file you are using, which seems more likely to me, as your code looks like it should work.

--- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>, Dan Godfrey <dgodfrey@...> wrote:
>
> I am having another problem. I am using the Excel.Application option to read and hopefully write to the spreadsheet. As of right now I can not even open the spreadsheet. All of the code compiles fine, but I get runtime errors. Right now I am getting
>
> "Attempted to read or write protected memory. This is often an indication that other memory is corrupt."
>
> When I execute xlWorkBook = xlApp.Workbooks.Open(txtECOFile.Text)
>
> Here is my code. Does anyone see anything that wrong with what I am coding? The file is definitely NOT protected and it is sitting on my C drive.
>
> Private Sub TestingExcelApplication()
> Dim xlApp As Excel.Application
> Dim xlWorkBook As Excel.Workbook
> Dim xlWorkSheet As Excel.Worksheet
> Dim range As Excel.Range
> Dim rCnt As Integer
> Dim cCnt As Integer
> Dim Obj As Object
> Dim msg As String = ""
> Try
> If System.IO.File.Exists(txtECOFile.Text) = True Then
>
> xlApp = New Excel.Application
> xlApp.visible = False
> xlApp.UserControl = False
> xlWorkBook = xlApp.Workbooks.Open(txtECOFile.Text)
>
> xlWorkSheet = xlWorkBook.Worksheets("Sheet1")
>
> range = xlWorkSheet.UsedRange
> 'dsECODataSet = xlWorkSheet.ExportDataTable(xlWorkSheet.UsedRange, ExcelExportDataTableOptions.ColumnNames)
> For rCnt = 1 To range.Rows.Count
> For cCnt = 1 To range.Columns.Count
> Obj = CType(range.Cells(rCnt, cCnt), Excel.Range)
> msg = msg & Obj.value & "; "
> Next
> msg = msg & Chr(13) & Chr(10)
> Next
>
> xlWorkBook.Close()
> xlApp.Quit()
>
>
> Message")
> dsECODataSet.tables.Clear()
> End If
> Catch ex As Exception
> Dim er AS String = TRIM(Ex.Message)
> MyMessageBox(er, True, MsgBoxStyle.OKOnly, "Testing OleDB Connection string")
> Finally
> releaseObject(xlApp)
> releaseObject(xlWorkBook)
> releaseObject(xlWorkSheet)
> End Try
> End Sub
>
>
> [Non-text portions of this message have been removed]
>





[Non-text portions of this message have been removed]