Reputation: 0 Apprentice
- Active Members
- Active Posts:
- 95 (0.08 per day)
- 31-March 12
- Profile Views:
- Last Active:
- May 28 2015 12:58 PM
- Dream Kudos:
Posts I've Made
Posted 22 May 2015Thank you so much modi123_1 it worked, i removed the where i copied it while testing things .
Thank you so much and thanks for the advise
Posted 22 May 2015Thank you, yes without where i put it by mistake i am trying million things i didnt work it gets discontinued ='1' too yes discontinued is a bit field
Posted 22 May 2015Thank you for your reply yes i know Select * selects all , but the discontinued field now is all '1' i run the query and it has where discontinued ='0' and it returns all discontinued field with value '1'
Posted 13 May 2015yea sure but i was continuing the discussion as you said to paste the full code.
Thank you anyways.
i will do the rest
Posted 13 May 2015Hi
If the column is not visible you are still incrementing the iC variable which explains why you are getting gaps in your columns. For example, if you have 3 columns and the second is hidden, the loop is saying:
Column Index 1 > is visible > write at column 1
Column Index 2 > is NOT visible > do not write
Column Index 3 > is visible > write at column 3
You need to use a different variable to keep track of what the real column index is that you will be writing to. Something like:
Dim currentColIndex As Integer = 1 For iC = 0 To colsTotal If DataGridView2.Columns(iC).Visible = True Then .Cells(1, currentColIndex).Value = DataGridView2.Columns(iC).HeaderText currentColIndex += 1 End If Next
On a slightly different note, please copy and paste all of the code that is relevant. You are missing some bits here such as the Catch statement and End Try and End Sub statements. If we copy as is, then we have to fix up the code to get it to compile and then start looking into it for you.
Thank you for your fast reply i tried your code the headers went good but below was inserting empty rows too please check the pic attached and here is the full code
Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click Dim rowsTotal, colsTotal As Short Dim I, j, iC As Short System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor Dim xlApp As New excel.Application Dim excelBook As excel.Workbook = xlApp.Workbooks.Add Dim excelWorksheet As excel.Worksheet = CType(excelBook.Worksheets(1), excel.Worksheet) excelWorksheet.Name = "Assets" Dim excelworksheet2 As excel.Worksheet = CType(excelBook.Worksheets(2), excel.Worksheet) Dim excelworksheet3 As excel.Worksheet = CType(excelBook.Worksheets(3), excel.Worksheet) excelworksheet2.Delete() excelworksheet3.Delete() xlApp.DisplayAlerts = False Try xlApp.Visible = False rowsTotal = DataGridView2.RowCount - 1 colsTotal = DataGridView2.Columns.Count - 1 With excelWorksheet .Cells.Select() .Cells.Delete() ''For iC = 0 To colsTotal '' If DataGridView2.Columns(iC).Visible = True Then '' .Cells(1, iC + 1).Value = DataGridView2.Columns(iC).HeaderText '' MsgBox(.Cells(1, iC + 1).Value) '' End If 'Next Dim currentColIndex As Integer = 1 For iC = 0 To colsTotal If DataGridView2.Columns(iC).Visible = True Then .Cells(1, currentColIndex).Value = DataGridView2.Columns(iC).HeaderText currentColIndex += 1 End If Next For I = 0 To rowsTotal For j = 0 To colsTotal If DataGridView2.Rows(I).Cells(j).Visible = True Then .Cells(I + 2, j + 1).value = DataGridView2.Rows(I).Cells(j).Value MsgBox(.Cells(I + 2, j + 1).value) End If Next j Next I .Rows("1:1").Font.FontStyle = "Bold" .Rows("1:1").Font.Size = 10 .Cells.Columns.AutoFit() .Cells.Select() .Cells.EntireColumn.AutoFit() .Cells(1, 1).Select() End With Dim saveFD As New SaveFileDialog saveFD.Filter = "Excel Workbook (*.xlsx)|*.xlsx" saveFD.OverwritePrompt = True saveFD.InitialDirectory = "C:\" saveFD.Title = "PLease Choose Filename and Destination" Dim DidWork1 As Integer = saveFD.ShowDialog() If DidWork1 = DialogResult.Cancel Then Exit Sub End If Dim savename As String savename = saveFD.FileName excelWorksheet.SaveAs(savename) Catch ex As Exception MsgBox("Export Excel Error " & ex.Message) Exit Sub Finally System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default excelBook.Close() releaseObject(excelBook) releaseObject(excelWorksheet) System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp) xlApp = Nothing Dim proc As System.Diagnostics.Process For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL") proc.Kill() Next End Try MsgBox("Assets Exported Successfully, Thank you", MsgBoxStyle.Information) End Sub
- Member Title:
- D.I.C Head
- Age Unknown
- Birthday Unknown