Papa_Don 31 Posting Pro in Training

Group,

I can't seem to append multiple files into one text file. Unfortunately it's creating the first file but is not creating or appending the additional files into the first file. My code looks like this:

                getRestranName = System.IO.Directory.GetFiles(folderName)
                Dim counter As Integer = My.Computer.FileSystem.GetFiles(folderName).Count

                fileCount = Convert.ToInt32(counter)
                fileCount = fileCount - 1
                hotelFolder = CStr(getFolderNameGD("O:\Revenue Management\Centralized Revenue Management Service\CRMS Hotels", propertyNo2 & "*"))

                ' Begin by setting the loop to read each file in folder
                For i = 0 To fileCount
                    fileName = ""
                    Dim objReader1 As New System.IO.StreamReader(getRestranName(i))
                    fileName = getRestranName(i)
                    If System.IO.File.Exists(fileName) Then
                        Do While objReader1.Peek() <> -1
                            txtLine = objReader1.ReadLine()
                            programName = Trim(Microsoft.VisualBasic.Left(txtLine, 14))

                            If lineNo = 3 And programName = "(res.restran)" Then
                                restranSave = "C:\Restran Conversion\Restran\" & propertyNo & "restran.txt"
                                If i = 0 Then      'This reads the first file in the folder.
                                    lblProgName.Text = "Copying " & fileName & "......"
                                    System.IO.File.Copy(fileName, restranSave, True)
                                    objReader1.Dispose()
                                    objReader1.Close()
                                    lblProgName.Text = "Saved temp file " & restranSave & "......"
                                    System.IO.File.Delete(fileName)
                                Else    'This should read any the second through the last file and append to the first (called "restranSave")
                                    System.IO.File.AppendAllText(restranSave, fileName)
                                    objReader1.Dispose()
                                    objReader1.Close()
                                    System.IO.File.Delete(fileName)
                                End If
                            End If

                            lineNo = lineNo + 1
                            If lineNo = 4 Or lineNo > 4 Then
                                lineNo = 1
                                Exit Do
                            End If

                     Loop    'Loop to read the next line of the "i" file

                    End If

                Next i            'Takes you to the next file in the same folder 

            End If

Does it appear I'm missing something? The first file gets read …

Papa_Don 31 Posting Pro in Training

Minamalist, I did as you've suggest (or I think I did). Is this what you were suggesting (see the picture attached)? Capture.PNG

Unfortunately I don't have a dropdown that allows me to publish directly from here. I've see and both a "Publishing Folder" and an "Installation Folder URL" in the Publish tab. Is there something I should be setting different there (see the attached 2nd picture)? Restran_Solutions.PNG

Papa_Don 31 Posting Pro in Training

Minimalist, the program is installed. It is listed in the Programs and Features listing with all the installed applications.

I am trying to run this through the Task Manager. The shortcut lists the path as "C:\Users\donacwil\AppData\Roaming\Microsoft\Windows\Start Menu\Programs\Starwood Hotels and Resorts". When I go to that spot, the application is another shortcut. The path on that indicates the same place.

(ddanbe) Did you set your solution configuration from debug to release before publishing? I'm not sure how to answer this as this is the first app I've built. See the attached for the settings.
Restran.PNG

Papa_Don 31 Posting Pro in Training

Group,

The "Release" folder inside the "bin" folder is empty. I've attached a picture of the search results for the "One Click Application". I have multiple including the shortcut that is on my desktop. I've not been able to get the shortcut to run via the Task Scheduler (it will work by manually clicking it). Could any of the pictured be the actual .exe file?

Restran_Solutions.PNG

As always, thanks for the help.

Don

Papa_Don 31 Posting Pro in Training

Group,

I've created and published a VB.net program. I've also installed this onto my computer. I want to set it up now to run via the Window Task Scheduler. However I need to locate the executable file to start the run. However I'm unable to find it using the name of my program. Doing a search via Windows Explorer returns the files used to develop the program. Once I installed it I assumed I'd find a folder and ".exe" file in the "Program Files" or the "Program Files (x86)" folders. But it's not there. Is there a secret to finding this? How or where should I look?

As always, thanks for your assistance.

Don

Papa_Don 31 Posting Pro in Training

Group,

I've figured it out: Since Outlook is using HTML, I needed to wrap my information within the HTML syntax. Here's what it now looks like:

`.HTMLBody = "<p>Text above Excel cells" & "<br><br>" & _
                RangetoHTML(rng) & "<br><br>" & _
                "Text below Excel cells.</p>"`

Don

Papa_Don 31 Posting Pro in Training

Hi group,

I'm trying to insert both a copied range (from and Excel file) AND a message (text) into the body of an Outlook email. I've perfected the code to insert the range, but I've not figured out how to get the message to go along with it. I hope you can help. Here's my code I've written:

