translate VB code into VB.net code

by **nonameher** » Sat, 21 Aug 2010 20:41:54

Private Sub Form_Load()
d = Now
d = Format(Now, "dd-mmm-yy")

PivRefFILE = "\\GLNSFS1\groups\POS\GWW_MMS_DATA" & "\Tasks\PivRefreshCheck\T_LOG" & Format(d, "yyyymmdd") & ".LOG"
Open PivRefFILE For Append As #1
Write #1, "Pivot refresh for " & d - 1 & ". was started at " & Now() & "."
Close #1
On Error Resume Next
Sql_Prod

Open PivRefFILE For Append As #1
Write #1, "Pivot refresh for " & d - 1 & " finished at " & Now() & "."
Write #1, "COMPLETED"
Close #1

End
End Sub

Public Sub OpenSpreadsheet(ByVal strDataDirectory As String)
Set xlApp = New Excel.Application ' Make sure excel is running
xlApp.Workbooks.Open strDataDirectory, , False, , , , True ' Load the work file (read only)
Set xlBook = xlApp.Workbooks(1) ' Get a pointer to the workbook
End Sub

Public Sub Sql_Prod()
Open PivRefFILE For Append As #1
Write #1, "Pivot refresh for prod was started at " & Now() & "."
Close #1
strDataDirectory = "\\GLNSFS\groups\POS\GWW_MMS_Data\CurrentProd\RMN_Current_prod\Prod\mmsprod_PIV_QUERYRMN.xls"
OpenSpreadsheet strDataDirectory
Set xlsheet = xlBook.Sheets(1)
xlsheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True
xlsheet.PivotTables("PivotTable1").PivotCache.Refresh
xlBook.Save
xlBook.Close
xlApp.Quit

End Sub

I am trying to translate this VB code into VB.net code

I am having some difficulty understand part of the code

I know this program is to go into Excel files and refresh the specific pivot tables and then save the file as a new file with a new name with the current date added into the orginal file name

however i have no idead what these lines does

Open PivRefFILE For Append As #1
Write #1, "Pivot refresh for " & d - 1 & ". was started at " & Now() & "."
Close #1
On Error Resume Next
Sql_Prod

Open PivRefFILE For Append As #1
Write #1, "Pivot refresh for " & d - 1 & " finished at " & Now() & "."
Write #1, "COMPLETED"
Close #1

any help will be appreciated

can someone reccomend a website that can aid me in this task

thanks

by **Stanley127** » Mon, 23 Aug 2010 21:42:55

They open up your file and append the text to the file.

Code Block

Open PivRefFILE For Append As #1 'Opens your file in append mode
Write #1, "Pivot refresh for " & d - 1 & ". was started at " & Now() & "." 'writes out the text to the file
Close #1 'Closes your file
On Error Resume Next 'if you get an error then keep moving (don't use in .NET)
Sql_Prod

Open PivRefFILE For Append As #1'Opens your file in append mode
Write #1, "Pivot refresh for " & d - 1 & " finished at " & Now() & "."'writes out the text to the file
Write #1, "COMPLETED"'writes out the text to the file
Close #1 'Closes your file

Try using this instead:

Code Block

Using sw As New StreamWriter(strFile, False)

sw.WriteLine("Pivot refresh for " & d - 1 & ". was started at " & Now() & ".")

End Using

Using sw As New StreamWriter(strFile, False)

sw.WriteLine("Pivot refresh for " & d - 1 & " finished at " & Now() & ".") 'writes out the text to the file
sw.WriteLine("COMPLETED")'writes out the text to the file

End Using

by **nonameher** » Tue, 24 Aug 2010 23:44:57

xlsheet.PivotTables("PivotTable1").PivotSelect("", xlDataAndLabel, True)

I am still having trouble with this line

i have no idea what it does

i dont see the xlDataAndLabel declared anywhere

can someone help

by **JohnWei** » Wed, 25 Aug 2010 22:43:56

Assuming you are using VB2005. Start a new Windows Application. Position the cursor above the End Class line.

Copy your VB6 code. On the Tools menu select Upgrade Visual Basic 6 code. Paste your copied code in the box and press Upgrade. Correct the warnings and errors. (Mostly change object declarations to type declarations.)

by **Alex Mour** » Fri, 27 Aug 2010 00:45:58

That should be an enum value, according to msdn - you're probably missing an import statement. Searching in the object browser for that name, it seems to be under Microsoft.Office.Interop.Excel (at least for version 12). If you can't find it (if your excel object model doesn't expose it for any reason, you can probably use the integer value for that option instead.)

Msdn page on the enumeration (XlPTSelectionMode Enumeration)

http://msdn2.microsoft.com/en-us/library/bb241533.aspx

by **nonameher** » Sun, 29 Aug 2010 01:46:59

oh wow, thanks..

how do u do a search like that to find exact what I was looking for