473,325 Members | 2,828 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Controlling Excel through Access VBA

124 100+
For some reason, the following code errors out at
.Range(Selection, Selection.End(xlToRight)).Select and say with object not set. I copied the VBA from an Excel macro, but know very little about Excel. I basically cut and pasted the code into Access and used the Worksheet object for the with block. Any help is appreciated.
Expand|Select|Wrap|Line Numbers
  1.     Dim objExcelApp As Excel.Application
  2.     Dim wb As Excel.Workbook
  3.  
  4.     Set objExcelApp = New Excel.Application
  5.     Dim ws As Worksheet
  6.     Set wb = objExcelApp.Workbooks.Add("D:\FilePath\Trans_8-17-2016.xls")
  7.     Set ws = wb.Sheets(1)
  8.  
  9.     With ws
  10.     .Columns("A:A").Select
  11.     .Range(Selection, Selection.End(xlToRight)).Select
  12.     .Columns("A:BR").EntireColumn.AutoFit
  13.     With Selection
  14.         .HorizontalAlignment = xlGeneral
  15.         .VerticalAlignment = xlBottom
  16.         .WrapText = True
  17.         .Orientation = 0
  18.         .AddIndent = False
  19.         .IndentLevel = 0
  20.         .ShrinkToFit = False
  21.         .ReadingOrder = xlContext
  22.         .MergeCells = False
  23.     End With
  24.     .Columns("H:H").Select
  25.     .Range(Selection, Selection.End(xlToRight)).Select
  26.     .Columns("H:I").Select
  27.     Selection.NumberFormat = "[$-409]h:mm AM/PM;@"
  28.     .Columns("AN:AO").Select
  29.     Selection.NumberFormat = "[$-409]h:mm AM/PM;@"
  30.     End With
  31.  
  32.     wb.SaveCopyAs "D:\FilePath\Trans_Formatted_8-19-2016.xls"
  33.  
  34.     'Close the workbook
  35.     wb.Close SaveChanges:=False
  36.     Set wb = Nothing
  37.     Set objExcelApp = Nothing
  38.  
Aug 19 '16 #1
4 2337
ADezii
8,834 Expert 8TB
I 'think' you need to fully qualify the Selection Object with the Excel Application Object, as in:
Expand|Select|Wrap|Line Numbers
  1. Dim objExcelApp As Excel.Application
  2. Dim wb As Excel.Workbook
  3. Dim ws As Worksheet
  4.  
  5. Set objExcelApp = New Excel.Application
  6. Set wb = objExcelApp.Workbooks.Add("D:\FilePath\Trans_8-17-2016.xls")
  7. Set wb = objExcelApp.Workbooks.Add("C:\Test\File1.xlsx")
  8.  
  9. Set ws = wb.Sheets(1)
  10.  
  11. With ws
  12.   .Columns("A:A").Select
  13.   .Range(objExcelApp.Selection, objExcelApp.Selection.End(xlToRight)).Select
  14.   .Columns("A:BR").EntireColumn.AutoFit
  15.  
  16.   With Selection
  17.     .HorizontalAlignment = xlGeneral
  18.     .VerticalAlignment = xlBottom
  19.     .WrapText = True
  20.     .Orientation = 0
  21.     .AddIndent = False
  22.     .IndentLevel = 0
  23.     .ShrinkToFit = False
  24.     .ReadingOrder = xlContext
  25.     .MergeCells = False
  26.   End With
  27.     .Columns("H:H").Select
  28.     .Range(objExcelApp.Selection, objExcelApp.Selection.End(xlToRight)).Select
  29.     .Columns("H:I").Select
  30.      objExcelApp.Selection.NumberFormat = "[$-409]h:mm AM/PM;@"
  31.     .Columns("AN:AO").Select
  32.      objExcelApp.Selection.NumberFormat = "[$-409]h:mm AM/PM;@"
  33. End With
  34.  
  35. wb.SaveCopyAs "D:\FilePath\Trans_8-17-2016.xls"
  36.  
  37. 'Close the workbook
  38. wb.Close SaveChanges:=False
  39. Set wb = Nothing
  40. Set objExcelApp = Nothing
Aug 19 '16 #2
BikeToWork
124 100+
Thanks for the reply, ADezi. You were absolutely right. I don't know Excel VBA very well. Now, I changed my code to the following and for some reason there is an instance of Excel running after the Access code runs. Can you see what I am doing wrong?

