Go Back   Access World Forums > Microsoft Access Discussion > Macros

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-08-2018, 03:13 PM   #1
Sonya810
Newly Registered User
 
Join Date: Mar 2018
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Sonya810 is on a distinguished road
RunCode with two functions

I am trying to run a macro that use two functions. The first function formats all of my excel files. The second function is suppose to import all excel files into an access table. The first function works, but when I go to create the macro adding my second function, I get an error "The expression you entered has the wrong number of arguments"

Sonya810 is offline   Reply With Quote
Old 03-08-2018, 03:25 PM   #2
ridders
Newbee moderator
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 5,530
Thanks: 79
Thanked 1,368 Times in 1,277 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: RunCode with two functions

This means the function requires specific information in the form of function 'arguments' e.g file names but you are either not providing all it needs .... OR somewhat less likely you are adding too many arguments.

If you want a more precise answer, you will need to pride more information.
What is the second function? What have you used in your function call?
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


-------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
Old 03-08-2018, 03:30 PM   #3
Sonya810
Newly Registered User
 
Join Date: Mar 2018
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Sonya810 is on a distinguished road
Re: RunCode with two functions

This is what I have. The first function works. The second does not:

Option Compare Database

Function ProcessFiles()


Dim wb As Workbook
Dim mypath As String
Dim myfile As String
Dim myExtension As String
Dim FldrPicker As FileDialog
Dim Filename As String

'Optimize Macro Speed
'Application.ScreenUpdating = False
'Application.EnableEvents = False
'Application.Calculation = xlCalculationManual

'Retrieve Target Folder Path From User
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

With FldrPicker
.Title = "Select A Target Folder"
.AllowMultiSelect = False
If .Show <> -1 Then GoTo NextCode
mypath = .SelectedItems(1) & ""
End With

'In Case of Cancel
NextCode:
mypath = mypath
If mypath = "" Then GoTo ResetSettings

'Target File Extension (must include wildcard "*")
myExtension = "*.xlsx*"

'Target Path with Ending Extention
myfile = Dir(mypath & myExtension)

'Loop through each Excel file in folder
Do While myfile <> ""
'Set variable equal to opened workbook
Set wb = Workbooks.Open(Filename:=mypath & myfile)

'Ensure Workbook has opened before moving on to next line of code
DoEvents

'Change First Worksheet's Background Fill Blue
'wb.Worksheets(1).Range("A1:Z1").Interior.Color = RGB(51, 98, 174)

Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Rows("1:5").Select
Selection.Delete Shift:=xlUp
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
Columns("D").Select
Selection.Delete Shift:=xlToLeft
Range("E7").Select


'Save and Close Workbook
wb.Close SaveChanges:=True

'Ensure Workbook has closed before moving on to next line of code
DoEvents

'Get next file name
myfile = Dir
Loop





'Message Box when tasks are completed
MsgBox "Task Complete!"

ResetSettings:
'Reset Macro Optimization Settings
'Application.EnableEvents = True
'Application.Calculation = xlCalculationAutomatic
'Application.ScreenUpdating = True

End Function

Function Impo_allExcel()
Dim myfile
Dim mypath

mypath = "C:\Users\tzqy46\Documents\IMDS"
ChDir (mypath)
myfile = Dir()
Do While mymile <> ""
If myfile Like "*.xlsx" Then
'this will import ALL the excel files
'(one at a time, but automatically) in this folder.
' Make sure that's what you want.
DoCmd.TransferSpreadsheet acImport, 13, "IMDS", mypath & myfile
End If
myfile = Dir()
Loop
End Function

Sonya810 is offline   Reply With Quote
Old 03-08-2018, 03:43 PM   #4
ridders
Newbee moderator
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 5,530
Thanks: 79
Thanked 1,368 Times in 1,277 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: RunCode with two functions

Typo in do while line.
You've written 'mymile' rather than 'myfile'
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


-------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
Old 03-08-2018, 03:45 PM   #5
Sonya810
Newly Registered User
 
Join Date: Mar 2018
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Sonya810 is on a distinguished road
Re: RunCode with two functions

Yes, I fixed that. It still will not add the data to the access table.
Sonya810 is offline   Reply With Quote
Old 03-08-2018, 03:53 PM   #6
ridders
Newbee moderator
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 5,530
Thanks: 79
Thanked 1,368 Times in 1,277 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: RunCode with two functions

Add data to which access table?
Are you trying to import the spreadsheet or append the data to an existing table?

How does the loop identify each Excel file in that folder in turn?
How does it know where o place the data?
I'm not clear how you think it should work?

To clarify, does fixing the typo solve the original 'arguments' error?
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


-------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
Old 03-08-2018, 03:57 PM   #7
Sonya810
Newly Registered User
 
Join Date: Mar 2018
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Sonya810 is on a distinguished road
Re: RunCode with two functions

I am trying to add data to an access table called IMDS. I am trying to import every spreadsheet from within a certain folder by looping through each spreadsheet and importing the data in an access table. I want to loop through each file in the folder and add the data. Each file is set up the same way and have the same column headers....same as my access table.

I did fix the error, but it still isn't working

Sonya810 is offline   Reply With Quote
Old 03-08-2018, 04:45 PM   #8
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Posts: 1,223
Thanks: 13
Thanked 231 Times in 229 Posts
Mark_ will become famous soon enough
Re: RunCode with two functions

I am guessing you went to this link for help first? If so, I think you missed one bit.

If I understand what you are trying to do, you would want to add the line
Code:
myfile = Dir("C:\Users\tzqy46\Documents\IMDS\*.xlsx")
prior to
Code:
Do While myfile <> ""
so that DIR knows what files you are looking for. As you are not telling DIR what to look for so it is returning... stuff.

To make sure of the behavior, I would add
Code:
msgbox "DIR returns " & myfile
as the first line within your do while loop.

Mark_ is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
RunCode SQL statement Bopsgtir Modules & VBA 10 10-13-2011 03:14 PM
Runcode macro Cobra Macros 16 02-09-2009 09:55 AM
Runcode through a macro abhinarulkar Macros 1 12-02-2004 02:26 AM
trying Runcode Action?? lipin Macros 1 06-13-2002 09:20 AM
[SOLVED] How can I use RunCode Action for.... Mendel8 Macros 2 02-26-2001 05:56 PM




All times are GMT -8. The time now is 11:55 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World