Sub Mail_Selection_Range_Outlook_Body()

Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object

Set rng = Nothing
' Only send the visible cells in the selection.

Set rng = Sheets("Listing").Range("A1:R" & lEndRow).SpecialCells(xlCellTypeVisible)

If rng Is Nothing Then
    MsgBox "The selection is not a range or the sheet is protected. " & _
           vbNewLine & "Please correct and try again.", vbOKOnly
    Exit Sub
End If

With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)


With OutMail
    .To = emailAdd
    .CC = ""
    .BCC = ""
    .Subject = "This is the Subject line / " & propID
'    .Body = "This is a Test Message." & vbNewLine
    .HTMLBody = RangetoHTML(rng)
    ' In place of the following statement, you can use ".Display" to
    ' display the e-mail message.
    .Send
End With
On Error GoTo 0

With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub


Function RangetoHTML(rng As Range)

    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook

    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & …
Papa_Don 31 Posting Pro in Training

Rev. Jim,

Truthfully, I thought about that as I saw the suggestion somewhere else. But I thought about trying to learn the most efficient way to show and hide the multiple textboxes and labels I'm using.

Thanks everyone for the suggestions. Now to give these a try.

Don

Papa_Don 31 Posting Pro in Training

Minimalist, The "condition" to show the textboxes and hide the labels come via a the event

Private Sub btnEditPropList_Click(sender As System.Object, e As System.EventArgs) Handles btnEditPropList.Click
lblProperty01.Visible = False
tbxProperty01.Visible = True
lblProperty02.Visible = False
tbxProperty02.Visible = True
End Sub

Of course, I'm lising all 400 lines of code to make textboxes visible and labels hidend. Once the edit is done, I then run

Private Sub btnSaveList_Click(sender As System.Object, e As System.EventArgs) Handles btnSaveList.Click
' writing information to file
lblProperty01.Visible = True
tbxProperty01.Visible = False
lblProperty02.Visible = True
tbxProperty02.Visible = False
End Sub

Which hides the textboxes and shows the labels.

I'm trying to understand the code you're writing. However I do not have a textbox called "TextBox1".
Dim condi As String = TextBox1.Text
Should I replace this with Dim condi As String = tbxProperty01.Text? I would have assumed that you'd create a loop or to say something to the effect of
Dim condi As String
For i = 1 to 200
If i < 10 Then
pNo = '0' & i
Else pNo = i
End If
condi = tbxProperty(pNo).Text (or something to this effect. I know writing it that way won't work. I'm not sure how to do it correctly - assuming it can be done).

I've not seen
For Each Cont As Label In Me.Controls.OfType(Of Label)()
before, so I don't know what this means or does. Since it begins with "For", I gather it …

Papa_Don 31 Posting Pro in Training

Group,

I've expanded a small app that started with 50 textboxes and labels to 200 of each. To hide and make these visable I use the codes

lblProperty01.Visible = False
tbxProperty01.Visible = True
lblProperty02.Visible = False
tbxProperty02.Visible = True

This means there are 400 lines of code to hide and display the 200 textboxes and labels. I've got to believe there is a more efficient way to write this..... and one that will hide and display these controls much faster. Can you advise me as to what that might be?

As always, thanks for your assistance!

Don

Papa_Don 31 Posting Pro in Training

Group,

Rev. Jim made a very good statement that I think I'm going to take: "You want to declare a variable number of variables dynamically which, in most cases, is just bad programming."

As a fairly new coder, I don't want to learn bad habits. So I'm going to abandon this idea and move on to more important things.

Group, as always I appreciate your help, input and above all, the good advice. You guys are the best!

Thanks again,

Don

Papa_Don 31 Posting Pro in Training

Rev. Jim,

I may be misstating what I need to do. I know my phraseology may not be correct.

In my earlier version of my application I defined (Dim) a variable as a string. These variables are named "prop1, prop2, prop3" and so on through "prop50". Later in the code I use StreamReader to read a text file to retrieve the actual property numbers and store those numbers in the above variables.

I'm now having to grow this property list. I need for there to be a minimum of 120 (and rapidly growing) variables for the 120 different properties that I need to run some routines for.

Since I need so many variables, I'd like to create these variable names programatically. So instead of doing individual Dim statements for every variable, I'd prefer just a few lines of code that will create these for me based on the number of individual properties there are in the text file. Today there are 118. But in the next few weeks, there will be 124. A month later there will be 128.

So I'm trying to find the correct syntax (if this actually exists) to programatically create the variable "prop1" and then read the text file for the first line to get the first property and then store that property number in "prop1". Then via a loop and a counter, the next variable to be programatically created will be "prop2". Then the text file will be read to get line 2 and it's …

