Log In
Register
Home
June 26, 2016
Offline
Hi Everyone my new problem is this:
On an excel sheet with Office 2007 I entered numbers ranging from cell (A1: E18)
And Three Shape Buttons
Now the purpose of this project and that I would like to be able to achieve is this:
1) By pressing the first key It should from Routine Create a Word Sheet and insert it in my sub Folder with only the data taken from the cells (A1: E18) and save it with the name I insert in the cell (I1)
2) The Same Thing You Should Be Achieving with an Excel Sheet
3) Also for what concerns the file in pdf format which in some ways is the only one that I was able to make work Except for the fact that I would like it to take into consideration only the numbered cells and not the whole sheet.
I wish you could also insert the image both in the saved Excel sheet and in the Word and PDF sheet
A bit like I did it manually in the various formats that you find in the subfolder called (Attachments) all here
Thank you for all the help you will want to give me on this
Greetings from A.Maurizio
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
To write into word, save to folder:
Range("A1:C20").Copy
Scrittura.Selection.Paste
Documento.SaveAs ThisWorkbook.Path & "\Allegati\" & Range("I1").Value
For excel, instead of copying the sheet (activesheet.copy):
Dim Rng As Range: Set Rng = ActiveSheet.Range("A1:E18")
Workbooks.Add
Rng.Copy ActiveWorkbook.Worksheets(1).Range("A1")
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\Allegati\" & Range("I1").Value
For PDF, you have to set the printable area of the excel sheet to A1:E18, only that range will be printed to pdf.
June 26, 2016
Offline
Hello Catalin Bombea
Thanks for your suggestions, they have really been useful to me
It was a bit challenging to understand and how to put the concept into practice (Scripture Select) e
(Documento.Save) as I couldn't figure out how to put the pieces together.
Then I figured it all out and now it works great.
Thank you so much
Greetings from Maurizio
June 26, 2016
Offline
Hi Catalin Bombea I thought I could get by on my own; But without you I lose myself in a glass of water:
My Problem is this:
With this Code Following your instructions yesterday I can open the new Excel sheet; But nothing appears inside
This is the code:
Option Explicit Sub CopyToExcel() On Error GoTo 1 Dim oXL As Excel.Application Dim oWB As Excel.Workbook Dim oSheet As Excel.Worksheet Dim oRng As Excel.Range Dim Rng As Range Dim NomeFile As String Set oXL = CreateObject("Excel.Application") 'New Excel.Document oXL.Application.Visible = True Set oWB = oXL.Workbooks.Add Set oSheet = oWB.ActiveSheet Set Rng = ActiveSheet.Range("A1:I26") NomeFile = Foglio1.Range("M1").Value oSheet.Activate 'For Each Rng In ActiveWorkbook.Worksheets Foglio1.Range("A1:I26").Select Rng.ActiveSheet.Copy 'oWB.ActiveSheet.Copy ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\Allegati\" & NomeFile & ".xlsm" 'doc.Close 'Next Rng 1: End Sub
Why This Happens And Where Am I Wrong Thanks
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
What you are doing wrong is that you did not used the code provided that was functional.
Instead, you are using a different method, but instead of copying from source you are copying from the new workbook, which is blank obviously.
Look at this line:
Set Rng = ActiveSheet.Range("A1:I26")
There is a reason why this line is BEFORE adding a new workbook: because when you add a new workbook, THE NEW book becomes active. We need to copy from Active sheet: Set Rng = ActiveSheet.Range("A1:I26"), but because you have this line AFTER adding the new sheet, you are copying from the new blank book.
NomeFile = Foglio1.Range("M1").Value
1 Set Rng = ActiveSheet.Range("A1:I26")
2 Set oWB = oXL.Workbooks.Add
3 Set oSheet = oWB.worksheets(1)
4 Rng.Copy oSheet.Range("A1")
5 oWB.SaveAs Filename:=ThisWorkbook.Path & "\Allegati\" & NomeFile
1 Guest(s)
