RunCode with two functions

[Sonya810]

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"

 

[isladogs]

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?

 

[Sonya810]

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

 

[isladogs]

Typo in do while line.
You've written 'mymile' rather than 'myfile'

 

[Sonya810]

Yes, I fixed that. It still will not add the data to the access table.

 

[isladogs]

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?

 

[Sonya810]

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

 

[Mark_]

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

myfile = Dir("C:\Users\tzqy46\Documents\IMDS\*.xlsx")

prior to

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

msgbox "DIR returns " & myfile

as the first line within your do while loop.