Papa_Don 31 Posting Pro in Training

Minimalist, I believe I'm understand what you're doing here. It looks like

           Dim idList As New List(Of String)
            idList.Add("Props" & i.ToString)

is creating the variable on the fly. Now I need to get the property number from

         txtProps = objReader.ReadLine()

into this newly created variable. I tried

         idList.Add("Props" & i.ToString) = txtProps

and

        idList.Item(i) = txtProps

but it doesn't like either of these at all. So how do I insert the read text (txtProp) into this newly created variable? I'm not going to be able to put these into a textbox. In fact, I'm doing away with the textboxes altogether.

Again, thanks for the ideas. I'm thinking this is exactly what I need.

Don

Papa_Don 31 Posting Pro in Training

Group,

10 months ago I posed the same question as to how to programatically create the variable names via the loop. However I'm now in real need of doing this and I don't fully understand what each step of the syntax actually means and what it does. Therefore I need your help. Here is that original post: [https://www.daniweb.com/programming/software-development/threads/489038/loop-read-text-line-by-line-into-multiple-textboxes]

Here's what I currently have:

    Dim txtProps As String
    Dim i As Integer
    txtProps = ""
    Dim objReader As New System.IO.StreamReader(propFileName)
    i = 1
    If System.IO.File.Exists(propFileName) = True Then
        Do While objReader.Peek() <> -1
            txtProps = objReader.ReadLine()
            If txtProps = "" Then
                Exit Do
            End If
            If i = 1 Then
                prop01 = txtProps
            End If
            If i = 2 Then
                prop02 = txtProps
            End If
            If i = 3 Then
                prop03 = txtProps
            i = i + 1
            Loop

The reality is when the original post was written, I only needed 20 variable to put my property numbers in. But I'm rewriting this to allow all of our properties to be udated at one time. This means I need to have 120 variables. The common denominator is the prefix of the variable (prop). But I will need to name them "prop01", "prop02", etc. up through "prop120".

In Reverend Jim's reply he writes the following code:

      Dim data() As String = {"box1", "box2", "box3"}
      For i = 0 To 2
      Me.Controls("textBox" & (i + 1)).Text = data(i)
     Next

What I'm not understanding is, the "Dim data() …

Papa_Don 31 Posting Pro in Training

Hi group,

I'm modifying a VB.net app where I need to loop through an Excel spreadsheet for data out of two column. In essence the code will need to read:

For i = 2 to end of file
    propNo = Cells(i, 2).Value
    crms = Cells(i, 8).Value
    If crms = "Y" Then
        ' Go run the routine
    End If
Next i

Unfortunately I have no idea how to get this information from the spreadsheet. I did find a link here that may explain it, but I'm not sure I fully grasp it and it seems rather long and complicated. I know how to read a text file. But since my info is actually in a Excel file, I need to read it.

Here's the link I was referring to: [(https://www.daniweb.com/programming/software-development/code/474907/how-to-read-an-excel-spreadsheet-using-adodb)]

Can you help?

As always, thanks for your assistance.

Don

Papa_Don 31 Posting Pro in Training

Awesome! Thank you, Teme64!

Papa_Don 31 Posting Pro in Training

Hi group,

I'm starting a test to see if I can automate some tasks. In this first test, I'm using System.Diagnostics to open a specific version (I actually have two versions on my computer - both are needed). After that's open, I want to open a specific file within that instance of the application. However I've not figured out how to do this. I've only completed this much so far:

        Dim myFile As New System.Diagnostics.Process

    With myFile
        .StartInfo.WorkingDirectory = "C:\Program Files (x86)\Tableau\Tableau 8.3\bin"
        .StartInfo.FileName = "tableau.exe"
    End With
    myFile.Start()

Now I need to open the following filepath/filename:

O:\Revenue Management\RM -- Specialty Select Brands\Analytics\Tableau Extracts\Pace\SSB_FR_FUTURE_PACE_LOC.twbx

How do I go about doing this?

I did try entering the filepath as ".StartInfo.WorkingDirectory" and the filename as ".StartInfo.FileName". This "worked" except that it opened it file in the most recent version of the Tableau program (which in this instance I cannot do).

I look forward to your thoughts and ideas.

Thanks again!

Don

Papa_Don 31 Posting Pro in Training

Awesome.... Absolutely awesome. Thank you, Professor Jim..... err... I mean Rev. Jim...

I'll use the array as well. I need to learn to do that anyway.

thanks for the help!

Don

Papa_Don 31 Posting Pro in Training

If you really want to try it, here is the info you don't see in the text file:

"propertyNo" is used to find the file and save the file. "392" happens to be the property number and therefore the folder name for the property.

"bookingDate" is on the text file and is found on line 2. It's listed as "29-JUL-2015".

"saveRestran" is the path to find the text file to read.

"newRestranOnly" is a variable to decide what part of the text file to capture and write to the Excel file. It actually links to a checkbox, so the varible is either true or false. If it's true, then I only want to write those lines from the text file where "Status" (the variable is "stat") is "NEW".

I've figured out everything except how to create this Excel file without actually opening an instance of Excel. As it stands now, the instance of Excel is open and is visual. The needed data from the text file is populated on the visual Excel Worksheet. It's then saved and closed.

Thoughts?

Papa_Don 31 Posting Pro in Training

I've attached the test data file I'm using.

The text file I'm using is created in the background. However I'd like to create the Excel document, add the parsed date from the text file and then save the data as an .xlsx file.

If I make the workbook visible, I can add to the sheet with no issue.

However I'm still unable to "SaveAs" the document. I've tried all things under sun. My latest attempt was using this:

xlWorkBook.SaveAs(newExlName, FileFormat:=51, CreateBackup:=False)
xlWorkBook.Close()
xlApp.Quit()

None of the above closing statements are working.

Again, thanks for your help.

Don

Papa_Don 31 Posting Pro in Training

As it turns out, the code

Dim restranFile As String = hotelFolder & "\Restran\" & propertyNo & "Restran.txt"
Dim restranText As String = File.ReadAllText(restranFile)

wasn't needed. I had copied it from another subroutine.

I did make some changes to the code to get it to open and update the cells. I added xlApp.Visible = True, although I really wanted to have this done in the background or hidden. Is there a way to have this spreadsheet created and saved without it being visible?

Don

Papa_Don 31 Posting Pro in Training

Hi group,

I'm attempting to create and write to an Excel spreadsheet with data from a text file. The code is meant to parse each line of the text and place the data into individual variables. I then need to place these variables into the appropriate cells on this newly created spreadsheet. Although I'm not getting any error messages, the workbook and data isn't being saved anywhere that I can see.

Here's my code as currently written:

Option Strict On
Option Infer Off
Imports System
Imports System.IO
Imports System.Text
Imports Microsoft.Office
Imports Microsoft.Office.Core
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel

Private Sub ConvertToExcel()
        Dim rowXl As Integer
        Dim txtLine As String = ""
        Dim header As String = ""
        Dim header1 As String = ""
        Dim header2 As String = ""
        Dim header3 As String = ""
        Dim header4 As String = ""
        Dim header5 As String = ""
        Dim header6 As String = ""
        Dim header7 As String = ""
        Dim arrival As String = ""
        Dim departure As String = ""
        Dim stat As String = ""
        Dim guestType As String = ""
        Dim guestName As String = ""
        Dim roomType As String = ""
        Dim ratePlan As String = ""
        Dim roomRate As String = ""
        Dim roomOcps As String = ""
        Dim paymntInfo As String = ""
        Dim rSource As String = ""
        Dim agentNo As String = ""
        Dim group As String = ""
        Dim endOfLine As String = ""

        Dim newExlName As String = hotelFolder & …
Papa_Don 31 Posting Pro in Training

FYI.... I found the cause of this specific error. It was looking for a "sheet number" (integer). I entered "1" and this fixed it. The adjusted code looks like this on that line: xlWorkSheet = CType(xlWorkBook.Sheets(1), Worksheet)

Now on to the next problem.

Papa_Don 31 Posting Pro in Training

Hello Group,

I'm attempting to create and write to an Excel file. I'm sure I've hit the first of many snags. Hopefully you can point me in the right direction.

In this first attempt, I'm getting an error code: "Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))". The error is attached to this line of code:xlWorkSheet = CType(xlWorkBook.Sheets("Data"), Worksheet)

Here is the full code for this part of the routine:

Option Strict On
Option Infer Off
Imports System
Imports System.IO
Imports System.Text
Imports Microsoft.Office
Imports Microsoft.Office.Core
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel

 Private Sub ConvertToExcel()
        Dim rowXl As Integer
        Dim txtLine As String = ""
        Dim header As String = ""
        Dim header1 As String = ""
        Dim header2 As String = ""
        Dim header3 As String = ""
        Dim header4 As String = ""
        Dim header5 As String = ""
        Dim header6 As String = ""
        Dim header7 As String = ""
        Dim arrival As String = ""
        Dim departure As String = ""
        Dim stat As String = ""
        Dim guestType As String = ""
        Dim guestName As String = ""
        Dim roomType As String = ""
        Dim ratePlan As String = ""
        Dim roomRate As String = ""
        Dim roomOcps As String = ""
        Dim paymntInfo As String = ""
        Dim rSource As String = ""
        Dim agentNo As String = ""
        Dim group As String = ""
        Dim endOfLine As String = ""

        Dim newExlName As String = hotelFolder & "\Restran\" & yearFolder & "\" & propertyNo & …
Papa_Don 31 Posting Pro in Training

Ahhhhh.... (and now the lightbulb comes on)! Excellent! Thanks for the advice and instruction. This is valuable! Thank you both!

Papa_Don 31 Posting Pro in Training

Group,

I've got a complex If/Then statement that may require the use of "Select Case". However I'm not familiar with how "Select Case" actually works. Needless to say, I'm not sure if you can use it within an "If/Then" statement. Here's what I need to do:

If row > 7 And IsNumeric(Microsoft.VisualBasic.Left(txtLine, 2))_ 
And (Microsoft.VisualBasic.Mid(txtLine, 18, 1)).Contains("G", "T", "C", "W") Then_
    Go Do something constructive
Else 
    Go do something else
End If.

To explain, I'm reading some specific characters from a line in a text file. In the second part, I'm reading one (1) character and need to determine if it is a "G, T, C or W". I suspect that "Select Case" may be the way to do this. Needless to say the above code is incorrect. Would there be a proper way to write this.

FYI....

This line will actually be an "ElseIf" line as it is part of a bigger If/Then statement.

In advance, thanks for your help.

Don

Papa_Don 31 Posting Pro in Training

Minimalist, You're probably on the right track for me. However I'm not sure I understand "Select Case" even after reading as I'm not sure how to incorporate this in my If/Then statement. This is because my statement is going to be somewhat complex.

I think I will mark this solved and resubmit the question as "If/Then and Select Case".

Papa_Don 31 Posting Pro in Training

Group,

I need to insert an "If/Then" statement that will require the command to check the string for multiple variables. In other words it needs to say something like this:

If Microsoft.VisualBasic.Mid(txtLine, 18, 1) = "C" or "T" or "G" or "W" Then
    Go do something
End If

After doing some reading, it seems that "Regex" may be the way to correctly write this. However this is a new command to me and I'm not sure how to write something like this. Can you give me a hand?

In advance, thanks for the help.

Don

Papa_Don 31 Posting Pro in Training

tinstaafl, I'll do some reading and give that a try. I hope that works.

Thanks for the help.

Papa_Don 31 Posting Pro in Training

As it turns out, I had a table listed twice which may have been causing the issue. I took it out, change to a different table and it worked correctly. Here's the final code:

case
          WHEN EXTRACT(YEAR from a19.FULL_DATE) IN ('2015') and a15.COUNTRY_NAME in ('CANADA')
          then sum((A11.RM_NT_QTY * A11.RM_RATE_USD_AMT) * (SELECT  d_cur_exchng.cur_exchng_from_usd_rate
                                                            FROM    d_cur_exchng,
                                                                    crmmart.d_date_period
                                                            WHERE   d_cur_exchng.date_key = a11.STAY_DATE_KEY  and
                                                                    crmmart.d_date_period.full_date = a19.full_date and
                                                                    cur_key = 113 and 
                                                                    rcrd_sts_cd = 'A'))
Papa_Don 31 Posting Pro in Training

Hi group,

I'm getting an "single-row subquery returns more than one row" error when running my routine. Looking through the table I'm querying, it is listing a date twice for some reason. Since I'm not able to remove this one row, is there a way to return the first instance of the date? If so, how?

I'm using a case statement where the routine is crashing. It reads as:

case    when        a01.COUNTRY_NAME in ('CANADA')
        then        a01.REV_TY * (select CADRATE.CANADIAN_EXCHANGE.CONV_RATE
                                  from CADRATE.CANADIAN_EXCHANGE
                                  where a01.STAY_DATE = CADRATE.CANADIAN_EXCHANGE.CONV_DATE)
        else        a01.REV_TY
end as CONV_REV_TY,

FYI: The CADRATE.CANADIAN_EXCHANGE has two instances of the March 1, 2015 date.

Are there thoughts on how to do this?

Thanks for your help.

Don

Papa_Don 31 Posting Pro in Training

Group,

In VB.net, I use the following to return the actual name of a folder where data is kept. Is there an equivalent in VBA? I sure hope so. It would solve some problems for me.

foldernameGD = CStr(getFolderNameGD("O:\Revenue Management\Centralized Revenue Management Service\CRMS Hotels", propertyNo2 & "*"))

In advance, thanks for your help.

Don

Papa_Don 31 Posting Pro in Training

Group,

In VB.net, I use the following to return the actual name of a folder where data is kept. Is there an equivalent in VBA? I sure hope so. It would solve some problems for me.

foldernameGD = CStr(getFolderNameGD("O:\Revenue Management\Centralized Revenue Management Service\CRMS Hotels", propertyNo2 & "*"))

In advance, thanks for your help.

Don

Papa_Don 31 Posting Pro in Training

@Hanif1993, I'm using Microsoft Office Professional 2010.

Papa_Don 31 Posting Pro in Training

Changing the code to read ActiveWorkbook.Close True made the spreadsheet stop and display an error.

Papa_Don 31 Posting Pro in Training

ddanbe, That definately isn't the issue. All of our computers are loaded with the "standard issue" fonts and all are exactly the same. Specifically, the heading are saved with the "Calibri" size 10 font. It is converting back to "Arial" size 6 - but only on a handful of cells. It is not across the page. If I didn't no better, I'd say those specific cells are programmed to change (either programmatically or through Conditional Formatting". But I see no indication of either anywhere.

I'm at a loss.

Papa_Don 31 Posting Pro in Training

Group,

I've created some spreadsheets that others use. The headings of the spreadsheets are divided up with colors. However after I've created these and saved them formatted as I like, some of these cells are changing automatically after the other users open them up.

I've read online there may have been custom formatting done originally. So I've gone in and deleted all of the "custom" formatting available. If then changed the column headings back to the formatting I prefer. But again, as the user is opening it the next day, these handful of column heading go back to a white background with a change in font and font size.

Any ideas what could cause this? More importantly, how do I fix it?

In advance, thanks for your help.

Don

Papa_Don 31 Posting Pro in Training

Hi Group,

I've recreated a spreadsheet that has multiple macro's behind it. One of these macro's opens and existing file, copies the data in it and then pastes it into the original workbook (where the macro's originate) and then closes the existing file. After the copy and paste is done, this original worksheet (for some unknown reason) is being saved as a new file (that name is "89.123456"). For the life of me, I can't figure out why.

