Using Calendar in VB 08

[schnabs]

Hello. I have a code that runs through a DIR of Excel sheets and updates them. What I am looking to do, is before it refreshes my Excel query, I want to insert a date on the current workbook. But I want a calendar to pop up and the user to select the date. My code is as follows:

Do While temp <> ""
oxlbook = oxl.Workbooks.Open("Z:\Quickbooks Properties\Reports\Delinquency Summary\" + temp)
oxl.Visible = True
oxl.ActiveSheet.pagesetup.centerheadear = "&""Arial,Bold""&12&F" & Chr(10) & Calendar.click
oxl.Run("'Z:\DelinquencySpreadsheet.xls'!SummaryHeader")
oxl.ActiveSheet.querytables(1).refresh()
oxl.ActiveSheet.querytables(2).refresh()
oxl.Columns.AutoFit()
oxl.ActiveWorkbook.SaveAs("Z:\2009 Combined Delinquencies\Summary\" + temp)
oxl.ActiveWorkbook.Close()
temp = Dir()

Loop

Where I update the header, I want the second part to pull the date from the calendar that the user selected. However, I only want the selection to occur once. My loop goes through 75 Excel files, and I only want the date selected once.
Thanks!

 

[Andrzejek]

If you ask for a date from your Calendar before your loop....

Have fun.

---- Andy

 

[schnabs]

Well yes I figured as much, I guess where I'm having trouble is the code required to pull the date from the calendar. I switched to try using the datetimepicker, but still the code is holding me up a bit.

 

[RiverGuy]

Did you use the DataTimePicker.Value property?

 

[schnabs]

Well here is my entire block of code.

Public Sub Summary_click() Handles summary.Click
Dim usercontrol1 As New UserControl1
Dim temp As String
Dim oxl As Microsoft.Office.Interop.Excel.Application
Dim oxlbook As Microsoft.Office.Interop.Excel.Workbook
temp = Dir("Z:\Quickbooks Properties\Reports\Delinquency Summary\*.xls")
oxl = New Microsoft.Office.Interop.Excel.Application()
oxl.DisplayAlerts = False
Dim dated As String
dated = "&""Arial,Bold""&12&F" & Chr(10) & usercontrol1.DateTimePicker1.Value.Date

Do While temp <> ""
oxlbook = oxl.Workbooks.Open("Z:\Quickbooks Properties\Reports\Delinquency Summary\" + temp)
oxl.Visible = True
oxl.ActiveSheet.pagesetup.centerheader = dated
oxl.Run("'Z:\DelinquencySpreadsheet.xls'!SummaryHeader")
oxl.ActiveSheet.querytables(1).refresh()
oxl.ActiveSheet.querytables(2).refresh()
oxl.Columns.AutoFit()
oxl.ActiveWorkbook.SaveAs("Z:\2009 Combined Delinquencies\Summary\" + temp)
oxl.ActiveWorkbook.Close()
temp = Dir()

Loop
oxl.DisplayAlerts = True
oxl.Quit()
End Sub

I may be using the code completely wrong, but nothing pops up for the user to select the date when I run that code.

 

[RiverGuy]

I'm not sure how you have your user control set up. My guess is, not correctly. Run this sample code on a test form. It pops up a date time picker and displays the date for you when it's closed. Feel free to refactor the code and integrate it into your project

        Dim fpicker As New Form
        Dim dt As New DateTimePicker
        fpicker.Controls.Add(dt)
        fpicker.StartPosition = FormStartPosition.CenterScreen
        fpicker.ClientSize = dt.Size
        fpicker.MinimizeBox = False
        fpicker.MaximizeBox = False
        fpicker.MaximumSize = fpicker.Size
        fpicker.MinimumSize = fpicker.Size
        fpicker.ShowDialog()
        Dim dateneeded As DateTime = dt.Value.Date
        fpicker.Close()
        fpicker.Dispose()
        fpicker = Nothing
        dt.Dispose()
        dt = Nothing

        MessageBox.Show(dateneeded.ToString)

 

[schnabs]

Awesome, thanks River. Only one last question. And this is kind of a newb question. How can I store the date selected as a string, then use it in my code?

 

[RiverGuy]

In the code above, I stored it in a DateTime variable (see the dateneeded variable). To utilize a string, you could do the following instead:

im dateneeded As String = dt.Value.Date.ToString()

 

[schnabs]

One last question. How would I use that string to be placed in my excel file in another sub? Since dateneeded is declared in the separate sub, I do not know how to refer to it from my query sub.