Re: export data to excel by specify position in excel

Posted 18 February 2013 - 06:13 AM

Quote

but when i try to Imports Microsoft.Office.Interop.Excel
it just wont work. the range keep giving me blue field.

Do you mean the text has a blue underline? Point at the word and a message should pop-up: what is this message? It might even offer to correct it for you. (This is fundamental to the use of Visual Studio and you should spend time to learn these features.)

Have you added a reference to the COM Excel library?

You will need to show some of your code that isn't working if you want more help.

If you try and run your code then it will give you error messages as well. What are these error messages..?

Erm you need to be more forthcoming; that is, to help people to help you.

Re: export data to excel by specify position in excel

Posted 18 February 2013 - 06:27 AM

andrewsw, on 18 February 2013 - 06:13 AM, said:

Quote

but when i try to Imports Microsoft.Office.Interop.Excel
it just wont work. the range keep giving me blue field.

Do you mean the text has a blue underline? Point at the word and a message should pop-up: what is this message? It might even offer to correct it for you. (This is fundamental to the use of Visual Studio and you should spend time to learn these features.)

Have you added a reference to the COM Excel library?

You will need to show some of your code that isn't working if you want more help.

If you try and run your code then it will give you error messages as well. What are these error messages..?

Erm you need to be more forthcoming; that is, to help people to help you.

it says range is a type and cannot be used as a expression
when i move my mouse to the Range before i compile.
i added the reference at .net instead of COM
i cant find office.interpre.excel at COm so i add at at .net Tab instead

Re: export data to excel by specify position in excel

Posted 18 February 2013 - 06:58 AM

You can't just type Range("A6") in Visual Basic and expect it to know that you are referring to an Excel Range. You need to spend time studying the fundamentals of programming and VB.NET. Alternatively, DIC has a post a job section.

Quote

i did not write all the code above. take references from here and there and came out with this.

Re: export data to excel by specify position in excel

Posted 19 February 2013 - 03:43 AM

ok this is my code that makes it work.

a little crazy at if else statement. but it works!
THanks andrew