Here's the code that runs this:

    Option Explicit

    Dim strategyName As String
    Dim docName As String
    Dim priceOptName As String
    Dim folderName As String
    Dim rptName As String
    Dim prevDate1 As Date
    Dim prevDate As String
    Dim Month1 As String
    Dim Day1 As String
    Dim Year1 As String

Sub CopyStrategyAnalysis()

    Dim iRet As Integer
    Dim strPrompt As String
    Dim strTitle As String

'   This Macro copies the Strategy Document and the PO Optimization Document

    ' This retrieves the files names from the Info tab page
    Sheets("Strategy").Select
    strategyName = Sheet4.Range("C6").Value
    docName = Sheet4.Range("C4").Value
    priceOptName = Sheet4.Range("C7").Value
    folderName = Sheet4.Range("B8").Value
    rptName = Sheet4.Range("C5").Value


    ' Check to see if we are on/off the Starwood network
    strPrompt = "Because you are off the network, you must first open" & vbNewLine & "the 'Analysis' and 'Price Optimization' documents" & vbNewLine & "manually.  If this has been done, click 'Yes'.  If not," & vbNewLine & "open these now and then click 'Yes'.  Otherwise click 'No'."
    strTitle = "Open Documents"

    If Sheet1.rdoNetwork = False Then
        iRet = …
