|
Hey All
I just wanted to get some feed back on my concept?
I quite literally spent days trying to work out why when I exported data to excel I could not no matter what, remove the instance of excel form the task manager…I tried every know method in every possible combination I could find on Google and ideas on these forums but it would not die
I didn’t want to use the known kill all excel instances loop as although an easy fix isn’t very user friendly as if the user has an instance of excel open, that will be close too and they probably wont like that too much…so I managed to come up with this solution which I hope or believe will finally solve the problem but has one perk to make it different
The code boasts that you can export data to excel…dispose of the excel instance in the task manager …BUT THEN … opens the file for the user to view… with the difference being, when the user now closes the file, the instance of excel is removed from the tack manager unlike if when closing an instance of excel created though automation which will stay in the task manager
vb.net 2005
<br />
Public Sub CreatExcel()<br />
Dim priorSum As Integer = 0<br />
Dim newSum As Integer = 0<br />
Dim xlProcID As Integer = 0<br />
For Each proc As Process In Process.GetProcessesByName("excel")<br />
priorSum += proc.Id<br />
Next proc<br />
Dim excelApp As Object = Nothing<br />
Dim excelBook As Object = Nothing<br />
Dim excelWorksheet As Object = Nothing<br />
Try<br />
excelApp = New Application<br />
excelBook = excelApp.Workbooks.Add<br />
excelWorksheet = CType(excelBook.Worksheets(1), Worksheet)<br />
excelApp.Visible = False<br />
For Each proc As Process In Process.GetProcessesByName("excel")<br />
newSum += proc.Id<br />
Next proc<br />
xlProcID = newSum - priorSum<br />
With excelWorksheet<br />
' Add/export some data to excel<br />
' Handy hint <br />
' This code will apply border ALL the way around a group of cells 'x,x' i.e. 'A1:A5'<br />
.Range(x, x).Borders(XlBordersIndex.xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous<br />
.Range(x, x).Borders(XlBordersIndex.xlEdgeLeft).Weight = XlBorderWeight.xlThin<br />
.Range(x, x).Borders(XlBordersIndex.xlEdgeLeft).ColorIndex = XlColorIndex.xlColorIndexAutomatic<br />
.Range(x, x).Borders(XlBordersIndex.xlEdgeRight).LineStyle = XlLineStyle.xlContinuous<br />
.Range(x, x).Borders(XlBordersIndex.xlEdgeRight).Weight = XlBorderWeight.xlThin<br />
.Range(x, x).Borders(XlBordersIndex.xlEdgeRight).ColorIndex = XlColorIndex.xlColorIndexAutomatic<br />
.Range(x, x).Borders(XlBordersIndex.xlEdgeTop).LineStyle = XlLineStyle.xlContinuous<br />
.Range(x, x).Borders(XlBordersIndex.xlEdgeTop).Weight = XlBorderWeight.xlThin<br />
.Range(x, x).Borders(XlBordersIndex.xlEdgeTop).ColorIndex = XlColorIndex.xlColorIndexAutomatic<br />
.Range(x, x).Borders(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous<br />
.Range(x, x).Borders(XlBordersIndex.xlEdgeBottom).Weight = XlBorderWeight.xlThin<br />
.Range(x, x).Borders(XlBordersIndex.xlEdgeBottom).ColorIndex = XlColorIndex.xlColorIndexAutomatic<br />
.Range(x, x).Borders(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous<br />
.Range(x, x).Borders(XlBordersIndex.xlInsideHorizontal).Weight = XlBorderWeight.xlThin<br />
.Range(x, x).Borders(XlBordersIndex.xlInsideHorizontal).ColorIndex = XlColorIndex.xlColorIndexAutomatic<br />
<br />
End With<br />
<br />
<br />
' Hany hint you can remove grid lines and name your sheets like so<br />
With excelApp<br />
.ActiveWindow.DisplayGridlines = False<br />
.ActiveSheet.name = "My Export"<br />
End With<br />
<br />
<br />
' Edit as required:<br />
excelApp.ActiveWorkbook.SaveAs("My File Location Path\My File Name.xls")<br />
<br />
' You must save the file here are some ideas for saving the file so as not to<br />
' have any issues with existing files that you previously exported that may have<br />
' the same file name as your new exported file which of cause would throw errors<br />
<br />
<br />
'Method 1 Unqueek File name when saving :=<br />
'You don’t have to use this but this is just my way of ensuring file names is never <br />
'the same as an old version export<br />
'###############<br />
Dim PublishDate As Date<br />
PublishDate = Now()<br />
Dim FormattedDateAsString As String<br />
FormattedDateAsString = Format(PublishDate, "F") & " " & Format(PublishDate, "(h:mtt)")<br />
Dim FixString As String = FormattedDateAsString<br />
Dim GetDate As Date = Format(PublishDate, "d")<br />
Dim TestString As String = GetDate.Day.ToString<br />
If TestString.EndsWith("1") And TestString <> "11" Then<br />
FormattedDateAsString = FixString.Insert(FixString.IndexOf(TestString) + 2, "st ")<br />
ElseIf TestString.EndsWith("3") And TestString <> "13" Then<br />
FormattedDateAsString = FixString.Insert(FixString.IndexOf(TestString) + 2, "rd ") 'FixString.Replace(TestString, (TestString & "rd"))<br />
Else<br />
FormattedDateAsString = FixString.Insert(FixString.IndexOf(TestString) + 2, "th ")<br />
End If<br />
TestString = GetDate.Year.ToString<br />
FormattedDateAsString = FormattedDateAsString.Replace(TestString, (TestString & " at"))<br />
FormattedDateAsString = FormattedDateAsString.Replace(":", ".")<br />
' Save the file<br />
excelApp.ActiveWorkbook.SaveAs("C:\My Report Created on - " & FormattedDateAsString & ".xls")<br />
'###############<br />
<br />
<br />
<br />
'Method 2 remove old file :=<br />
'You could also delete and replace old file if you rather<br />
'#############################<br />
If File.Exists("C:\My Report.xls") Then<br />
File.Delete("C:\My Report.xls")<br />
End If<br />
'#############################<br />
<br />
<br />
' You don’t have to have this but I do to be sure everything is saved <br />
For Each w In excelApp.Application.Workbooks<br />
'save all the work sheets<br />
w.Save()<br />
Next w<br />
' close the work sheets with out prompting...may not need this either<br />
excelApp.ActiveWorkbook.Close(SaveChanges:=False)<br />
<br />
<br />
'Now kill excel but only the excel instance we created and NOT any other excel applications running at the same time<br />
If Not excelApp Is Nothing Then<br />
GC.Collect()<br />
GC.WaitForPendingFinalizers()<br />
GC.Collect()<br />
GC.WaitForPendingFinalizers()<br />
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelWorksheet)<br />
excelWorksheet = Nothing<br />
If Not excelBook Is Nothing Then<br />
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelBook)<br />
End If<br />
excelApp.Quit()<br />
excelBook = Nothing<br />
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelApp)<br />
excelApp = Nothing<br />
Dim proc As Process = Process.GetProcessById(xlProcID)<br />
proc.Kill()<br />
End If<br />
<br />
' Now if you want you can open the excel file view. The advantage of this code is that<br />
' when the user closes the excel it wont get stuck in the system task manager<br />
Dim p As New System.Diagnostics.Process<br />
<br />
' Set this to the file location and file name of ythe excel you just exported<br />
' Example: "My File Location Path\My File Name.xls" or "C:\My Report.xls" or "C:\My Report Created on - " & FormattedDateAsString & ".xls"<br />
' Edit as required:<br />
p.StartInfo.FileName = "C:\My Report Created on - " & FormattedDateAsString & ".xls"<br />
p.StartInfo.WindowStyle = ProcessWindowStyle.Minimized<br />
p.Start()<br />
<br />
'Wait until the process passes back an exit code add if you want but your application <br />
'wont run untill the user closes the excel down <br />
'p.WaitForExit()<br />
<br />
' Free the recources ...may not need this line as will work with out but added to be save<br />
' Remove if you add the line above 'WaitForExit()'<br />
p = Nothing<br />
<br />
'Free resources associated with this process<br />
' add this if you add the line above 'WaitForExit()'<br />
' p.Close()<br />
<br />
<br />
Catch ex As Exception<br />
MsgBox(ex.Message & vbTab & ex.Source & vbTab & ex.HelpLink)<br />
End Try<br />
End Sub<br />
All comments welcome
|
|
|
|
|
I'm using crystal report and vb6. I posted a thread two days ago regarding changing the connection properties of the crystal report in vb6. I found a way by using : crxReport.Databases.SetDataSource rsReport
but I'm having an exception report : "The exception unknown software exception (0xe0434f4d) occurred in the application at location 0x77e55e02"
The report shows but after opening several reports the error message occurs.
This is my scenario:
"I'm using a udl for my connection. I have two databases namely TK and TK_MCC. TK is for other company and TK_MCC is for the subsidiary company. I used TK database when I created the crystal report. I used the setdatasource to pass the recordset to change the datasource location of the crystal report. So even if they change the database TK to TK_MCC they can still access the report. But when I did the setdatasource there was an exception report. (The exception unknown software exception (0xe0434f4d) occurred in the application at location 0x77e55e02)"
Before it was working fine when I don't have to change the database.
By the way, i tried it also in another computer (just installed the program) but the error message was different. The other error was error28 out of stack space.
Can someone help me on this...
Thank you very much.
|
|
|
|
|
I like to replicate keys typed inside a treeview control through to another window (say another textbox) without setting focus to the textbox first and then using vb's sendkeys function.
|
|
|
|
|
Use a delegate to pass values between windows.
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
|
I need away for people to clean the web by having them delete old .pdf's
I am adding a way for the user to sort, but when I did that I would get an error when I would delete a file.
Any thoughs, beside read more books. Thanks in advance
Sub articleList_ItemDataBound(ByVal sender As Object, ByVal e As DataGridItemEventArgs)
' First, make sure we're NOT dealing with a Header or Footer row
If e.Item.ItemType <> ListItemType.Header And _
e.Item.ItemType <> ListItemType.Footer Then
'Now, reference the Button control that the Delete ButtonColumn
'has been rendered to
Dim deleteButton As Button = e.Item.Cells(0).Controls(0)
'We can now add the onclick event handler
deleteButton.Attributes("onclick") = "javascript:return " & _
"confirm('Are you sure you want to delete the file " & _
DataBinder.Eval(e.Item.DataItem, "Name") & "?')"
End If
End Sub
Sub articleList_DeleteFile(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)
'First, get the filename to delete
Dim fileName As String = articleList.DataKeyField(e.Item.ItemIndex)
File.Delete(fileName)
'You would want to rebind the Directory's files to the DataGrid after
'deleting the file...
Dim dirInfo As New DirectoryInfo(Server.MapPath("./"))
articleList.DataSource = dirInfo.GetFiles("*.pdf")
articleList.DataBind()
End Sub
-- modified at 18:50 Wednesday 7th November, 2007
|
|
|
|
|
Please don't post all your code. Just the trouble spot in your code ( or at least where you suspect it is not working ).
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Hi there,
I would like to change the hideselection color from the treeview. I have set it to false, and i've noticed that the color is equal to the systemcolor control.
Is it possible to change this color to for example Red?
Thanks
|
|
|
|
|
You can't change TreeView.HideSelection Color directly.
But to change HideSelection color here is an another solution
1) TreeView.HideSelection = true
2) Declare:
Public previousSeletedNode As TreeNode 'To hold reference to previously selected TreeNode
3)
Private Sub TreeView1_Validating(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles TreeView1.Validating
TreeView1.SelectedNode.BackColor = Color.Yellow
TreeView1.SelectedNode.ForeColor = Color.White
previousSeletedNode = TreeView1.SelectedNode
End Sub
4)
Private Sub TreeView1_AfterSelect(ByVal sender As System.Object, ByVal e As System.Windows.Forms.TreeViewEventArgs) Handles TreeView1.AfterSelect
If Not previousSeletedNode Is Nothing Then
previousSeletedNode.BackColor = TreeView1.BackColor
previousSeletedNode.ForeColor = TreeView1.ForeColor
End If
End Sub
|
|
|
|
|
that works great! Thanks.
|
|
|
|
|
Can anyone please let me know how to convert the data type of a field under Crystal Reports I'm using this code in the formula editor but gives me an error 'A Field is Required'.
sum (ToNumber({A String Field}))
I know it takes only Numeric data type fields but is there any way to first convert the string into a Number then calculate its Sum?
Thanks in Advance !
AliAmjad(MCP)
First make it Run THEN make it Run Fast.
|
|
|
|
|
Is it possible in VB to open up a word doc/template replace some certain words with information read in from a csv file and then print this doc/template ? I suppose in effect it is a simple mail merge.
I've written the bit to open the csv file and read the data in but am a bit stumped as to how to open a word doc and replace some words and then print.
Any help gratefully appreciated.
|
|
|
|
|
With the Microsoft Interop Library found with MS Office, I think it is possible...
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Is anyone able to help with a snippet of code as to how to find a word in a word document and then replace with required text ?
I've tried the code below but the items highlighted are not declared, I've tried various lines of code for the declaration but cannot seem to get it right.
With Selection.Find
.ClearFormatting
.Text = "hi"
.Replacement.ClearFormatting
.Replacement.Text = "hello"
.Execute Replace:=wdReplaceAll, Forward:=True, _
Wrap:=wdFindContinue
End With
Any help would be gratefully appreciated
|
|
|
|
|
Found a solution that works for finding text and replacing it in word.doc, snippet below
Imports Microsoft.Office.Interop
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim axword As Microsoft.Office.Interop.Word.Application
Dim axdoc As Word.Document
axword = New Word.Application
axdoc = axword.Documents.Open("c:\quote.dotx")
axword.Visible = True
axdoc.Activate()
axword.Selection.Find.ClearFormatting()
axword.Selection.Find.Text = "~{CUSTOMERNAME}"
axword.Selection.Find.Execute()
axword.Selection.Select()
axword.Selection.TypeText("Joe Bloggs")
|
|
|
|
|
how do i add columns to my datagrid and give them a name
|
|
|
|
|
Use the myDataGrid.Columns.Add method
Steve Jowett
-------------------------
Sometimes a man who deserves to be looked down upon because he is a fool, is only despised only because he is an 'I.T. Consultant'
|
|
|
|
|
ok i got my datagrid set up and its loading text files fine now when i edit the text files in the datagrid i need to know how to save the changed textfile.
|
|
|
|
|
Dear All,
I use VB.NET 2003 and MS Access 2003.
After executing the program, I have enclosed coed to compact the database and close it. Those code are as follows,
Sub Main()
Try
Dim File_Path, compact_file As String
'Original file path that u want to compact
File_Path = AppDomain.CurrentDomain.BaseDirectory & "db.mdb"
'compact file path, a temp file
compact_file = AppDomain.CurrentDomain.BaseDirectory & "db1.mdb"
'First check the file u want to compact exists or not
If File.Exists(File_Path) Then
Dim db As New DAO.DBEngine
'CompactDatabase has two parameters, creates a copy of compact DB at the Destination path
db.CompactDatabase(File_Path, compact_file)
End If
'restore the original file from the compacted file
If File.Exists(compact_file) Then
File.Delete(File_Path)
File.Move(compact_file, File_Path)
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
This was working fine, but after i created crystal report to display my results, those codes are not working. An error displays at,
db.CompactDatabase(File_Path, compact_file)
An error message as follows,
Cannot open database "Path/db.mdb'. It may not be a database that your application recognizes, or the file may be currupt.
If i execute program without displaing crystal report, it is OK. The problem arise only after run crystal reports.
Please help me.
Thanks
Dimuthu
|
|
|
|
|
Is crystal reports trying to run before the compacting of the Database is complete?
Steve Jowett
-------------------------
Sometimes a man who deserves to be looked down upon because he is a fool, is only despised only because he is an 'I.T. Consultant'
|
|
|
|
|
Dear Steve,
Yes, the database is compacting when the program is exiting. Compacting codes are placed in main Exit Menu.
Thanks
Dimuthu
|
|
|
|
|
Hi,
I'd like to display a form with an animated graphic whilst some information in a large dataset is being saved to a file. Naturally, once the file is saved the form should close. Can anyone point me in the right direction please.
thanks in advance
|
|
|
|
|
You what something like this :-
Dim f as WorkingForm<br />
f.show<br />
'Do something here<br />
f.close<br />
f.dispose<br />
f = nothing
Ensure the TopMost property of the WorkingForm is set to True.
Steve Jowett
-------------------------
Sometimes a man who deserves to be looked down upon because he is a fool, is only despised only because he is an 'I.T. Consultant'
|
|
|
|
|
I'm not shure but this might have the problem that the animation isn't going to go to well/smooth
perhaps multithreading would solve that problem
at the end of the thread that save the file fire an event that closes the form?
If my help was helpfull let me know, if not let me know why.
The only way we learn is by making mistakes.
|
|
|
|
|
I have a third party executable that I run, which writes its results to a text file. Unfortunately, it writes only to a single file, overwriting itself each time, and locking the file as it writes. As a result, if I run four copies of this simultaneously, for example, only one of those copies will sucessfully write to the log file.
Is there some way I could launch the file and intercept its file output operations (bearing in mind I have no knowledge of how it's doing that output), redirecting them to a unique instance of the log file each time it runs?
Thanks for any input, and do let me know if the above doesn't clearly explain what it is I am trying to do.
|
|
|
|