Mar 06, 2009 iSpreadsheet is a free, simple to use yet powerfull mobile spreadsheet Application for your iPhone & iPad. Quickly create beautiful spreadsheets that are compatible with major office solutions such as Excel and Numbers. ISpreadsheet is built from the ground up for iPhone and iPad so it runs very f. If you've created an Excel spreadsheet that performs calculations, you can create an executable program using the XCell Compiler utility. This will allow you to share the spreadsheet as a program file, obscuring the underlying formulas that make the calculations in binary format.
After exporting a query from Access as an.xls file, I want to then open the.xls file, and further tailor it using VBA. The catch is that I want to run the VBA code from Access to minimize timing errors from other automation programs.
Using the linked thread, I discovered that I can open the Excel file from Access VBA. Function lixiang Dim XL As Object Set XL = CreateObject('Excel.Application') XL.Workbooks.Open 'c:/access files/excel vba test/query3.xls' XL.Visible = True 'XL.Run 'congratsmacro.congrats' However, I can't run the 'Congrats' subprocedure from my personal.xlsb workbook, because it doesn't appear at all when opening Excel files certain ways from other programs. The macros dialog box is completely blank. If possible, I'll simply paste the 'Congrats' subprocedure into my Access VBA, but I don't know how to run it to effect the Excel spreadsheet from Access. Add-ins and startup programs are not loaded when using Excel via automation.
You could do XL.Workbooks.Open XL.StartupPath & ' Personal.xlsb', ReadOnly:=True Or you could run Excel via the Shell command instead of via automation. Or you could copy the procedure to Access and modify it. It should work if you put With XL End With around the code and put a. Before every Excel object reference e.g. Sub Transplanted With XL.Worksheets('Sheet1').Activate.Range('A1').Select.ActiveCell.Value = 123 End With End Sub But the simplest of the 3 is the first.
Bill Manville. Excel MVP, Oxford, England.
I wasn't able to get the simple first one above to work, but this is to a much greater delight! Inside the function from my first post: Function lixiang Dim XL As Object Set XL = CreateObject('Excel.Application') XL.Workbooks.Open 'c:/access files/excel vba test/query3.xls' XL.Visible = True 'I added the following below on the very next line With XL call procedurebelow End With. I then copied and pasted the complex Subroutine I have stored in Excel into this.accdb's VBA as a seperate Subroutine, which was called by the 'call procedurebelow' command. This worked perfectly!!!!! After exporting a query from Access as an.xls file, I want to then open the.xls file, and further tailor it using VBA.
The catch is that I want to run the VBA code from Access to minimize timing errors from other automation programs. Using the linked thread, I discovered that I can open the Excel file from Access VBA. Function lixiang Dim XL As Object Set XL = CreateObject('Excel.Application') XL.Workbooks.Open 'c:/access files/excel vba test/query3.xls' XL.Visible = True 'XL.Run 'congratsmacro.congrats' However, I can't run the 'Congrats' subprocedure from my personal.xlsb workbook, because it doesn't appear at all when opening Excel files certain ways from other programs. The macros dialog box is completely blank. If possible, I'll simply paste the 'Congrats' subprocedure into my Access VBA, but I don't know how to run it to effect the Excel spreadsheet from Access. Add-ins and startup programs are not loaded when using Excel via automation. You could do XL.Workbooks.Open XL.StartupPath & ' Personal.xlsb', ReadOnly:=True Or you could run Excel via the Shell command instead of via automation.
Or you could copy the procedure to Access and modify it. It should work if you put With XL End With around the code and put a. Before every Excel object reference e.g. Sub Transplanted With XL.Worksheets('Sheet1').Activate.Range('A1').Select.ActiveCell.Value = 123 End With End Sub But the simplest of the 3 is the first. Bill Manville.
Excel MVP, Oxford, England. I wasn't able to get the simple first one above to work, but this is to a much greater delight!
Inside the function from my first post: Function lixiang Dim XL As Object Set XL = CreateObject('Excel.Application') XL.Workbooks.Open 'c:/access files/excel vba test/query3.xls' XL.Visible = True 'I added the following below on the very next line With XL call procedurebelow End With. I then copied and pasted the complex Subroutine I have stored in Excel into this.accdb's VBA as a seperate Subroutine, which was called by the 'call procedurebelow' command. This worked perfectly!!!!!
I am quite surprised that the Excel macro worked perfectly in Access with no change. I would have expected you to have to make XL a module level variable and put the With XL. End With in the procedurebelow and precede Excel objects within procedurebelow with.
As I indicated before. I guess you must have a Reference to Excel's object model and you are lucky that the only global objects of Excel your code uses are ones that Access does not have itself (Selection, ActiveCell, ActiveWorkbook, ThisWorkbook). Any use of Application.something in procedurebelow would probably have given a compile error because Application would be interpreted as meaning the Access Application object, not Excel's. Bill Manville. Excel MVP, Oxford, England. You needed the reference to the Excel object library for Set objXL = New Excel.Application Having the reference would also avoid the need for some changes to the procedure you copied over from Excel in that it makes Excel's globals such as ActiveSheet, ActiveCell, Selection. Known to Access.
However, where there is a clash of globals, such as Application which exists in both Access and Excel's object models the Access one would 'win' and things like Application.ScreenUpdating = False would give run-time errors. They would need changing to objXL.ScreenUpdating = False or With objXL.Application.ScreenUpdating = False. End With For that reason I still think the safest route would be to modify the called code making sure that all Excel objects were referenced from the Excel application object. Bill Manville. Excel MVP, Oxford, England. Now what if I have my Access vba:. Open 2 independant workbooks.
Copy data from A.(a1:a1000) to B.(a5:a1006). Copy/Paste Values on certain columns in B, and then with additional formatting on B. Close B. Close A I'm not sure how many With objXLA. And With objXLB's.
S i'll need, or if a simple objXLA/objXLA.activate would suffice? Fortunately, while both sheets are open, the code will be as simple as: A.range(a1:a1000).copy B.range(a6).paste B.Columns(B:C).paste values. Every use of an Excel object should reference back to the Excel application object you have created in one way or another. Your code could be something like this: Dim objXL As Object Set objXL = CreateObject('Excel.Application') With objXL.Workbooks.Open(strPath & ' A.xls').Workbooks.Open(strPath & ' B.xls').Workbooks('A.xls').Sheets('Sheet1').Range('A1:A1000').Copy.Workbooks('B.xls').Sheets('Sheet1').Range('A6') ' don't know what you are wanting to paste values to B:C.Workbooks('A.xls').Close False.Workbooks('B.xls').Close True ' save changes.Quit End With Set objXL = Nothing So, there is a With objXL block around the Excel-related code. Before each Excel property or method that would be referenced from the Application object in Excel VBA (explicitly as in the case of Quit or implicitly in the case of Workbooks). Alternatively you could (with a reference to the Excel object model) use variables to reference the workbook objects, as in Dim objXL As Excel.Application Dim wbA As Excel.Workbook Dim wbB As Excel.Workbook Set objXL = New Excel.Application With objXL Set wbA =.Workbooks.Open(strPath & ' A.xls') Set wbB =.Workbooks.Open(strPath & ' B.xls') wbA.Sheets('Sheet1').Range('A1:A1000').Copy wbB.Sheets('Sheet1').Range('A6') ' don't know what you are wanting to paste values to B:C wbA.Close False wbB.Close True ' save changes.Quit End With Set objXL = Nothing Set wbA = Nothing Set wbB = Nothing No. Is required before wbA because wbA is an Access VBA object variable referencing an Excel object.
Bill Manville. Excel MVP, Oxford, England.