Papa_Don 31 Posting Pro in Training

Minimalist, never mind. I believe I've fixed it. I've written the following in:

If propertyNo IsNot Nothing Then
                strLength = propertyNo.Length
                Blah.. blah.. blah...
End If

I've tested it and it worked fine.

You're my hero!

Don

Papa_Don 31 Posting Pro in Training

Minimalist,

Bingo! Found it!

strLength = propertyNo.Length

This is happening because I'm getting a "Null" value. The error message says, "NullReference exception occured".

I'm fairly sure I know why it's happening. And it is something that can happen. This specific line is looking for the number of characters in the property number. However it is possible for the variable to be blank (thus the "null" value). Now the question is, how do I overcome this "error".

Thanks again! You're the man!!

Don

Papa_Don 31 Posting Pro in Training

Hello group,

I've decided I want to take some classes in SQL, to better understand how to write queries. In looking online for where to turn to for this kind of training, I also see classes to prep for certification. I'm curious of what the value to my employer would be if I had that kind of certification (specifically Oracle/SQL 11g)? Is the expense for this certification worth it to my employer? Is there additional training in that prep that would benefit me and/or my employer?

If you have some thoughts, feel free to post. If you prefer, you can send me a private note as well.

In advance, thanks for your thoughts and ideas.

Don

