-
May 25th, 2017, 01:28 AM
#1
Thread Starter
New Member
[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
-
May 25th, 2017, 04:55 AM
#2
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
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
May 25th, 2017, 06:46 AM
#3
Thread Starter
New Member
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.
-
May 25th, 2017, 10:07 AM
#4
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
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
May 26th, 2017, 12:33 AM
#5
Thread Starter
New Member
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.
-
May 26th, 2017, 01:23 AM
#6
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
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
May 26th, 2017, 02:56 AM
#7
Thread Starter
New Member
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.
-
May 26th, 2017, 03:26 AM
#8
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
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
May 26th, 2017, 03:28 AM
#9
Thread Starter
New Member
Re: Macro / VB Coding help needed please
-
May 26th, 2017, 03:39 AM
#10
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
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
May 26th, 2017, 03:58 AM
#11
Thread Starter
New Member
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.
-
May 26th, 2017, 04:11 AM
#12
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
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
May 26th, 2017, 04:13 AM
#13
Thread Starter
New Member
Re: Macro / VB Coding help needed please
Thanks Siddharth,
Worked as per above update.
Thanks again.
-
May 26th, 2017, 04:17 AM
#14
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
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|