Create Inquiry For Quotation

Solved/Closed
Mike - Mar 22, 2012 at 07:12 AM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
- Apr 3, 2012 at 10:02 AM
Hello,
I'm Mike,
I'm a mechanical engineer, in my company we send inquiries for quotations.
I have two Excel file the first is a table where all the needed details of a supplier are available(with a check box for each supplier), the second one is a sample of an inquiry for quotation named ( SAMPLE)
i need a macro that will do the following:
1- i will check the boxes of suppliers that i want to generate theInquiries.
2- i will press the Genrate botton
3- an interface will appear, where i will insert the name of the project and some other data, like a specific tag number, a date, my name and the due date of the reply.
i appriciate if you give me an e-mail or a method to send you the two files so you can see what i'm talking about.

thank you in advance


8 replies

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Mar 24, 2012 at 03:07 PM
You can post the files on some shared link and post back the link to the file. It would also help if you could use those files as reference and re-explain what you would like to do
0
Please Find on the following link Two Files, One named ( Program) and the other IFQ-IFQ number-Company name
what i need is:
1-check the boxes in the program file.
2- click generate ( the program will create a folder on desktop titeled "IFQ-Project Name"
3- the program will create a copy of the IFQ-IFQ number-Company name ( the IFQ number and company name are filled automatically from the table in program excel file)
4- all red highlited cells will be filled automatically from the table in program excel file.
5- all green highlited cells will be filled from a text box
Text Box: when i hit generate, a box will appear where i fill in all the green highlited categiries.(Date, Project Name-Inquiry Tag-Engineer Name-Due date-)
6- i have to be able to add as much as i want raws in the table of program excel file
(All red and green cells have to be black font in the original and copy version of IFQ)
thank you in advance.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Mar 26, 2012 at 02:38 AM
what link ? may be forum autofilter did not like the link. you can try to put space in place of dots in the link
0
wwwdot4shareddotcom/ file/ usOW7bIl/ filedothtml
wwwdot4shareddotcom/ file/ NpsrlnQ_/ filedothtm

i replaced . by dot and added some spaces.
thank you in advance
0
Mayyouck_10
Posts
8
Registration date
Monday March 26, 2012
Status
Member
Last seen
April 3, 2012

Mar 26, 2012 at 03:12 AM
fourshareddotcom/ file/ usOW7bIl/ filedothtml
fourshareddotcom/ file/ NpsrlnQ_/ filedothtm

i replaced . by dot and added some spaces.
thank you in advance
please add www dot and replace four by 4
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Mar 26, 2012 at 03:18 AM
does not allow me to download without being a member
try
https://authentification.site
0

Didn't find the answer you are looking for?

Ask a question
Mayyouck_10
Posts
8
Registration date
Monday March 26, 2012
Status
Member
Last seen
April 3, 2012

Mar 26, 2012 at 03:28 AM
http://speedy.sh/ca992/Program.xlsx
http://speedy.sh/8TAAe/IFQ-IFQ-Number-Company-name.xlsx
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Mar 26, 2012 at 07:33 PM
why is there a need for a new workbook /? looking at the files, I think you are better served using access database
0
Mayyouck_10
Posts
8
Registration date
Monday March 26, 2012
Status
Member
Last seen
April 3, 2012

Mar 27, 2012 at 12:54 AM
If you want we can merge the two files ( program and IFQ-IFQ Number-Company name) i one workbook and the the macro will create separate excel files ( Copy of IFQ-IFQ Number-Company name)
I need the new workbook because i will send each one to separate supplier ( a company )
so each workbook named ( IFQ-........) will go to a separate company.
i appriciate your help.
0
Mayyouck_10
Posts
8
Registration date
Monday March 26, 2012
Status
Member
Last seen
April 3, 2012

Mar 31, 2012 at 01:21 AM
is it possible to do this with Macro Excel??!!!
0
Mayyouck_10
Posts
8
Registration date
Monday March 26, 2012
Status
Member
Last seen
April 3, 2012

Mar 31, 2012 at 03:35 AM
if the problem is in the check box!! we can replace it by a cell where we type 1 instead of the check box and 0 if the ox is not checked.
Thank you for your help
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Apr 2, 2012 at 07:28 PM
Is the green stuff will be same for all file ?
0
Mayyouck_10
Posts
8
Registration date
Monday March 26, 2012
Status
Member
Last seen
April 3, 2012

Apr 3, 2012 at 12:36 AM
every time i run the macro, i have to reenter the green parameters.
so when i run the macro it will ask me about these parameters but just once, it will ask me one time and create a number of IFQ-...... where the green parameters are the same, but the next time i run the macro to create other IFQ i have to reenter these parameters.
all the selected companies will have the same green parameters(when IFQ is created).
and when i run the macro another time i want to change these parameters but just once for all the selected companies.
thank you a lot. i really appriciate your help.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Apr 3, 2012 at 06:14 AM
Try this. First some assumptions

1. the two files are in same location
2. instead of checkbox in column 12, there is "TRUE" if the row is to be considered.
3. there is data validation

Option Explicit

Sub createMe()
    Dim finalLocation                   As String
    Dim fileLocation                    As String
    Dim lRow                            As Long
    Dim lStartRow                       As Long
    Dim lLastRow                        As Long
    
    Dim ifqTag                          As String
    Dim projectName                     As String
    Dim requestDate                     As String
    Dim engineer                        As String
    Dim dueDate                         As String
    
    Dim wbIFQ                           As Workbook
    Dim companyName                     As String
    Dim productname                     As String
    Dim contactPerson                   As String
    Dim phoneNumber                     As String
    Dim faxNumber                       As String
    Dim emailId                         As String
    Dim ifqNumber                       As String
  
    Dim temp                            As String
    
    ThisWorkbook.Activate
    
    Const QUOTE_FILE = "IFQ-IFQ Number-Company name.xlsx"
    
    fileLocation = ThisWorkbook.Path
    If (Right(fileLocation, 1) <> Application.PathSeparator) Then fileLocation = fileLocation & Application.PathSeparator
    fileLocation = fileLocation & QUOTE_FILE
    If (Dir(fileLocation) = vbNullString) Then
        MsgBox "unable to find file " & fileLocation
        Exit Sub
    End If
    
    
    lStartRow = getItemLocation("true", Columns(12), , False)
    If lStartRow = 0 Then
        MsgBox "There is nothing to generate"
        Exit Sub
    End If
    
    lLastRow = getItemLocation("true", Columns(12))
  
    ifqTag = InputBox("Enter IFQ Tag", "IFQ Tag")
    projectName = InputBox("Enter Project", "Project")
    requestDate = InputBox("Enter Request Date", "Request Date")
    engineer = InputBox("Enter Engineer Name", "Engineer Name")
    dueDate = InputBox("Enter Due By Date", "Due By")
    
    finalLocation = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator & "IFQ - " & projectName & Application.PathSeparator
    temp = Dir(finalLocation, vbDirectory)
    If (temp = vbNullString) Then MkDir finalLocation
    
    ThisWorkbook.Activate
    For lRow = lStartRow To lLastRow
        ThisWorkbook.Activate
        If (Cells(lRow, "M") = True) Then
            companyName = Cells(lRow, "C")
            productname = Cells(lRow, "D")
            contactPerson = Cells(lRow, "F")
            phoneNumber = Cells(lRow, "G")
            faxNumber = Cells(lRow, "H")
            emailId = Cells(lRow, "J")
            ifqNumber = Cells(lRow, "K")
        

            Set wbIFQ = Workbooks.Open(fileLocation)
            wbIFQ.SaveAs finalLocation & ifqNumber & " - " & companyName
            
            wbIFQ.Sheets("Form-IFQ-34").Range("B17") = companyName
            wbIFQ.Sheets("Form-IFQ-34").Range("C18") = phoneNumber
            wbIFQ.Sheets("Form-IFQ-34").Range("C19") = faxNumber
            wbIFQ.Sheets("Form-IFQ-34").Range("D20") = emailId
            wbIFQ.Sheets("Form-IFQ-34").Range("B22") = contactPerson
            wbIFQ.Sheets("Form-IFQ-34").Range("A27") = productname
            wbIFQ.Sheets("Form-IFQ-34").Range("O16") = ifqTag & "/" & ifqNumber
             
             wbIFQ.Sheets("Form-IFQ-34").Range("B23") = requestDate
             wbIFQ.Sheets("Form-IFQ-34").Range("B24") = projectName
             wbIFQ.Sheets("Form-IFQ-34").Range("A35") = engineer
             wbIFQ.Sheets("Form-IFQ-34").Range("G50") = dueDate
            
            wbIFQ.Save
            wbIFQ.Close
            Set wbIFQ = Nothing
        End If
    Next
    
End Sub


Public Function getItemLocation(sLookFor As String, _
                                rngSearch As Range, _
                                Optional bFullString As Boolean = True, _
                                Optional bLastOccurance As Boolean = True, _
                                Optional bFindRow As Boolean = True) As Long
                                   
   'find the first/last row/column  within a range for a specific string
      
   Dim Cell             As Range
   Dim iLookAt          As Integer
   Dim iSearchDir       As Integer
   Dim iSearchOdr       As Integer
         
   If (bFullString) _
   Then
      iLookAt = xlWhole
   Else
      iLookAt = xlPart
   End If
   If (bLastOccurance) _
   Then
      iSearchDir = xlPrevious
   Else
      iSearchDir = xlNext
   End If
   If Not (bFindRow) _
   Then
      iSearchOdr = xlByColumns
   Else
      iSearchOdr = xlByRows
   End If
         
   With rngSearch
      If (bLastOccurance) _
      Then
         Set Cell = .Find(sLookFor, .Cells(1, 1), xlValues, iLookAt, iSearchOdr, iSearchDir)
      Else
         Set Cell = .Find(sLookFor, .Cells(.Rows.Count, .Columns.Count), xlValues, iLookAt, iSearchOdr, iSearchDir)
      End If
   End With
         
   If Cell Is Nothing Then
      getItemLocation = 0
   ElseIf Not (bFindRow) _
   Then
      getItemLocation = Cell.Column
   Else
      getItemLocation = Cell.Row
   End If
   Set Cell = Nothing

End Function

0
Mayyouck_10
Posts
8
Registration date
Monday March 26, 2012
Status
Member
Last seen
April 3, 2012

Apr 3, 2012 at 06:32 AM
it is creating the folder but it is not generating the IFQ!!
0
Mayyouck_10
Posts
8
Registration date
Monday March 26, 2012
Status
Member
Last seen
April 3, 2012

Apr 3, 2012 at 08:07 AM
ok ok it works, this is great. thank you a lot. i really appriciate your help.
if i face any problem i will contact you.
thank you again
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Apr 3, 2012 at 10:02 AM
You are quite welcome.
0