Papa_Don 31 Posting Pro in Training

Rev Jim, thanks for the suggestions! These are absolutely fantastic. I knew there had to be a way to shorten the way it was written. I suspect it will be faster as well.

Minimalist, I'm not having any issue with the foldernames portion of this. It's finding everything correctly and working fine there. To get an idea of folder names, they look like this:

0208 - FPbS Norwood
0033-Sheraton Baltimore North Hotel
868-Sheraton Mahwah Hotel

To my initial issue, does any thing within this error message indicate where my problem is:

System.NullReferenceException: Object reference not set to an instance of an object.
at IPSDataUpdate.Main.RunComplexProperties()
at IPSDataUpdate.Main.Main_Load(Object sender, EventArgs e)
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ContainerControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

"Object Reference not set to an instance of an object." That suggests to me that I have some code in there that is pointing to an object that doesn't exist. Is my thinking right? If that's the case, wouldn't I get the squiggly lines in the code that would show me where my issue is?

Any thoughts and ideas?

Thanks, group! You're the best!

Don

Papa_Don 31 Posting Pro in Training

Group, I've cleaned up all my errors. Unfortunately I'm still getting my error message. I'm wondering now.... Did I delete a GUI control as Toby_2 suggested? I don't recall doing this. But is there way I can find it?

