98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Access VBA Training and help » Using Access VBA, i would like to control an Excel Workbook
Using Access VBA, i would like to control an Excel Workbook
Resolved · High Priority · Version 2016
Thu 9 May 2019, 15:31Delegate Manny said...
Manny has attended:
Access Intermediate course
Access Advanced course
Using Access VBA, i would like to control an Excel Workbook
Using Access VBA, i would like to control an Excel workbook and split this into 3 seperate workbooks based on a column.
Steps.
1. Download source file from FTP site (done)
2. Using the source file, split the file into 3 seperate workbooks and save these down.
3. kill the source file (done)
I have tried lots of different codes, but really having no luck cracking this one.
Please may you give me a hand
Manny
Fri 10 May 2019, 10:10Trainer Claire said...
RE: Using Access VBA, i would like to control an Excel Workbook
Hi Manny,
I know Jens is busy today so I will have a go at helping on this. I don't train on Access VBA but I do on Excel VBA.
The first thing to check is that you have the correct Libraries loaded to allow Access to talk to Excel.
Have you got that in place?
How far have you got with this? What specific problems are you having?
thanks
Claire
Tue 14 May 2019, 17:28Delegate Manny said...
RE: Using Access VBA, i would like to control an Excel Workbook
Hi Claire,
So i have tried a few things.
Dim appExcel As Excel.Application
Dim myWorkbook As Excel.Workbook
Dim ws As Excel.Worksheet
Set ws = CreateObject("Excel.Application").Workbooks.Open("STRINGPATH").Sheets(1)
... do stuff...
myWorkbook.Close
appExcel.Quit
Set appExcel = Nothing
Set myWorkbook = Nothing
Set ws = Nothing
when i come to delete the file i was using by Kill("STRING PATH") - it says permission denied. i gather because an instance of excel is still open. but unsure how to close it
Wed 15 May 2019, 09:54Trainer Claire said...
RE: Using Access VBA, i would like to control an Excel Workbook
Hi Manny,
I think Excel being open should be OK. My first thought is do you have delete permissions for the place the file is saved?
I'm going to ask Jens to take a look at this as I haven't worked with Access VBA for some time. Hopefully this is something he'll point out straight away :)
thanks
Claire
Wed 15 May 2019, 10:24Trainer Jens said...
RE: Using Access VBA, i would like to control an Excel Workbook
Hi Manny,
It is a very common error and there is a lot of different things which give you the problem.
I guess you already have tried yourself by googling the issue.
The FileSystemObject method is for controlling the files and folders, and I suggest that you try this. You will have to activate a script library and then you will find that you have a lot of options to control files.
If you follow the hyperlink below you will find a very good website explaining the FileSytemObject.
https://wellsr.com/vba/2018/excel/introduction-to-the-vba-filesystemobject/
I will not know if this is the solution, because it could be a security issue in your system
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 16 May 2019, 09:01Delegate Manny said...
RE: Using Access VBA, i would like to control an Excel Workbook
Hi Claire/Jens,
I shall check the link, see what i can do. The excel VBA works normally in Excel, but just not access. I am not a big fan of controlling excel with Access, it is fiddly!
Essentially what i want to do is split an incoming excel file (1 Excel file containing 3 funds). The file is sent via FTP, Access picks up the file and puts it to our shared Drive. The next step is to open it and split the file so that i have 3 excel files for each seperate fund.
Is there an easier way to do this rather than the way i am doing this? Ive written about 60 lines of code to split the file and i am sure there must be a more elegant way to split this file? Happy to post the code (but as i say, its not the most elegant code ive written).
May you kindly suggest an easier route?
Manny
Thu 16 May 2019, 14:36Trainer Jens said...
RE: Using Access VBA, i would like to control an Excel Workbook
Hi Manny,
I would do it in Excel.
You can probably create a connection straight to the file by using Power Query. Power Query can also split the file.
If you want to code it please have a look at the video below.
https://www.youtube.com/watch?v=48oUxaL7X6c
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Tue 21 May 2019, 09:39Delegate Manny said...
RE: Using Access VBA, i would like to control an Excel Workbook
Hi Jens,
I tried to use the power query as you suggested, it became increasingly fiddly. I decided to use an autofilter in the end. Its not the most elegant solution, but its the easiest route to take!
wb.Sheets(1).Range("A3").AutoFilter Field:=3, Criteria1:="LU1881"
Set wbtarget = Workbooks.Add
wb.Sheets(1).Cells.SpecialCells(xlCellTypeVisible).Copy Destination:=wbtarget.Sheets(1).Cells(1, 1)
wbtarget.SaveAs gubFP & "\" & "GUB Pricing " & Format(rbcCOB, "dd_mm_yyyy") & ".xlsx"
wb.Sheets(1).Range("A3").AutoFilter Field:=3, Criteria1:="LU3865"
Set wbtarget = Workbooks.Add
wb.Sheets(1).Cells.SpecialCells(xlCellTypeVisible).Copy Destination:=wbtarget.Sheets(1).Cells(1, 1)
wbtarget.SaveAs emboFP & "\" & "EMBO Pricing " & Format(rbcCOB, "dd_mm_yyyy") & ".xlsx"
wb.Sheets(1).Range("A3").AutoFilter Field:=3, Criteria1:="LU1795"
Set wbtarget = Workbooks.Add
wb.Sheets(1).Cells.SpecialCells(xlCellTypeVisible).Copy Destination:=wbtarget.Sheets(1).Cells(1, 1)
wbtarget.SaveAs arcFP & "\" & "ARC Pricing " & Format(rbcCOB, "dd_mm_yyyy") & ".xlsx"
Manny
Tue 21 May 2019, 20:48Trainer Jens said...
RE: Using Access VBA, i would like to control an Excel Workbook
Good work again Manny.
And thank you for sharing your code.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Tue 28 May 2019, 18:18Delegate Manny said...
RE: Using Access VBA, i would like to control an Excel Workbook
im stuck on the last bit, i need to delete the raw data. i tried the kill command but it looks like i keep getting the permission denied error. i did some googling and found that if i force close all the excel (atomic solution)
Shell ("taskkill /f /im excel.exe")
the kill command works! the issue is that it seems to skip this line of code when its running, but in debug code it registers. i wonder if it needs to wait and register this line first?
Manny
Wed 29 May 2019, 19:08Delegate Manny said...
RE: Using Access VBA, i would like to control an Excel Workbook
Mananged to get i, use the below. i used a shell command in the launchapp32("xxxx")
https://access-programmers.co.uk/forums/showthread.php?t=116783
launchapp32("your bat file name") instead of your shell command
Private Declare Function Openprocess Lib "Kernel32.dll" (ByVal _
dwAccess As Long, ByVal flnherit As Integer, ByVal hObject _
As Long) As Long
Private Declare Function WaitForSingleObject Lib "Kernel32" (ByVal _
hHandel As Long, ByVal dwMilliseconds As Long) As Long
Private Declare Function CloseHandle Lib "Kernel32" (ByVal _
hObject As Long) As Long
Function LaunchApp32(MYAppName As String) As Integer
On Error Resume Next
Const SYNCHRONIZE = 1048576
Const INFINITE = -1&
Dim ProcessId&
Dim ProcessHandle&
Dim Ret&
LaunchApp32 = -1
ProcessId = Shell(MYAppName, vbNormalFocus)
If ProcessId <> 0 Then
ProcessHandle = Openprocess(SYNCHRONIZE, True, ProcessId&)
Ret = WaitForSingleObject(ProcessHandle, INFINITE)
Ret = CloseHandle(ProcessHandle)
MsgBox "Back From Shell: " _
& MYAppName & " Finished", 64
Else
MsgBox "ERROR: Unable to start " & MYAppName
LaunchApp32 = 0
End If
End Function
Thu 30 May 2019, 09:54Trainer Jens said...
RE: Using Access VBA, i would like to control an Excel Workbook
Hi Manny,
I am again amazed about your solutions. You are getting around some areas of VBA very few have knowledge about.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Tue 28 May 2019: Automatically marked as resolved.
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Access tip:Deleting duplicate records from a tableYou cannot delete records tables where there duplicate records. A way around this is to create a new table which wont hold the duplicates. and then deleting the old table. |