Sub xlws()
Dim xl As New Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim DoNotSaveChanges As Boolean = False
wkb = xl.Workbooks.Open("D:\Book1.xls")
wks = wkb.Sheets.Item("Sheet1")
Dim dbRS As ADODB.Recordset
dbRS = dbConnection.Recordsets
dbRS.Open("SELECT [Preset Key].PK_Key,[PluDetails].CM_Message from [PluDetails] inner join [Preset Key] on [PluDetails].PLU.PLU_Index=[Preset key].PK_Link ")
Dim dd As String
Dim d2 As String
dbRS.GetRows(3)
Do While Not dbRS.EOF
wks.Rows().RowHeight = 58
Dim hi As Double
hi = 7
wks.Cells.Font.Size = 10
wks.Columns().ColumnWidth = hi
wks.Cells.Borders.LineStyle = 12
dd = dbRS(0).Value()
d2 = dbRS(1).Value()
If dd = "1" Then
wks.Cells(8, 1) = d2
End If
If dd = "2" Then
wks.Cells(8, 2) = d2
dbRS.MoveNext()
End If
If dd = "3" Then
wks.Cells(8, 3) = d2
dbRS.MoveNext()
End If
If dd = "4" Then
wks.Cells(8, 4) = d2
dbRS.MoveNext()
End If
If dd = "5" Then
wks.Cells(8, 5) = d2
End If
If dd = "6" Then
wks.Cells(8, 6) = d2
End If
If dd = "7" Then
wks.Cells(8, 7) = d2
End If
If dd = "8" Then
wks.Cells(8, 8) = d2
End If
If dd = "9" Then
wks.Cells(7, 1) = d2
End If
If dd = "10" Then
wks.Cells(7, 2) = d2
End If
If dd = "11" Then
wks.Cells(7, 3) = d2
End If
If dd = "12" Then
wks.Cells(7, 4) = d2
End If
If dd = "13" Then
wks.Cells(7, 5) = d2
End If
If dd = "14" Then
wks.Cells(7, 6) = d2
End If
If dd = "15" Then
wks.Cells(7, 7) = d2
End If
If dd = "16" Then
wks.Cells(7, 8) = d2
End If
If dd = "17" Then
wks.Cells(6, 1) = d2
End If
If dd = "18" Then
wks.Cells(6, 2) = d2
End If
If dd = "19" Then
wks.Cells(6, 3) = d2
End If
If dd = "20" Then
wks.Cells(6, 4) = d2
End If
If dd = "21" Then
wks.Cells(6, 5) = d2
End If
If dd = "22" Then
wks.Cells(6, 6) = d2
End If
If dd = "23" Then
wks.Cells(6, 7) = d2
End If
If dd = "24" Then
wks.Cells(6, 8) = d2
End If
If dd = "25" Then wks.Cells(5, 1) = d2
If dd = "26" Then
wks.Cells(5, 2) = d2
End If
If dd = "27" Then
wks.Cells(5, 3) = d2
End If
If dd = "28" Then
wks.Cells(5, 4) = d2
End If
If dd = "29" Then
wks.Cells(5, 5) = d2
End If
If dd = "30" Then
wks.Cells(5, 6) = d2
End If
If dd = "31" Then
wks.Cells(5, 7) = d2
End If
If dd = "32" Then
wks.Cells(5, 8) = d2
End If
If dd = "33" Then
wks.Cells(4, 1) = d2
End If
If dd = "34" Then
wks.Cells(4, 2) = d2
End If
If dd = "35" Then
wks.Cells(4, 3) = d2
End If
If dd = "36" Then
wks.Cells(4, 4) = d2
End If
If dd = "37" Then
wks.Cells(4, 5) = d2
End If
If dd = "38" Then
wks.Cells(4, 6) = d2
End If
If dd = "39" Then
wks.Cells(4, 7) = d2
End If
If dd = "40" Then
wks.Cells(4, 8) = d2
End If
If dd = "41" Then
wks.Cells(3, 1) = d2
End If
If dd = "42" Then
wks.Cells(3, 2) = d2
End If
If dd = "43" Then
wks.Cells(3, 3) = d2
End If
If dd = "44" Then
wks.Cells(3, 4) = d2
End If
If dd = "45" Then
wks.Cells(3, 5) = d2
End If
If dd = "46" Then
wks.Cells(3, 6) = d2
End If
If dd = "47" Then
wks.Cells(3, 7) = d2
End If
If dd = "48" Then
wks.Cells(3, 8) = d2
End If
If dd = "49" Then
wks.Cells(2, 1) = d2
End If
If dd = "50" Then
wks.Cells(2, 2) = d2
End If
If dd = "51" Then
wks.Cells(2, 3) = d2
End If
If dd = "52" Then
wks.Cells(2, 4) = d2
End If
If dd = "53" Then
wks.Cells(2, 5) = d2
End If
If dd = "54 Then" Then
wks.Cells(2, 6) = d2
End If
If dd = "55" Then
wks.Cells(2, 7) = d2
End If
If dd = "56" Then
wks.Cells(2, 8) = d2
End If
If dd = "57" Then
wks.Cells(1, 1) = d2
End If
If dd = "58" Then
wks.Cells(1, 2) = d2
End If
If dd = "59" Then
wks.Cells(1, 3) = d2
End If
If dd = "60" Then
wks.Cells(1, 4) = d2
End If
If dd = "61" Then
wks.Cells(1, 5) = d2
End If
If dd = "62" Then
wks.Cells(1, 6) = d2
End If
If dd = "63" Then
wks.Cells(1, 7) = d2
End If
If dd = "64" Then
wks.Cells(1, 8) = d2
End If
If dd = "65" Then
wks.Cells(3, 10) = d2
End If
If dd = "66" Then
wks.Cells(3, 11) = d2
End If
If dd = "67" Then
wks.Cells(3, 12) = d2
End If
If dd = "68" Then
wks.Cells(3, 13) = d2
End If
If dd = "69" Then
wks.Cells(3, 14) = d2
End If
If dd = "70" Then
wks.Cells(3, 15) = d2
End If
If dd = "71" Then
wks.Cells(2, 9) = d2
End If
If dd = "72" Then
wks.Cells(2, 10) = d2
End If
If dd = "73" Then
wks.Cells(2, 11) = d2
End If
If dd = "74" Then
wks.Cells(2, 12) = d2
End If
If dd = "75" Then
wks.Cells(2, 13) = d2
End If
If dd = "76" Then
wks.Cells(2, 14) = d2
End If
If dd = "77" Then
wks.Cells(2, 15) = d2
End If
If dd = "78" Then
wks.Cells(1, 10) = d2
End If
If dd = "79" Then
wks.Cells(1, 11) = d2
End If
If dd = "80" Then
wks.Cells(1, 12) = d2
End If
If dd = "81" Then
wks.Cells(1, 13) = d2
End If
If dd = "82" Then
wks.Cells(1, 14) = d2
End If
dbRS.MoveNext()
Loop
xl.Visible = True
xl.UserControl = True
wks = Nothing
wkb = Nothing
xl = Nothing
GC.Collect()