Papa_Don 31 Posting Pro in Training

"In your case I think it just means that you have to put the as clause at the enf of your function like:
Public Function getFolderNameGD(ByVal fullyQualifiedFolderName As String, ByVal searchPattern As String) As String"

I didn't realize that you can (and in this case, should) do something like this. I've used the original syntax before with no issues.

Papa_Don 31 Posting Pro in Training

I've changed the Option Restrict to "On". Here's the first error I'm having trouble resolving:

Error 1 Option Strict On requires all Function, Property, and Operator declarations to have an 'As' clause.

Here's the code that goes with that line:

 Public Function getFolderNameGD(ByVal fullyQualifiedFolderName As String, ByVal searchPattern As String)
        'return directory that matches searchPattern
        For Each fqDirName As String In System.IO.Directory.GetDirectories(fullyQualifiedFolderName, searchPattern, System.IO.SearchOption.TopDirectoryOnly)
            Return fqDirName
        Next
        Return String.Empty
    End Function

It doesn't seem to like "getFolderNameGD". The lines that go with this function now have been changed from

foldernameGD = getFolderNameGD("O:\Revenue Management\Centralized Revenue Management Service\CRMS Hotels", propertyNo2 & "*")

To this:

foldernameGD = CStr(getFolderNameGD("O:\Revenue Management\Centralized Revenue Management Service\CRMS Hotels", propertyNo2 & "*"))

Is it trying to tell me my Function name should change? If not, what is it?

Papa_Don 31 Posting Pro in Training

Group,

I'm extracting some month and year values from dates. I need to ensure they are NUMBERS as I'm going to multiply, add and subtract. I'm assuming I need to DECLARE these as NUMBERs. However I'm not sure how to do this correctly as I'm getting errors preventing me to run this. Here's my SQL code I've written so far:

DECLARE
                            Period_Out_Month_Number         NUMBER;
                            Period_Out_Year_Number          NUMBER;
                            Open_Date_Month_Number          NUMBER;
                            Open_Date_Year_Number           NUMBER;


Select  Distinct            b.prop_master_id,
                            a.periods_out_desc,
                            b.Open_Date,


Case                        
        When                (SUBSTR(a.periods_out_desc,0,3)) in 'Jan' then 1
        when                (SUBSTR(a.periods_out_desc,0,3)) in 'Feb' then 2
        when                (SUBSTR(a.periods_out_desc,0,3)) in 'Mar' then 3
        when                (SUBSTR(a.periods_out_desc,0,3)) in 'Apr' then 4
        when                (SUBSTR(a.periods_out_desc,0,3)) in 'May' then 5
        when                (SUBSTR(a.periods_out_desc,0,3)) in 'Jun' then 6
        when                (SUBSTR(a.periods_out_desc,0,3)) in 'Jul' then 7
        when                (SUBSTR(a.periods_out_desc,0,3)) in 'Aug' then 8
        when                (SUBSTR(a.periods_out_desc,0,3)) in 'Sep' then 9
        when                (SUBSTR(a.periods_out_desc,0,3)) in 'Oct' then 10
        when                (SUBSTR(a.periods_out_desc,0,3)) in 'Nov' then 11
        when                (SUBSTR(a.periods_out_desc,0,3)) in 'Dec' then 12

end as Period_Out_Month_Number,

                            (SUBSTR(a.periods_out_desc,-4,4)) Period_Out_Year_Number,
                            EXTRACT(MONTH FROM b.Open_Date)  Open_Date_Month_Number,
                            EXTRACT(YEAR FROM b.Open_Date)  Open_Date_Year_Number