Expand|Select|Wrap|Line Numbers
  1. On Error GoTo BAIL
  2.  
  3.  
  4. Dim objExcelApp As Excel.Application
  5. Dim wb As Excel.Workbook
  6. Dim ws As Worksheet
  7.  
  8. Set objExcelApp = New Excel.Application
  9. Set wb = objExcelApp.Workbooks.Add("D:\Databases\ALS_LAB\REPORTS\Trans_8-17-2016.xls")
  10.  
  11.  
  12. Set ws = wb.Sheets(1)
  13.  
  14. With ws
  15.   .Columns("A:A").Select
  16.   .Range(objExcelApp.Selection, objExcelApp.Selection.End(xlToRight)).Select
  17.   .Columns("A:BR").EntireColumn.AutoFit
  18.  
  19.   With Selection
  20.     .HorizontalAlignment = xlGeneral
  21.     .VerticalAlignment = xlBottom
  22.     .WrapText = True
  23.     .Orientation = 0
  24.     .AddIndent = False
  25.     .IndentLevel = 0
  26.     .ShrinkToFit = False
  27.     .ReadingOrder = xlContext
  28.     .MergeCells = False
  29.   End With
  30.     .Columns("H:H").Select
  31.     .Range(objExcelApp.Selection, objExcelApp.Selection.End(xlToRight)).Select
  32.     .Columns("H:I").Select
  33.      objExcelApp.Selection.NumberFormat = "[$-409]h:mm AM/PM;@"
  34.     .Columns("AN:AO").Select
  35.      objExcelApp.Selection.NumberFormat = "[$-409]h:mm AM/PM;@"
  36. End With
  37.  
  38. wb.SaveCopyAs "D:\Databases\ALS_LAB\Reports\Trans_Formatted_8-19-2016.xls"
  39.  
  40.  
  41. 'Close the workbook
  42.     wb.Close SaveChanges:=False
  43.     Set wb = Nothing
  44.  
  45.  
  46.     Set objExcelApp = Nothing
  47.     Set ws = Nothing
  48.  
  49. Leave:
  50.     Exit Function
  51.  
  52. BAIL:
  53.     Set ws = Nothing
  54.     Set wb = Nothing
  55.     Set objExcelApp = Nothing
  56.     MsgBox Err.Description & vbCrLf & Err.Number
  57.     Resume Leave
  58.  
Aug 19 '16 #3
ADezii
8,834 Expert 8TB
  1. It may be a left-over Instance of Excel, Reboot and run the Code again.
  2. If the above doesn't work, try:
    Expand|Select|Wrap|Line Numbers
    1. objExcelApp.Quit
    2. Set objExcelApp = Nothing
Aug 19 '16 #4
TheSmileyCoder
2,322 Expert Mod 2GB
When your code reaches this line
Expand|Select|Wrap|Line Numbers
  1.   With Selection
Access recognizes that Selection is a method in the Excel library. However you have not specifed which instance of excel in which to use this method. So Access tries to help and generates a hidden excel instance on the fly, from which to execute the Selection method.

Think of it like this is it helps. Imagine having 3 cars
Expand|Select|Wrap|Line Numbers
  1. Dim Car1 as Car
  2. Dim Car2 as Car
  3. Dim Car3 as Car
The following code would make sense:
Expand|Select|Wrap|Line Numbers
  1. Car1.Accelerate
  2. Car1.Brake
But the below code, by itself does not make sense, which car are we talking about?
Expand|Select|Wrap|Line Numbers
  1. Accelerate
  2. Brake
So any time you use code from the excel library you need to fully qualify it. Since we already have objExcelApp defined, we can use that
Expand|Select|Wrap|Line Numbers
  1. With objExcelApp.Selection
When you get leftover instances of excel is usually
A) Unqualified references such as the above
B) Errors in your code, that results in abnormal termination of code.
Aug 20 '16 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Lynn A. | last post by:
I have to perform an import from an excel file to Access. The file comes originally as a csv file. I have to delete some rows and columns, then change the formatting of some columns. I am using...
0
by: Jack | last post by:
Windows 2K Pro Access/Excel 2003 Hi there, I have a large number of password protected Excel Workbooks. The files are protected by code that runs when the workbook opens. The code asks the...
8
by: Charley Kyd | last post by:
I'd like to learn how to use VS.NET as though it were Excel VBA, with the goal of creating a compiled Excel addin. That is, I'd like... ....all forms to belong to the Excel environment. ....the...
0
by: Al Christians | last post by:
I'm starting to test a python application that creates an Excel workbook, then fills in values for some cells and formulas for other cells. The formulas involve circular references, which will...
4
by: BFoxDDS | last post by:
i'm sure this is simple to do, but i can't find the answer in the search engines. i have an access db with 400,000 records. i produce queries of 5,000 records at a time exported into an excel...
7
by: farhaaad | last post by:
Hi everybody, I just wanted to know if i can make a form in excel (the same as access forms), so when i enter data in excel form it goes to a table in access ? I mean when i enter a value in a...
1
by: rweston | last post by:
I am trying to open an MS Excel file and perform a find/replace function, all using VBA within MS Access. I keep getting a "Subscript out of range." error. Do you know why I am getting this error? ...
0
by: TrevoriousD | last post by:
hi I have created a data link from access query to excel. I use excel pull data from access in order to do stats. the excel workbook always stays in the same folder as the access db. I want to be...
2
by: roshaan | last post by:
i m very new to asp and working on a project using asp and access not asp.net………. I have a small application in which after login, user can upload .xls file, then the record from this excel file...
2
by: DavidAustin | last post by:
Hi all, I am trying to export query to Excel from Access, manipulate it into a pivot table, format the pivot table and then export into a word document... I have some code that I have...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.