Click here to Skip to main content
15,886,578 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
i try to fetch values and save in excel file
Posted
Updated 26-May-16 1:37am
v10
Comments
CHill60 25-May-16 5:58am    
We need far more information that this in order to help you.
What have you tried? What is the problem?
super_user 25-May-16 6:35am    
please check update question
Richard MacCutchan 25-May-16 6:40am    
You have not declared or initialised the variable oSheet.
super_user 25-May-16 6:41am    
i already declared ...
Dim worksheet As Excel.Worksheet
Dim workbook As Excel.Workbook
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim s1 As String = ""
F. Xaver 25-May-16 7:18am    
but not initialised ...
Set oSheet = oBook.Worksheets(1)
for example

1 solution

You are going about this a very strange way.
VB
Dim s1 As String = "" 
...
For Each p1 As PointLatLng In lLines3
    s1 += p1.Lat + "," + p1.Lng
Next

Strings are immutable. In other words they cannot be changed. You are actually getting a brand new string for every iteration of that loop - not very efficient. If you are going to build strings up then use the StringBuilder Class[^]
So now you have a comma-separated list of some values, which you use a a format string to get data from the clipboard?
VB
Clipboard.GetData(s1)
I don't think you really mean to do that. Perhaps you are trying to put s1 into the clipboard...
VB
Clipboard.SetText(s1)
Which leads me to your next problem
oBook.Worksheets(1).Paste()

I suspect that you think you are going to get the values from above in columns A,B,C,D,E,F... etc. You are not. You will get a comma-separated list of values as a string in Cell A1. (Well that's what you will get if you fix the Clipboard problem above, otherwise you will get whatever, if anything, you last copied into the Clipboard).

You then attempt to insert the same data into the same cell (again!) ...
VB
oSheet.Range("A1").Value = s1
But you have not initialised oSheet. You need to give it a value before you can use it e.g.
VB
oSheet = oBook.Worksheets(1)

The problems are not so obvious with the next two lines
VB.NET
oBook.SaveAs("C:\Book1.xls")
In most systems you have to have administrator rights to be able to write to the root of the C: drive. You also don't want the root folder to become too cluttered ... stick the file into a folder - e.g. C:\Temp.
You should also give it a sensible name rather than using the Excel default name - you are less likely to overwrite a file you might have wanted to keep and it makes life easier if you need to tidy up after yourself.
oExcel.Quit()

There isn't a problem per se with just quitting excel like this but occasionally you might get an error suggesting that Excel is having problems closing down. Try closing the book first to overcome that
oBook.Close()


The code that follows is how I would have done this except that I haven't bothered with any error handling, checking if the file already exists, safety checks on values etc etc.
VB
Dim oExcel As Excel.Application = New Excel.Application()
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

oBook = oExcel.Workbooks.Add
If (oBook.Worksheets.Count = 0) Then
    oBook.Worksheets.Add()
End If

oSheet = oBook.Worksheets(1)

Dim i As Integer = 0 'NB 0 (zero) is correct, this should not be 1
For Each p1 As PointLatLng In lLines3
    oSheet.Range("A1").Offset(0, i).Value2 = p1.lat
    oSheet.Range("A1").Offset(0, i + 1).Value2 = p1.lng
    i += 2
Next

oBook.SaveAs("C:\Temp\PointLatLng.xls")
oBook.Close()
oExcel.Quit()
 
Share this answer
 
Comments
Matt T Heffron 25-May-16 15:53pm    
+5
super_user 26-May-16 0:39am    
i try this but file not save
Matt T Heffron 26-May-16 12:28pm    
This was entered as a reply to my comment, not to @CHill60 who posted the solution.
Be sure to respond to the correct message; that way the appropriate person will be notified of your response.
Use the "Have a Question or Comment?" button to respond directly to the Solution.
Use the "Reply" button to respond to a specific Comment.
CHill60 27-May-16 10:44am    
Do you actually have a folder called c:\Temp? It worked fine when I tested it

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900