from                        dmart.F_ST_PERIOD_WKLY a,
                            crmmart.d_prop b 

where   b.prop_master_id in ('123') and
        (SUBSTR(a.periods_out_desc,-4,4) in ('2008', '2009', '2010', '2011', '2012', '2013', '2014', '2015')) and
        (SUBSTR(a.periods_out_desc,0,3) in ('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'))

I've tried adding BEGIN after the DECLARE statements. However that still left me with an error message. My question is: How do I correctly write my code to DECLARE that I want my "extractions" to be stored in the form of a number?

In advance, thanks for your help.

Don

Papa_Don 31 Posting Pro in Training

Group,

The GUI does show after the error message displays. I have Option Restrict Off and Option Infer Off.

Here's the code I've written:

Option Strict Off
Option Infer Off
Imports System
Imports System.IO
Imports System.Text
Imports iTextSharp.text
Imports iTextSharp.text.pdf

Public Class Main
    Dim prop01 As String
    Dim prop02 As String
    Dim prop03 As String
    Dim prop04 As String
    Dim prop05 As String
    Dim prop06 As String
    Dim prop07 As String
    Dim prop08 As String
    Dim prop09 As String
    Dim prop10 As String
    Dim prop11 As String
    Dim prop12 As String
    Dim prop13 As String
    Dim prop14 As String
    Dim prop15 As String
    Dim prop16 As String
    Dim prop17 As String
    Dim prop18 As String
    Dim prop19 As String
    Dim prop20 As String
    Dim prop21 As String
    Dim prop22 As String
    Dim prop23 As String
    Dim prop24 As String
    Dim prop25 As String
    Dim prop26 As String
    Dim prop27 As String
    Dim prop28 As String
    Dim prop29 As String
    Dim prop30 As String
    Dim pageCode As Char = Microsoft.VisualBasic.ChrW(&H2640)
    Dim foldernameGD As String
    Dim AllOvrSave As String
    Dim saveAllOvr As String
    Dim DASRPTSave As String
    Dim saveDASRPT As String
    Dim restranSave As String
    Dim saveRestran As String
    Dim hotelFolder As String
    Dim propertyNo As String
    Dim propertyNo1 As String
    Dim propertyNo2 As String
    Dim tempSave As String
    Dim yearFolder As String
    Dim formDate As String
    Dim formMonth As String
    Dim formDay As String
    Dim formYear As String

    Private Sub Main_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        '   creating …
Papa_Don 31 Posting Pro in Training

Hi Group,

I'm getting the following error message as soon as the application opens:

************** Exception Text **************
System.NullReferenceException: Object reference not set to an instance of an object.
at IPSDataUpdate.Main.RunComplexProperties()
at IPSDataUpdate.Main.Main_Load(Object sender, EventArgs e)
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ContainerControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

I've stepped through the application line by line in an effort to find where the issue is at. However I can't seem to find it. The application seems to build fine with no errors coming up. It is only after the user opens the application does this come up. Most of the information above is probably helpful. However I'm not real sure what it's trying to tell me. Any thoughts on where to look to fix this?

As always, thanks for your help.

Don

Papa_Don 31 Posting Pro in Training

Thanks for the updates. At least you've got me thinking in the right direction. I'll try finding a TOAD site and ask them.

Thank you very much for the help.

Papa_Don 31 Posting Pro in Training

I think this is what I want. However based on how I've written the code so far, I'm not sure where to insert this new line (or should I modify an existing line?). Here's how it is currently written:

where                  
          a11.BKNG_LOC_KEY = a14.LOC_KEY and             
          a11.PROP_KEY = a15.PROP_KEY and             
          a11.RATE_PLAN_KEY = a16.RATE_PLAN_KEY and             
          a11.BKNG_DATE_KEY = a17.DATE_KEY and             
          a11.ARV_DATE_KEY = a18.DATE_KEY and            
          a11.STAY_DATE_KEY = a19.DATE_KEY and            
          a15.prop_master_id = a20.prop_id and
          a11.DPRT_DATE_KEY = a22.DATE_KEY and 
          a15.PROP_MASTER_ID = A21.PROP_MASTER_ID and
          a19.FULL_DATE between To_Date('01/01/15','MM/DD/YY') and To_Date('05/26/15','MM/DD/YY'

In trying to use your suggestion, I've rewritten it this way:

          a19.FULL_DATE between To_Date(:begdate,'MM/DD/YY') and To_Date(:enddate,'MM/DD/YY')  

This does bring up a box to enter something in to both of these new variables. Unfortunately when trying to run the script, I get an error message that says, "Parameter begdate not found". Is this because I'm not entering it in the correct format (I used 01/01/2015)?

Thanks for your help.

Don