dcsimg
Results 1 to 14 of 14

Thread: [RESOLVED] Macro / VB Coding help needed please

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2017
    Posts
    12

    Resolved [RESOLVED] Macro / VB Coding help needed please

    Good day,

    Would someone be able to assist me with the vb coding on the below problem i have.

    I am using Windows 10 with office 365, VB for applications 7.1

    I have a generated and excel file for testing of products, the file contains 2 sheets, one which contains the parameters (data) and the other is the input sheet. The input sheet is updated according to production and this has got a Production order and a trace number in separate cells. When combined this would be the reference to where this must be saved. I added a hidden cell (L8) on the input sheet which merges the text of both and this needs to be the save as file name, this does not contain any thing other than text.

    I want a print and save button which when clicked automatically prints the sheet to the printer and save the file as a PDF on the server.

    When i run a macro it prints fine to the printer and also to the 3rd party application (Adobe) however there it requires a manual input of the file name, i have tried numerous things i found on the web to try and resolve it but for some reason it just goes to that point the whole time.

    My experience in programming is limited to an extend and I only use this as a form of self defense.

    Below is the codes i have tried so far:

    Code 1:

    Code:
    Sub PrintSave()
    '
    ' PrintSave Macro
    ' PrintSave
    '
    ' Keyboard Shortcut: Ctrl+Shift+P
    '
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    End Sub
    
    Sub CleanSave()
        Dim filename As String
        filename = "C:\Users\JJ\Desktop\test reports" & Range("H7").Text & ".pdf"
        
    End Sub
    
    Function strClean(strIn As String) As String
    Dim objRegex As Object
    Set objRegex = CreateObject("vbscript.regexp")
    With objRegex
        .Pattern = "[\[\]|\/\\:\*\?""<>]"
        .Global = True
        strClean = .Replace(strIn, vbNullString)
    End Sub
    Code 2:

    Code:
    Sub savepdf1()
    '
    ' savepdf1 Macro
    ' savepdf1
    '
    ' Keyboard Shortcut: Ctrl+q
    '
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
            IgnorePrintAreas:=False
    End Sub

    Code 3:

    Code:
    Sub saveaspdf()
    '
    ' saveaspdf Macro
    ' Save as pdf
    '
    ' Keyboard Shortcut: Ctrl+Shift+A
    '
        Range("F10").Select
        ActiveSheet.Shapes.Range(Array("Button 1")).Select
        Selection.Characters.Text = "Button 1"
        With Selection.Characters(Start:=1, Length:=8).Font
            .Name = "Calibri"
            .FontStyle = "Regular"
            .Size = 12
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = 1
        End With
        Range("F9").Select
        ActiveSheet.Shapes.Range(Array("Button 1")).Select
        Selection.Characters.Text = "Save as pdf" & Chr(10) & "ton 1"
        With Selection.Characters(Start:=1, Length:=17).Font
            .Name = "Calibri"
            .FontStyle = "Regular"
            .Size = 12
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = 1
        End With
        Range("G10").Select
        ActiveSheet.Shapes.Range(Array("Button 1")).Select
        ActiveSheet.Shapes("Button 1").ScaleWidth 1.4835167925, msoFalse, _
            msoScaleFromTopLeft
        Range("G11").Select
        ActiveSheet.Shapes.Range(Array("Button 1")).Select
        ActiveWorkbook.Save
        Range("G10").Select
    End Sub
    Kind Regards
    Please assist.
    Last edited by Siddharth Rout; May 25th, 2017 at 04:40 AM. Reason: Added Code tags

  2. #2
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,989

    Re: Macro / VB Coding help needed please

    Why are you using ActiveWindow.SelectedSheets.PrintOut method? Why not .ExportAsFixedFormat when printing/saving as pdf? It will let you specify the filename as well.

    You may want to see the below link?

    Workbook.ExportAsFixedFormat Method

    Hope I have understood your query correctly?
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    ACER R7 (Win 8.1+Office 2013+VS2013) || Sony VPCCB-45FN with a Win10+Office 2010. || Mac Book Pro (10.6.8) with Office 2011

  3. #3

    Thread Starter
    New Member
    Join Date
    May 2017
    Posts
    12

    Re: Macro / VB Coding help needed please

    Hi Siddharth,

    Thank you for your reply.

    This could sort of work but i would need the added functionality to automatically save the document as "xxxx.pdf" and the xxxx is contained within a cell in the spreadsheet. In essence the file name would change each time the file is saved but the master template remains.

    eg. The sheet would be called master copy. Say cell (H 7) combines text from both cell's (A1) and (E1) the combination of text would be the "save as" name contained in cell (H7) when doing the pdf i want the folder to save as the text specified in cell H7 within my specified directory.

  4. #4
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,989

    Re: Macro / VB Coding help needed please

    Then

    1. Declare a workbook and worksheet object
    2. Open the workbook which has the path/filename
    3. Read it from the cell
    4. Use it in your code
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    ACER R7 (Win 8.1+Office 2013+VS2013) || Sony VPCCB-45FN with a Win10+Office 2010. || Mac Book Pro (10.6.8) with Office 2011

  5. #5

    Thread Starter
    New Member
    Join Date
    May 2017
    Posts
    12

    Re: Macro / VB Coding help needed please

    Thank you again Siddharth,

    Thank you for your time and patience on this, Now i have 2 questions.


    Please forgive me if this sounds stupid. but now i am more confused than i was when i started.

    1. I have created 5 macro's already to try and get this done, Now for some reason i cannot delete the macros. (Cannot delete macro on a hidden workbook.Unhide the workbook using the unhide command)

    i have no hidden workbooks or columns in the workbook.

    2. I set the workbook to an object using Set wbk = workbooks("INPUT.xlsx")

    I read from the cell using Dim sheet_text As String
    sheet_text = (oXLSheet.Cells(4, 8))
    But this does not work.

    can I PM the workbook to you? maybe there is something which i have done preventing the actions?

    You're help is much appreciated.

  6. #6
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,989

    Re: Macro / VB Coding help needed please

    Ok scratch that. Let's start from the beginning.

    We will rebuild from the start and we will try and have only one macro in lieu of 5 if possible

    Explain what exactly are you trying to do. While explaining use the exact names of the workbook, worksheets and cell names which will be part of the macro
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    ACER R7 (Win 8.1+Office 2013+VS2013) || Sony VPCCB-45FN with a Win10+Office 2010. || Mac Book Pro (10.6.8) with Office 2011

  7. #7

    Thread Starter
    New Member
    Join Date
    May 2017
    Posts
    12

    Re: Macro / VB Coding help needed please

    Siddharth,

    Thank you.

    I have a workbook named "Blastbag Work Instruction" the workbook contains 2 sheets namely "INPUT" and "DATA"

    The "DATA' Sheet contains all the raw data which autofill's parts of the "INPUT" sheet by means of dropdown selection which returns the parameters for the selected product.

    The Product has a Production order number "Input cell C6" (lets say value is 12345) and also a trace number "INPUT cell G6" (lets say value is 1). The 2 cells then combines in "INPUT cell D8" using the formula " = C6&" TN "&G6" ( this cell would then be 12345 TN 1) as it only combines the above mentioned cell's texts. This combination of text would be the file name i want to save the pdf as. (Path would be "C:Users\JJ\Desktop\Blastbagtest\*12345 TN 1*)

    So in essence i want to have a static excel sheet which does not save the excel version but rather prints an hard copy and saves the "input sheets data" a pdf as the file name (combination of the 2 cells) this combination can differ up to 10 times per day. which would mean there can be 10 different files with its own unique Po and trace id in a day. for eg 12345 TN 1, 12345 TN 2, 12345 TN 3, 12346 TN 1) note the trace number is updated each time a new batch of components are used and then each time a new Production order is started the first section changes and the trace id returns to 1. These inputs are done manually.

    The printing and pdf save range is contained in the cell range of ("Input" cells A1:G103) and this consists of 2 pages.

    Unfortunately i cannot let the lady whom deals with it manually save the pdf as there is bound to be errors which would mess up the entire system. thus requiring the simple print and save pdf button on the sheet.

    Thanks again
    Last edited by jjd17; May 26th, 2017 at 03:08 AM.

  8. #8
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,989

    Re: Macro / VB Coding help needed please

    So this is what I have understood.

    1. You are running this code from the workbook "Blastbag Work Instruction"
    2. There are 2 sheets. INPUT" and "DATA"
    3. The filename is in cell D8 of INPUT sheet
    4. You want to save the INPUT sheet range A1:G103 as a pdf.

    Is my understanding correct?
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    ACER R7 (Win 8.1+Office 2013+VS2013) || Sony VPCCB-45FN with a Win10+Office 2010. || Mac Book Pro (10.6.8) with Office 2011

  9. #9

    Thread Starter
    New Member
    Join Date
    May 2017
    Posts
    12

    Re: Macro / VB Coding help needed please

    Correct on all of them

  10. #10
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,989

    Re: Macro / VB Coding help needed please

    Is this what you are trying?

    Code:
    Sub Sample()
        Dim wb As Workbook
        Dim wsI As Worksheet
        Dim NewFileName As String
        
        Set wb = ThisWorkbook
        
        Set wsI = wb.Sheets("Input")
        
        With wsI
            NewFileName = "C:\Users\JJ\Desktop\Blastbagtest\" & .Range("D8").Value & ".pdf"
            
            .Range("A1:G103").ExportAsFixedFormat Type:=xlTypePDF, _
                                                   Filename:=NewFileName, _
                                                   Quality:=xlQualityStandard, _
                                                   IncludeDocProperties:=True, _
                                                   IgnorePrintAreas:=False, _
                                                   OpenAfterPublish:=True
            
        End With
        
        MsgBox ("Done")
    End Sub
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    ACER R7 (Win 8.1+Office 2013+VS2013) || Sony VPCCB-45FN with a Win10+Office 2010. || Mac Book Pro (10.6.8) with Office 2011

  11. #11

    Thread Starter
    New Member
    Join Date
    May 2017
    Posts
    12

    Re: Macro / VB Coding help needed please

    Never mind idiot me used the code in the VBA project instead of the workbook.

    Worked 100%

    Thank you big time!
    Last edited by jjd17; May 26th, 2017 at 04:11 AM.

  12. #12
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,989

    Re: Macro / VB Coding help needed please

    That is because there is some space in the worksheet name. Go to the worksheet and retype the name of worksheet in it's tab. Remember to remove the spaces.
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    ACER R7 (Win 8.1+Office 2013+VS2013) || Sony VPCCB-45FN with a Win10+Office 2010. || Mac Book Pro (10.6.8) with Office 2011

  13. #13

    Thread Starter
    New Member
    Join Date
    May 2017
    Posts
    12

    Re: Macro / VB Coding help needed please

    Thanks Siddharth,

    Worked as per above update.

    Thanks again.

  14. #14
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,989

    Re: Macro / VB Coding help needed please

    Great!

    If your query is solved then feel free to mark the thread resolved If you do not know how then see the link in my signature.
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    ACER R7 (Win 8.1+Office 2013+VS2013) || Sony VPCCB-45FN with a Win10+Office 2010. || Mac Book Pro (10.6.8) with Office 2011

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width