I am creating a form that selects data based on a user selected model type, flange type, and a range of line sizes. This hasn't been a problem, I am able to get the correct report from the form thus far.
My issue is the button I am using to export the report to excel. I want the button to save the file with a filename based on the parameters selected in the form, then open the excel file, delete empty columns, move the table a bit (just so the borders are visible), and then add the lines to make it look like a nice table. I am at the point where this works most of the time when I click the button. It seems to be not working properly whenever excel is already open, especially if there is an excel file that is already open which was created by this button. What really gets me is that it'll give a "Method 'Range' of object '_Global' " error sometimes but if I close the excel file and try again it will work no problem.
Keep in mind that I am a complete novice when it comes to Access and VBA in general - everything you see here I have taught myself in the last week. So feel free to treat me like a child, I am learning each step of the way.
I have omitted all the code concerning the option lists on the form. This is just the button. -
Option Compare Database
-
Option Explicit
-
-
Private objExcel As Excel.Application
-
Private xlwb As Excel.Workbook
-
Private xlws As Excel.Worksheet
-
-
Private Sub CreateTable_Click()
-
-
Dim filename As String
-
Dim lastrow As Long
-
Dim lastcolumn As Long
-
Dim i As Long
-
Dim flange As String
-
-
Select Case FlangeType.Value
-
Case 1
-
flange = "SO"
-
Case 2
-
flange = "WN"
-
Case 3
-
flange = "WA"
-
End Select
-
-
'set filename based on filter of table and save as excel sheet
-
filename = "Type1drawing_" & DrawingType.Value & "_" & flange & "flange_" & minsize.Value & "_to_" & maxsize.Value & ".xls"
-
DoCmd.OutputTo acOutputReport, "rptType1Drawing", "Excel97-Excel2003Workbook(*.xls)", filename, False, "", 0, acExportQualityPrint
-
-
'set an excel object and open the saved file to format correctly
-
Set objExcel = New Excel.Application
-
objExcel.Visible = True
-
Set xlwb = objExcel.Workbooks.Open(filename)
-
Set xlws = objExcel.Worksheets("Type 1 Drawing Maker")
-
-
objExcel.ScreenUpdating = False
-
objExcel.Calculation = xlCalculationManual
-
xlws.Activate
-
-
With xlws
-
lastrow = Range("A1").End(xlDown).Row
-
lastcolumn = Range("A1").End(xlToRight).Column
-
i = lastcolumn
-
-
'delete empty columns (non-available liners)
-
Do Until i = 0
-
If WorksheetFunction.CountA(Range(Cells(2, i), Cells(lastrow, i))) = 0 Then 'use row 2 so the headers aren't counted
-
Columns(i).Delete
-
End If
-
i = i - 1
-
Loop
-
'move for better view and then format
-
Range("A1", Cells(lastrow, lastcolumn)).Cut
-
Range("b2").Select
-
ActiveSheet.Paste
-
-
-
'update the last column and row
-
lastcolumn = Range("b2").End(xlToRight).Column
-
lastrow = Range("b2").End(xlDown).Row
-
-
Range("b2", Cells(lastrow, lastcolumn)).Select
-
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
-
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
-
With Selection.Borders(xlEdgeLeft)
-
.LineStyle = xlContinuous
-
.Weight = xlThin
-
.ColorIndex = xlAutomatic
-
End With
-
With Selection.Borders(xlEdgeTop)
-
.LineStyle = xlContinuous
-
.Weight = xlThin
-
.ColorIndex = xlAutomatic
-
End With
-
With Selection.Borders(xlEdgeBottom)
-
.LineStyle = xlContinuous
-
.Weight = xlThin
-
.ColorIndex = xlAutomatic
-
End With
-
With Selection.Borders(xlEdgeRight)
-
.LineStyle = xlContinuous
-
.Weight = xlThin
-
.ColorIndex = xlAutomatic
-
End With
-
With Selection.Borders(xlInsideVertical)
-
.LineStyle = xlContinuous
-
.Weight = xlThin
-
.ColorIndex = xlAutomatic
-
End With
-
With Selection.Borders(xlInsideHorizontal)
-
.LineStyle = xlContinuous
-
.Weight = xlThin
-
.ColorIndex = xlAutomatic
-
End With
-
With Selection
-
.RowHeight = 14
-
.VerticalAlignment = xlCenter
-
End With
-
With Selection.Font
-
.ColorIndex = xlAutomatic
-
.TintAndShade = 0
-
End With
-
Columns.AutoFit
-
Cells(1, 1).Select
-
End With
-
-
-
objExcel.ScreenUpdating = True
-
objExcel.Calculation = xlCalculationAutomatic
-
xlwb.Save
-
Set objExcel = Nothing
-
Set xlwb = Nothing
-
Set xlws = Nothing
-
-
End Sub
-
Hi
When automaton Excel (or any office application) in Access etc you need to explicitly refer to the Excel object because, If you do not, then the application running he code makes assumption as ti what you are referring to, which is not good.
I am a bit surprised this work at all, but i have had a similar experience with Word in my 'younger' days.
In your code you do not qualify any range or cell object so access will not now you have opened a new file and assume it is the original file (I think). There is a certain amount of supposition in these comments. However if you use explicit references then there is no ambiguity and the program knows exactly what you indended.
So, below I have moded a part of you code to indicate how I would do this - With xlws
-
lastrow = .Range("A1").End(xlDown).Row
-
lastcolumn = .Range("A1").End(xlToRight).Column
-
i = lastcolumn
-
-
'delete empty columns (non-available liners)
-
Do Until i = 0
-
If objExcel.WorksheetFunction.CountA(.Range(.Cells(2, i), .Cells(lastrow, i))) = 0 Then 'use row 2 so the headers aren't counted
-
.Columns(i).Delete
-
End If
-
i = i - 1
-
Loop
-
'move for better view and then format
-
.Range("A1", xlws.Cells(lastrow, lastcolumn)).Cut
-
.Range("b2").Select
-
.Paste
-
-
-
'update the last column and row
-
lastcolumn = .Range("b2").End(xlToRight).Column
-
lastrow = .Range("b2").End(xlDown).Row
-
-
'FORMAT RANGE
-
With .Range("b2", .Cells(lastrow, lastcolumn))
-
.RowHeight = 14
-
.VerticalAlignment = xlCenter
-
.Borders(xlDiagonalDown).LineStyle = xlNone
-
.Borders(xlDiagonalUp).LineStyle = xlNone
-
With .Borders(xlEdgeLeft)
-
.LineStyle = xlContinuous
-
.Weight = xlThin
-
.ColorIndex = xlAutomatic
-
End With
-
With .Borders(xlEdgeTop)
-
.LineStyle = xlContinuous
-
.Weight = xlThin
-
.ColorIndex = xlAutomatic
-
End With
-
With .Borders(xlEdgeBottom)
-
.LineStyle = xlContinuous
-
.Weight = xlThin
-
.ColorIndex = xlAutomatic
-
End With
-
With .Borders(xlEdgeRight)
-
.LineStyle = xlContinuous
-
.Weight = xlThin
-
.ColorIndex = xlAutomatic
-
End With
-
With .Borders(xlInsideVertical)
-
.LineStyle = xlContinuous
-
.Weight = xlThin
-
.ColorIndex = xlAutomatic
-
End With
-
With .Borders(xlInsideHorizontal)
-
.LineStyle = xlContinuous
-
.Weight = xlThin
-
.ColorIndex = xlAutomatic
-
End With
-
With .Font
-
.ColorIndex = xlAutomatic
-
.TintAndShade = 0
-
End With
-
End With
-
-
.Columns.AutoFit
-
.Cells(1, 1).Select
-
End With
-
In short, in this code block, every time you use the Range or Cell obects you need a period/full stop in front of it.
You will note I have remove all reference to the 'With Selection' and used 'With .Range("b2", .Cells(lastrow, lastcolumn))' directly as you do not deed to select the range to refer to it.
Note, I also think the 'WorksheetFunction' if a part of the application object ie. objExcel in this case.
As I have/cannot run the code I may have missed some object referrals.
HTH
12 4854
You could create a "template" workbook with all your formatting already done. Then make a copy of the workbook and import the data into it. That way you don't have to code all the formatting work and makes it easier to change formatting later down the road.
The issue is that I will not be the only perosn accessing this database. So the code needs to produce the Excel sheet in the correct format from any computer that has Excel and Access on it. So the Access VBA needs to complete the entire task if I'm not mistaken.
Can you not put the excel template in a central network location that everyone has access to? Or include the excel template with each person's copy of the Access database?
I don't know, I'm just a summer intern. I suppose that is probably possible but shouldn't it be possible to get Access VBA to control Excel anyways?
NeoPa 32,556
Expert Mod 16PB
I just deleted your question in the article thread. It's a perfectly valid question - but article threads aren't there for asking questions in.
If you repeat it here I will see it and can respond. Alternatively, you can post it in a new thread if you prefer. I'm more likely to see it from in here though ;-)
Strangely, one of my first ever tasks myself in Access was to produce a procedure called "Prettify()" which basically did a similar job to what you're asking about. That was a few years ago now mind.
Well the question basically pertained to the one this thread is about. I don't quite understand what part of this code is not properly referencing excel.
Hi
When automaton Excel (or any office application) in Access etc you need to explicitly refer to the Excel object because, If you do not, then the application running he code makes assumption as ti what you are referring to, which is not good.
I am a bit surprised this work at all, but i have had a similar experience with Word in my 'younger' days.
In your code you do not qualify any range or cell object so access will not now you have opened a new file and assume it is the original file (I think). There is a certain amount of supposition in these comments. However if you use explicit references then there is no ambiguity and the program knows exactly what you indended.
So, below I have moded a part of you code to indicate how I would do this - With xlws
-
lastrow = .Range("A1").End(xlDown).Row
-
lastcolumn = .Range("A1").End(xlToRight).Column
-
i = lastcolumn
-
-
'delete empty columns (non-available liners)
-
Do Until i = 0
-
If objExcel.WorksheetFunction.CountA(.Range(.Cells(2, i), .Cells(lastrow, i))) = 0 Then 'use row 2 so the headers aren't counted
-
.Columns(i).Delete
-
End If
-
i = i - 1
-
Loop
-
'move for better view and then format
-
.Range("A1", xlws.Cells(lastrow, lastcolumn)).Cut
-
.Range("b2").Select
-
.Paste
-
-
-
'update the last column and row
-
lastcolumn = .Range("b2").End(xlToRight).Column
-
lastrow = .Range("b2").End(xlDown).Row
-
-
'FORMAT RANGE
-
With .Range("b2", .Cells(lastrow, lastcolumn))
-
.RowHeight = 14
-
.VerticalAlignment = xlCenter
-
.Borders(xlDiagonalDown).LineStyle = xlNone
-
.Borders(xlDiagonalUp).LineStyle = xlNone
-
With .Borders(xlEdgeLeft)
-
.LineStyle = xlContinuous
-
.Weight = xlThin
-
.ColorIndex = xlAutomatic
-
End With
-
With .Borders(xlEdgeTop)
-
.LineStyle = xlContinuous
-
.Weight = xlThin
-
.ColorIndex = xlAutomatic
-
End With
-
With .Borders(xlEdgeBottom)
-
.LineStyle = xlContinuous
-
.Weight = xlThin
-
.ColorIndex = xlAutomatic
-
End With
-
With .Borders(xlEdgeRight)
-
.LineStyle = xlContinuous
-
.Weight = xlThin
-
.ColorIndex = xlAutomatic
-
End With
-
With .Borders(xlInsideVertical)
-
.LineStyle = xlContinuous
-
.Weight = xlThin
-
.ColorIndex = xlAutomatic
-
End With
-
With .Borders(xlInsideHorizontal)
-
.LineStyle = xlContinuous
-
.Weight = xlThin
-
.ColorIndex = xlAutomatic
-
End With
-
With .Font
-
.ColorIndex = xlAutomatic
-
.TintAndShade = 0
-
End With
-
End With
-
-
.Columns.AutoFit
-
.Cells(1, 1).Select
-
End With
-
In short, in this code block, every time you use the Range or Cell obects you need a period/full stop in front of it.
You will note I have remove all reference to the 'With Selection' and used 'With .Range("b2", .Cells(lastrow, lastcolumn))' directly as you do not deed to select the range to refer to it.
Note, I also think the 'WorksheetFunction' if a part of the application object ie. objExcel in this case.
As I have/cannot run the code I may have missed some object referrals.
HTH
This seems to have fixed my problem of using the button two times in a row. Thank you, I didn't realize that putting a "." infront of everything was necessary. Live and learn!
NeoPa 32,556
Expert Mod 16PB ghallan:
I didn't realize that putting a "." infront of everything was necessary.
This is related to using With in your code. generally a good idea and very much to be recommended. ghallan:
I don't quite understand what part of this code is not properly referencing excel.
Well, you have to do a bit of work on that yourself first. Just dumping code (114 lines) for others to scan through and find your problems without any clues isn't a great idea. None of us has the code in a working project so that would be many times more work for others than the basics would be for you.
Start by determining what is failing where in your code. Identify which reference isn't working as expected. Error messages and line numbers are important here. When you have that very basic information then ask a specific question related to that and we'll do what we can to help you.
You may no longer need the help for this question, but please remember the idea for your next ones. It will make it much easier to provide you with a quicker, and more helpful, response.
Hi again
Just a little more info on this subject.
I indicated my surprise that this code work at all.
It has now dawned on me that your code used early binding with a reference to the Excel library; therefore the compiler would eventually fine the object and methods after first searching the Access library.
I always use late binding (to eliminate DLL problems in different version of Office) which does not have a reference to the Excel library, in which case it would not have compiled as it was, and explicit references to the object variables would be required at all times as indicated.
The first time your code runs it assigns the unqualified objects to the first file, but this assignment is not changed in subsequently passes and is still looking for this first file, which is closed or not the active file/worksheet, I think.
So, as NeoPa said, it is always best, when using automation, to reference the objects/methods explicitly.
Hope that help.
MTB
Thanks guys. This is my first time using VBA for a project and my first time using a website like this for help. All the pointers are greatly appreciated.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Ali Eghtebas |
last post by:
Hi,
I tried this demo application in here (watch for any line breaks in the
URL):
(http://support.crystaldecisions.com/communityCS/FilesAndUpdates/vbnet_win_a
dodotnet.exe.asp
When exporting...
|
by: Paul |
last post by:
I have written code to transfer a table to an Excel document.
However, I do not know the code to add some "bells and whistles" to
the document. Can someone direct me to a reference on the Internet...
|
by: mik18 |
last post by:
I'm having trouble with exporting reports to Word in the rtf format and
I'm hoping someone has a solution. The reports are losing their
formats. Not all the formating is lost but some is and of...
|
by: Mustufa Baig |
last post by:
I have an ASP.NET website where I am showing off crystal reports to users by
exporting them to pdf format. Following is the code:
----------------
1 Private Sub ExportReport()
2 Dim oStream...
|
by: jpatchak |
last post by:
Hi, I have kind of a strange problem. I have a report that I need to export to excel. The report opens fine in Access and when I export it or click on "Analyze It with Microsoft Office Excel," I...
|
by: Kathy |
last post by:
I have a number of reports in a MS Access application that have a logo
picture embedded in the report. When I run the report and choose File,
then Export to Excel, I get an error indicating...
|
by: Roo06 |
last post by:
I am trying to open a spreadsheet from Acess based on a value in a textbox. I don't how to create the reference to the path and file name and was wondering if someone could help. I am new to VBA and...
|
by: jennwilson |
last post by:
Access 2000 - I have developed a database that houses patient information. The patient information must be sent in an excel file to another company department. I know how to export my data to Excel...
|
by: Tempalli |
last post by:
I am exporting the data from ms access to excel where the error displays as Run-time error -2147467259(800004005)
Method 'Copyfromrecordset' of object 'Range' faild.
...
|
by: colintis |
last post by:
Before asking the question I've been digging a hole that is deep enough to bury myself in google....Most of the results are simply importing the excel file to table with the same table structure.
...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |