Kan någon hjälpa mig med det här ! jag försöker att via ASP.net skapa en kopia av en excelfil på server och sedan fylla kopian med data .filen ska sedan skickas till klienten och där efter tasbort på servern.

Koden jag använder är den under.

Problemet är att den funkar tup 1 gång av 10.

Kan någon hjälpa mig med hur jag ska sriva den för att det ska fungera !


<code>
Private Sub ExportToExcel(ByVal unit As String)

Dim ExcelWasNotRunning As Boolean
Dim ea As Excel.Application
Dim wb As Excel.Workbook
Dim ws_A As Excel.Worksheet
Dim ws_B As Excel.Worksheet
Dim Ws_N As Excel.Worksheet
Dim Ws_S As Excel.Worksheet
Dim r As Excel.Range

System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")

On Error Resume Next
ea = GetObject(, "Excel.Application")
If Err().Number <> 0 Then ExcelWasNotRunning = True

Err().Clear() ' Clear Err object in case error occurred.

If (ExcelWasNotRunning) Then
ea = CreateObject("Excel.Application")
Else
ea = GetObject("Excel.Application", "Excel.Application")
End If



wb = ea.Workbooks.Open(Server.MapPath("\Nekudabamall\Nekudabamall.xls"))


Dim FileNR, filname As String
Dim upperbound, lowerbound As Integer
upperbound = 1000
lowerbound = 4
FileNR = CStr(Int((upperbound - lowerbound + 1) * Rnd() + lowerbound)) ' Generate random value between 1 and 1000.

filname = "Nyckeltal" & FileNR & ".xls"
wb.SaveAs(Server.MapPath("\Nekudabamall\" & filname))



wb.Close()


'If Not File.Exists(Server.MapPath("\Nekudabamall\" & filname)) Then
'MsgBox("filname " & filname & " finns inte")
' End If



wb = ea.Workbooks.Open(Server.MapPath("\Nekudabamall\" & filname))



ws_A = wb.Worksheets
ws_B = wb.Worksheets
Ws_S = wb.Worksheets
Ws_N = wb.Worksheets


Ws_N = wb.Worksheets("Nyckeltal")

r = Ws_N.Range("A4:A5")
r(1, 4) = Dpd_Unit.SelectedItem.Text
r(1, 5) = Txt_Ansvarig.Text

r = Nothing

r = Ws_N.Range("A8:A8")
r(1, 4) = SetBusinessRatio(CInt(unit), "Prospect-A")
r(2, 4) = SetBusinessRatio(CInt(unit), "Prospect-B")
r(3, 4) = SetBusinessRatio(CInt(unit), "Suspect")
r(7, 4) = SetProspectValue(CInt(unit))
r(8, 4) = SetWeighProspectValue(CInt(unit))
r(10, 4) = SetAvgTerminateDate(CInt(unit), "Prospect-A")
r(11, 4) = SetAvgTerminateDate(CInt(unit), "Prospect-B")



r = Nothing


ws_A = wb.Worksheets("A-Prospects")

r = ws_A.Range("A8:I8")
r(1, 1) = 1111111
r(1, 2) = "2002-01-01"
r(1, 3) = "Saab Aviocomp"
r(1, 4) = "Brandväggsjobb"
r(1, 5) = "Tobias Wester"
r(1, 6) = "NX_Test"
r(1, 7) = 1111111
r(1, 8) = "2002-01-01"

r = Nothing

ws_B = wb.Worksheets("B-Prospects")

r = ws_B.Range("A8:I8")
r(1, 1) = 22222222
r(1, 2) = "2002-02-02"
r(1, 3) = "Saab Aviocomp"
r(1, 4) = "Brandväggsjobb"
r(1, 5) = "Anders ahlström"
r(1, 6) = "NX_Test"
r(1, 7) = 22222222
r(1, 8) = "2002-02-02"

r = Nothing

Ws_S = wb.Worksheets("Suspect")

r = Ws_S.Range("A8:I8")
r(1, 1) = 22222222
r(1, 2) = "2002-02-02"
r(1, 3) = "Saab Aviocomp"
r(1, 4) = "Brandväggsjobb"
r(1, 5) = "Anders ahlström"
r(1, 6) = "NX_Test"
r(1, 7) = 22222222
r(1, 8) = "2002-02-02"


wb.Save()



wb = Nothing
Ws_N = Nothing
ws_A = Nothing
ws_B = Nothing
Ws_S = Nothing
r = Nothing

ea.Quit()
ea = Nothing
GC.Collect()



fileCopy("Nyckeltal.xls", filname)



DeleteFile(filname)

End Sub




Private Sub DeleteFile(ByVal FileName As String)
System.IO.File.Delete(Server.MapPath("\Nekudabamall\" & FileName))
End Sub




Private Sub fileCopy(ByVal SaveAsfilname As String, ByVal filname As String)
' clear the current output content from the buffer
Response.Clear()

' add the header that specifies the default filename for the

'Download/SaveAs dialog
Response.AddHeader("Content-Disposition", "filename=" & SaveAsfilname)

Response.AddHeader("Content-Length", filname.Length.ToString())


' specify that the response is a stream that cannot be read by the
' client and must be downloaded
Response.ContentType = "application/octet-stream"
' send the file stream to the client
Response.WriteFile(Server.MapPath("\Nekudabamall\" & filname))

Response.Flush()


Response.Close()





' send all bufferd data to cliente


End Sub
</code>

Vad händer när det inte fungerar? Felmeddelande?

/johan/

Det vanligaste problemet är att det inte skapas någon ny fil på servern....

Och då faller ju hela lösningen ;-)

Men ibland när det väl har skapats en fil så vill den inte försvinna när delete suben körs !

Dessutom dom gånger allt funkar utmärkt så blir filen som är skickad till klienten readonley.......... ;-(

Hur är skrivrättigheterna?