Windows Activate Excel vba/macro

O

Olaf123

New Member
Joined
May 2, 2019
Messages
5
Hello Everyone.
I just recently got upgraded from Excel 2013 to Excel 2016. When I had 2013 version, I built a vba code where I created an Excel workbook instance, posted some data into it via Access vba and then once it is done the code will connect to my Personal.xlsb and run a build in macro, to format the data that was just posted into a new workbook. The issue is that once I transferred my vba codes into the new Access and Excel, I started to get an error stating that it it error code 9. Out of range.
I played around with the code, and was able to identify that the issue is occurring when I run Excel macro. The process cant Identify which workbook to use. Because of this, I added a new code highlighted in green. It works fin, but the issue is that a newly created workbook would have to have a static name- Book4, but it is not always the case. When I run this process multiple times during the day, the process creates a new workbook with a new name (book1, book2). I tried various options to make make Windows active with a variable name, but it was not successful.
Can someone please help me to figure this out?

Here is my code:



Code: 
Sub FinalReport()
Dim ws As Worksheet


[COLOR=#008000]Windows("Book4").Activate[/COLOR]


Set ws = Application.z.Sheets("MonthlyReport")
'Set ws = Application.ActiveWorkbook.Sheets("MonthlyPReport")
ws.Columns("A:Q").AutoFit


ws.Range("A1").Font.Bold = True
ws.Range("A1").Font.Size = 11
ws.Range("A1").HorizontalAlignment = xlCenter

ws.Range("B1").Font.Bold = True
ws.Range("B1").Font.Size = 11
ws.Range("B1").HorizontalAlignment = xlCenter


ws.Range("C1").Font.Bold = True
ws.Range("C1").Font.Size = 11
ws.Range("C1").HorizontalAlignment = xlCenter


ws.Range("D1").Font.Bold = True
ws.Range("D1").Font.Size = 11
ws.Range("D1").HorizontalAlignment = xlCenter

ws.Range("E1").Font.Bold = True
ws.Range("E1").Font.Size = 11
ws.Range("E1").HorizontalAlignment = xlCenter

ws.Range("F1").Font.Bold = True
ws.Range("F1").Font.Size = 11
ws.Range("F1").HorizontalAlignment = xlCenter

ws.Range("G1").Font.Bold = True
ws.Range("G1").Font.Size = 11
ws.Range("G1").HorizontalAlignment = xlCenter

ws.Range("H1").Font.Bold = True
ws.Range("H1").Font.Size = 11
ws.Range("H1").HorizontalAlignment = xlCenter

ws.Range("I1").Font.Bold = True
ws.Range("I1").Font.Size = 11
ws.Range("I1").HorizontalAlignment = xlCenter

ws.Range("J1").Font.Bold = True
ws.Range("J1").Font.Size = 11
ws.Range("J1").HorizontalAlignment = xlCenter

ws.Range("K1").Font.Bold = True
ws.Range("K1").Font.Size = 11
ws.Range("K1").HorizontalAlignment = xlCenter

ws.Range("L1").Font.Bold = True
ws.Range("L1").Font.Size = 11
ws.Range("L1").HorizontalAlignment = xlCenter

ws.Range("M1").Font.Bold = True
ws.Range("M1").Font.Size = 11
ws.Range("M1").HorizontalAlignment = xlCenter

ws.Range("N1").Font.Bold = True
ws.Range("N1").Font.Size = 11
ws.Range("N1").HorizontalAlignment = xlCenter

ws.Range("O1").Font.Bold = True
ws.Range("O1").Font.Size = 11
ws.Range("O1").HorizontalAlignment = xlCenter

ws.Range("P1").Font.Bold = True
ws.Range("P1").Font.Size = 11
ws.Range("P1").HorizontalAlignment = xlCenter

ws.Range("Q1").Font.Bold = True
ws.Range("Q1").Font.Size = 11
ws.Range("Q1").HorizontalAlignment = xlCenter

Range("A1:Q1").Select

With Selection.Interior
.Pattern = xlSolid
.PatternColor = 12632256
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With

With ws.Range("A1:Q1").Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With ws.Range("A1:Q1").Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With ws.Range("A1:Q1").Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With ws.Range("A1:Q1").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With ws.Range("A1:Q1").Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With

End Sub
 
Last edited by a moderator:

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
I started to get an error stating that it it error code 9. Out of range.
Click to expand...

What line was it erring on?

Also what is z in
Code: 
Set ws = Application.z.Sheets("MonthlyReport")
 
Last edited:
Upvote 0
MARK858 said:
What line was it erring on?

Also what is z in
Code: 
Set ws = Application.z.Sheets("MonthlyReport")
Click to expand...
The error appearing on the whole macro. The only thing that I was able to figure out that the code can’t tell which worksheet to use.
Regarding the “z”, this part of code is noted out- not used. Please disregard that piece. The code in the following row is correct and active.
Thank you.
 
Upvote 0
Regarding the “z”, this part of code is noted out- not used. Please disregard that piece.
Click to expand...

Not in the code posted it isn't, if it is in your code please post your actual current code.

The error appearing on the whole macro.
Click to expand...

No line is highlighted when you click debug?
 
Last edited:
Upvote 0
Hello Mark858,

In addition tio the Excel code I am including Access vba code that triggers my excel code.

The Excel vba code:

Code: 
[COLOR=#333333]Sub FinalReport()[/COLOR]
[COLOR=#333333]Dim ws As Worksheet[/COLOR]


[COLOR=#008000][COLOR=#417394]Windows[/COLOR]("Book4").Activate[/COLOR]

[COLOR=#333333]Set ws = Application.ActiveWorkbook.Sheets("MonthlyPReport")[/COLOR]
[COLOR=#333333]ws.Columns("A:Q").AutoFit[/COLOR]


[COLOR=#333333]ws.Range("A1").Font.Bold = True[/COLOR]
[COLOR=#333333]ws.Range("A1").Font.Size = 11[/COLOR]
[COLOR=#333333]ws.Range("A1").HorizontalAlignment = xlCenter[/COLOR]

[COLOR=#333333]ws.Range("B1").Font.Bold = True[/COLOR]
[COLOR=#333333]ws.Range("B1").Font.Size = 11[/COLOR]
[COLOR=#333333]ws.Range("B1").HorizontalAlignment = xlCenter[/COLOR]


[COLOR=#333333]ws.Range("C1").Font.Bold = True[/COLOR]
[COLOR=#333333]ws.Range("C1").Font.Size = 11[/COLOR]
[COLOR=#333333]ws.Range("C1").HorizontalAlignment = xlCenter[/COLOR]


[COLOR=#333333]ws.Range("D1").Font.Bold = True[/COLOR]
[COLOR=#333333]ws.Range("D1").Font.Size = 11[/COLOR]
[COLOR=#333333]ws.Range("D1").HorizontalAlignment = xlCenter[/COLOR]

[COLOR=#333333]ws.Range("E1").Font.Bold = True[/COLOR]
[COLOR=#333333]ws.Range("E1").Font.Size = 11[/COLOR]
[COLOR=#333333]ws.Range("E1").HorizontalAlignment = xlCenter[/COLOR]

[COLOR=#333333]ws.Range("F1").Font.Bold = True[/COLOR]
[COLOR=#333333]ws.Range("F1").Font.Size = 11[/COLOR]
[COLOR=#333333]ws.Range("F1").HorizontalAlignment = xlCenter[/COLOR]

[COLOR=#333333]ws.Range("G1").Font.Bold = True[/COLOR]
[COLOR=#333333]ws.Range("G1").Font.Size = 11[/COLOR]
[COLOR=#333333]ws.Range("G1").HorizontalAlignment = xlCenter[/COLOR]

[COLOR=#333333]ws.Range("H1").Font.Bold = True[/COLOR]
[COLOR=#333333]ws.Range("H1").Font.Size = 11[/COLOR]
[COLOR=#333333]ws.Range("H1").HorizontalAlignment = xlCenter[/COLOR]

[COLOR=#333333]ws.Range("I1").Font.Bold = True[/COLOR]
[COLOR=#333333]ws.Range("I1").Font.Size = 11[/COLOR]
[COLOR=#333333]ws.Range("I1").HorizontalAlignment = xlCenter[/COLOR]

[COLOR=#333333]ws.Range("J1").Font.Bold = True[/COLOR]
[COLOR=#333333]ws.Range("J1").Font.Size = 11[/COLOR]
[COLOR=#333333]ws.Range("J1").HorizontalAlignment = xlCenter[/COLOR]

[COLOR=#333333]ws.Range("K1").Font.Bold = True[/COLOR]
[COLOR=#333333]ws.Range("K1").Font.Size = 11[/COLOR]
[COLOR=#333333]ws.Range("K1").HorizontalAlignment = xlCenter[/COLOR]

[COLOR=#333333]ws.Range("L1").Font.Bold = True[/COLOR]
[COLOR=#333333]ws.Range("L1").Font.Size = 11[/COLOR]
[COLOR=#333333]ws.Range("L1").HorizontalAlignment = xlCenter[/COLOR]

[COLOR=#333333]ws.Range("M1").Font.Bold = True[/COLOR]
[COLOR=#333333]ws.Range("M1").Font.Size = 11[/COLOR]
[COLOR=#333333]ws.Range("M1").HorizontalAlignment = xlCenter[/COLOR]

[COLOR=#333333]ws.Range("N1").Font.Bold = True[/COLOR]
[COLOR=#333333]ws.Range("N1").Font.Size = 11[/COLOR]
[COLOR=#333333]ws.Range("N1").HorizontalAlignment = xlCenter[/COLOR]

[COLOR=#333333]ws.Range("O1").Font.Bold = True[/COLOR]
[COLOR=#333333]ws.Range("O1").Font.Size = 11[/COLOR]
[COLOR=#333333]ws.Range("O1").HorizontalAlignment = xlCenter[/COLOR]

[COLOR=#333333]ws.Range("P1").Font.Bold = True[/COLOR]
[COLOR=#333333]ws.Range("P1").Font.Size = 11[/COLOR]
[COLOR=#333333]ws.Range("P1").HorizontalAlignment = xlCenter[/COLOR]

[COLOR=#333333]ws.Range("Q1").Font.Bold = True[/COLOR]
[COLOR=#333333]ws.Range("Q1").Font.Size = 11[/COLOR]
[COLOR=#333333]ws.Range("Q1").HorizontalAlignment = xlCenter[/COLOR]

[COLOR=#333333]Range("A1:Q1").Select[/COLOR]

[COLOR=#333333]With Selection.Interior[/COLOR]
[COLOR=#333333].Pattern = xlSolid[/COLOR]
[COLOR=#333333].PatternColor = 12632256[/COLOR]
[COLOR=#333333].ThemeColor = xlThemeColorDark1[/COLOR]
[COLOR=#333333].TintAndShade = -0.249977111117893[/COLOR]
[COLOR=#333333].PatternTintAndShade = 0[/COLOR]
[COLOR=#333333]End With[/COLOR]

[COLOR=#333333]With ws.Range("A1:Q1").Borders(xlInsideVertical)[/COLOR]
[COLOR=#333333].LineStyle = xlContinuous[/COLOR]
[COLOR=#333333].ColorIndex = 0[/COLOR]
[COLOR=#333333].TintAndShade = 0[/COLOR]
[COLOR=#333333].Weight = xlMedium[/COLOR]
[COLOR=#333333]End With[/COLOR]
[COLOR=#333333]With ws.Range("A1:Q1").Borders(xlEdgeLeft)[/COLOR]
[COLOR=#333333].LineStyle = xlContinuous[/COLOR]
[COLOR=#333333].ColorIndex = 0[/COLOR]
[COLOR=#333333].TintAndShade = 0[/COLOR]
[COLOR=#333333].Weight = xlMedium[/COLOR]
[COLOR=#333333]End With[/COLOR]
[COLOR=#333333]With ws.Range("A1:Q1").Borders(xlEdgeTop)[/COLOR]
[COLOR=#333333].LineStyle = xlContinuous[/COLOR]
[COLOR=#333333].ColorIndex = 0[/COLOR]
[COLOR=#333333].TintAndShade = 0[/COLOR]
[COLOR=#333333].Weight = xlMedium[/COLOR]
[COLOR=#333333]End With[/COLOR]
[COLOR=#333333]With ws.Range("A1:Q1").Borders(xlEdgeBottom)[/COLOR]
[COLOR=#333333].LineStyle = xlContinuous[/COLOR]
[COLOR=#333333].ColorIndex = 0[/COLOR]
[COLOR=#333333].TintAndShade = 0[/COLOR]
[COLOR=#333333].Weight = xlMedium[/COLOR]
[COLOR=#333333]End With[/COLOR]
[COLOR=#333333]With ws.Range("A1:Q1").Borders(xlEdgeRight)[/COLOR]
[COLOR=#333333].LineStyle = xlContinuous[/COLOR]
[COLOR=#333333].ColorIndex = 0[/COLOR]
[COLOR=#333333].TintAndShade = 0[/COLOR]
[COLOR=#333333].Weight = xlMedium[/COLOR]
[COLOR=#333333]End With[/COLOR]

[COLOR=#333333]End Sub


Access vba code:
[/COLOR]Function ExportIntoExcel()


Dim rs As DAO.Recordset
Dim rsMonthlyPayableReportApVersion As DAO.Recordset
Dim strSQL As String
Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object


Set xlApp = CreateObject("Excel.Application")
    xlApp.Workbooks.Open ("C:\Users\[COLOR=#0000ff]'my name goes here'[/COLOR]\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB")
    Set xlWb = xlApp.Workbooks.Add
    Set xlWs = xlWb.Worksheets("Sheet1")
    xlWs.Name = "[COLOR=#333333]MonthlyPReport[/COLOR]"
 
    xlApp.Visible = True
    xlApp.UserControl = True
    xlApp.ScreenUpdating = False
    
xlWs.cells(1, 1).Value = "Vendor #"
xlWs.cells(1, 2).Value = "Site"
xlWs.cells(1, 3).Value = "Invoice"
xlWs.cells(1, 4).Value = "Invoice Date"
xlWs.cells(1, 5).Value = "Amount"
xlWs.cells(1, 6).Value = "Legal Entity"
xlWs.cells(1, 7).Value = "Cost Center"
xlWs.cells(1, 8).Value = "Natural Account"
xlWs.cells(1, 9).Value = "Contract ID"
xlWs.cells(1, 10).Value = "Vendor Name"
xlWs.cells(1, 11).Value = "Street Address1"
xlWs.cells(1, 12).Value = "Street Address2"
xlWs.cells(1, 13).Value = "City"
xlWs.cells(1, 14).Value = "State Code"
xlWs.cells(1, 15).Value = "Zip"
xlWs.cells(1, 16).Value = "E-mail"
xlWs.cells(1, 17).Value = "Phone"


    strSQL = "SELECT * FROM [MonthlyPayableReportApVersion];"
    Set rsMonthlyPayableReportApVersion = CurrentDb.OpenRecordset(strSQL)


xlWs.cells(2, 1).CopyFromRecordset rsMonthlyPayableReportApVersion




         
xlApp.Run "PERSONAL.XLSB![COLOR=#333333]FinalReport[/COLOR]"


xlApp.ScreenUpdating = True


End Function
Thank you.
 
Last edited by a moderator:
Upvote 0
Mark858,
To answer your second question, the error highlights the following part of the code:

Set ws = Application.ActiveWorkbook.Sheets("MonthlyPReport")
 
Upvote 0
Check that the spelling of MonthlyPReport is exact on the worksheet and that there are no leading or trailing spaces.
 
Upvote 0
Thank you for your reply Mark 858,
I did check it right now and it matches both sides, no trailing/leading spaces in it.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
VBA to check if worksheet exists
Replies
8
Views
345
bdt
B
H
  • Question Question
Save and Close Excel after a 15min inactivity.
Replies
4
Views
784
Hopey87
H
L
  • Solved
Would like to make edits to VBA Calendar Code
Replies
8
Views
929
Legacy 352679
L
L
  • Locked
  • Question Question
Help Cleaning Up Code
Replies
2
Views
600
RoryA
RoryA
K
  • Solved
create a button that do actions
2 3
Replies
22
Views
1K
kerm007
K

Forum statistics

Threads
1,224,823
Messages
6,181,173
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top