[chenaz]

hi, i wanted to export data from access to excel to specify position.

how do i archive this? i only manage to export all data to excel . but i wan to re-arrange them? can anyone give me some clues?

[andrewsw]

Clues: record macros in Excel.

If you need more assistance then you'll need to show your code.

[andrewsw]

DUPLICATE DELETED

This post has been edited by andrewsw: 18 February 2013 - 03:04 AM

[chenaz]

andrewsw, on 18 February 2013 - 03:01 AM, said:

Clues: record macros in Excel.

If you need more assistance then you'll need to show your code.

hi thanks for ur quick reply. i just reached home.

   Private Sub Load_Excel_Details()
        Dim con As New OleDb.OleDbConnection

        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\User\Desktop\eLeap (preset key)\eLeap\bin\Debug\eleap.mdb;Persist Security Info=True;Jet OLEDB:Database Password=123"


        con.Open()
        Dim ds As New DataSet


        'Extracting from database
        Dim sql, filename As String
        Dim col, row As Integer
        sql = "SELECT * from [PRESET KEY]"
        Dim da As New OleDb.OleDbDataAdapter(sql, con)

            da.Fill(ds, "[PRESET KEY]")
           
       



        Dim Excel As Object = CreateObject("Excel.Application")
        


        'Export to Excel process
        Try
            With Excel
                .SheetsInNewWorkbook = 1
                .Workbooks.Add()
                .Worksheets(1).Select()

                Dim i As Integer = 1
                For col = 0 To ds.Tables(0).Columns.Count - 1
                    .cells(1, i).value = ds.Tables(0).Columns(col).ColumnName
                    .cells(1, i).EntireRow.Font.Bold = True
                    i += 1
                Next
                i = 2
                Dim k As Integer = 1
                For col = 0 To ds.Tables(0).Columns.Count - 1
                    i = 2
                    For row = 0 To ds.Tables(0).Rows.Count - 1
                        .Cells(i, k).Value = ds.Tables(0).Rows(row).ItemArray(col)
                        i += 1
                    Next
                    k += 1
                Next
                filename = "D:\PLU" & Format(Now(), "dd-MM-yyyy_hh-mm-ss") & ".xls"
                .ActiveCell.Worksheet.SaveAs(filename)
            End With
            System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
            Excel = Nothing
            MsgBox("Data's are exported to Excel Succesfully in '" & filename & "'", MsgBoxStyle.Information)

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try


        Dim pro() As Process = System.Diagnostics.Process.GetProcessesByName("EXCEL")
        For Each i As Process In pro
            i.Kill()
        Next

    End Sub

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

[andrewsw]

Did you try recording macros then..?

Anyway, the recording process is the way to go if you are not familiar with the Excel Object Model. It wouldn't yield code such as the following however:

    Columns("F:F").Cut
    Columns("C:C").Insert Shift:=xlToRight

This post has been edited by andrewsw: 18 February 2013 - 05:07 AM

[chenaz]

andrewsw, on 18 February 2013 - 05:07 AM, said:

Did you try recording macros then..?

Anyway, the recording process is the way to go if you are not familiar with the Excel Object Model. It wouldn't yield code such as the following however:

    Columns("F:F").Cut
    Columns("C:C").Insert Shift:=xlToRight

Hi,

i tried using Range("A1").value

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

and for the columns, do i have to declare anything first?

thanks so much

This post has been edited by chenaz: 18 February 2013 - 06:08 AM

[andrewsw]

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.

This post has been edited by andrewsw: 18 February 2013 - 06:13 AM

[chenaz]

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

and really thanks for ur help

[andrewsw]

Quote

it says range is a type and cannot be used as a expression
when i move my mouse to the Range before i compile.

That doesn't help without showing your code.

Quote

i added the reference at .net instead of COM

Yeah, you can't just make stuff up . Untick that option and search for Excel in the COM tab; it will show as Excel 14.0 Object Library or similar. (There is a search box that you can use.)

[chenaz]

Hi

Imports Microsoft.Office.Interop.Excel


Sub getvalue()
  
 Dim con As New OleDb.OleDbConnection

        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\AiZho\Desktop\eLeapv1.3.2.2 (preset key)\eLeap\bin\Debug\eleap.mdb;Persist Security Info=True;Jet OLEDB:Database Password=@eLeap"

        con.Open()
        Dim ds As New DataSet

        'Extracting from database
        Dim sql, filename As String
        Dim col, row As Integer
        sql = "SELECT date from pluItemLog"
        Dim da As New OleDb.OleDbDataAdapter(sql, con)
    
            da.Fill(ds, "pluItemLog")
     
        Dim Excel As Object = CreateObject("Excel.Application")
       [u] Range[/u]("A6").value = 'value here
'getting blue underline at this range
  filename = "E:\PLU" & Format(Now(), "dd-MM-yyyy_hh-mm-ss") & ".xls"

    End Sub

[andrewsw]

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.

- this can only take you so far.

This post has been edited by andrewsw: 18 February 2013 - 07:00 AM

[chenaz]

HI,


so what should i do?could u pls advise? i'm still at learning stage.

or mayabe i code jus modify the old codes tat i have to archive wat i wanted?
by adding each and indivial records to each individual cell in excel?

 For row = 0 To ds.Tables(0).Rows.Count - 1

                     .Cells(i, k).Value = ds.Tables(0).Rows(row).ItemArray(col)

                     i += 1

                 Next

                 k += 1

             Next

This post has been edited by chenaz: 19 February 2013 - 03:40 AM

[chenaz]

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()