PDA

View Full Version : EXCEL/VBA macro for print address wrong spreadsheet in wrong workbook



Dilermando
05-26-2015, 04:08 AM
Hello. That’s is my situation:

1 - file "cadastrar paciente.xls" has just one spreadsheet: "cadastrar paciente" and two macros: "gravar paciente" and "imprimir ficha paciente"

2 - file "cadastro pacientes.xls" has two spreadsheets: "cadastro pacientes" and "agenda"

3 – after opening "cadastro pacientes.xls", I type a few data in spreadsheet "cadastrar paciente"

4 – then I run macro "gravar paciente" (in "cadastrar paciente.xls"), to save such data in spreadsheet "cadastro pacientes" (located in in the other file "cadastro pacientes.xls")

5 - macro "imprimir ficha paciente" was automatically created, it’s purpose is to print a fixed range of cells belonging to spreadsheet "cadastrar paciente" (located in "cadastrar paciente.xls")

6 - however, macro "imprimir ficha paciente" doesn’t work as designed, it starts opening the wrong spreadsheet "cadastro pacientes" - which is located in the wrong file "cadastro pacientes.xls - then it freezes (no error message is displayed, anytime)

7 - I guess some clever trick is lacking, how to tell my macro to go the right way, that is, to pick up a range of cells in spreadsheet "cadastrar paciente" (located in "cadastrar paciente.xls"), then proceed to printing ?

Your help will be fully appreciated. Thank you !

**********************************************************
macro "imprimir ficha paciente" (located in "cadastrar paciente.xls")
Sub imprimir_ficha_paciente()
' selecionar dados para impressão da ficha do paciente
Range("G4:H22").Select
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = ""
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.511811023622047)
.RightMargin = Application.InchesToPoints(0.511811023622047)
.TopMargin = Application.InchesToPoints(0.78740157480315)
.BottomMargin = Application.InchesToPoints(0.78740157480315)
.HeaderMargin = Application.InchesToPoints(0.31496062992126)
.FooterMargin = Application.InchesToPoints(0.31496062992126)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With
Application.PrintCommunication = True
Selection.PrintOut Copies:=1, Collate:=True
Range("A1").Select
End Sub

Kenneth Hobs
05-26-2015, 09:42 AM
Use Activate to active the workbook and worksheet as needed.

Fully qualify your references. e.g.

MsgBox Workbooks("cadastrar paciente").WorkSheet("Sheet1").Range("A1").Value

Dilermando
05-26-2015, 10:24 AM
Hello Keneth.
Thank you for your answer.
Following your advice, I added the line:
Workbooks("C:\CONTROLE PACIENTES CONSULTAS PAGAMENTOS SUELY GRIMALDI\CADASTRAR PACIENTE.xls").Worksheet("CADASTRAR PACIENTE").Range("A1").Value
just before the line:
Range("G4:H22").Select
(workbook and worksheet have the same name)
Sorry, it still doesn't work: macro insists opening the wrong couple workbook/worksheet, then freezes.
What a mistery ...

Kenneth Hobs
05-26-2015, 12:40 PM
I used MsgBox to show a value. You used it without MsgBox which is like coding: 5. 5 would be a syntax error.

When debugging code, I use a MsgBox or Debug.Print. The latter used instead of MsgBox prints a run's result into the Immediate Window of the Visual Basic Editor (VBE). You can even test one line snippets of code there. e.g. x=5: ?x, and press Enter key would return 5. Think of the ? used this way in that window as a "Debug.Print x" line of code.

Another good way to see where your problem lies is by debugging your code by running one line of code at a time. Press F8 while in your routine to execute each line. Hover your mouse over variables to see what they resolved to.

To better explain full, partial, and relative objects, methods and properties, replace the string "x:\test\test.xlsm" to one of your files and "Sheet1" as needed.

Notice how I used With to prefix the object and then used . to reference those objects. I could have added the workbook object and the sheet object prefix to each property but With is more efficient.

Notice that in the 2nd MsgBox, . is not used which infers that we are using the active objects like ActiveWorkbook and ActiveSheet when we use the object Range for example.

Sub ken()
Dim wb As Workbook, ws As Worksheet
Set wb = Workbooks.Open("x:\test\test.xlsm")
Set ws = wb.Worksheets("Sheet1")

With ws
MsgBox "Workbook Path: " & wb.Path & vbCrLf & _
"Workbook FullName: " & wb.FullName & vbCrLf & _
"Workbook Name: " & .Parent.Name & vbCrLf & _
"Worksheet Name: " & .Name & vbCrLf & _
"Relative Range A1 Address: " & .Range("A1").Address(False, False) & vbCrLf & _
"Absolute Range A1 Address: " & .Range("A1").Address & vbCrLf & _
"External Range A1 Address: " & .Range("A1").Address(External:=True) & vbCrLf & _
"Range A1 Value: " & .Range("A1").Value
End With

'Close opened workbook
wb.Close False

'ActiveWorkbook, ActiveSheet
MsgBox "Workbook Path: " & ActiveWorkbook.Path & vbCrLf & _
"Workbook FullName: " & ActiveWorkbook.FullName & vbCrLf & _
"Workbook Name: " & ActiveWorkbook.Name & vbCrLf & _
"Worksheet Name: " & ActiveSheet.Name & vbCrLf & _
"Relative Range A1 Address: " & Range("A1").Address(False, False) & vbCrLf & _
"Absolute Range A1 Address: " & Range("A1").Address & vbCrLf & _
"External Range A1 Address: " & Range("A1").Address(External:=True) & vbCrLf & _
"Range A1 Value: " & Range("A1").Value
End Sub

Dilermando
05-26-2015, 06:02 PM
Hello Kenneth.

1 - I tried also with MsgBox, however with the same disturbing outcome

2 - then I tried inserting my code for printing in the macro which previous scope was just recording typed data in agenda consultas cadastro pacientes.xls

3 - guess what ... it worked !

I'm sorry I could not fully appreciate your suggestions, given my very limited knowledge of VBA. But I feel you brought to my attention some relevant issues, which I'll try to decode.

Thank you very much !

Dilermando