CODEWITHSUNDEEP
vbaexcel
user1410368
user1410368

Reputation: 1

VBA to link Excel spreadsheets to Access

I am creating a code in VBA in Access 2010 to link excel sheets and put them into tables in access. I keep getting an invalid outside of procedure at the strFile = Dir(StrPath &"*.xls") It keeps telling the the strPath is invalid outside procedure

Please help.

Option Compare Database
Option Explicit

'code will link to excel and pull site survey files into access tables

'Setting the path for the directory

Const strPath As String = "C:\Users\cparson\Documents\Survey_Eqpm\SiteSurveyData.xlsx"

'FileName
Dim strFile As String
'Array
Dim strFileList() As String
'File Number
Dim intFile As Integer

'Looping through the folder and building the file list
strFile = Dir(strPath & "*.xls")
While strFile <> ""
    'adding files to the list
    intFile = intFile + 1
    ReDim Preserve strFileList(1 To intFile)
    strFileList(intFile) = strFile
    strFile = Dir()
Wend
'checking to see if files where found
If intFile = 0 Then
    MsgBox "No Files Found"
    Exit Sub
End If
'going through the files and linking them to access
For intFile = 1 To UBound(strFileList)
    DoCmd.TransferSpreadsheet acLink, , _
    strFileList(intFile), strPath & strFileList(intFile), True, "A5:J17"
Next
MsgBox UBound(strFileList) & "Files were linked"
End Sub

Upvotes: 0

Views: 19129

Answers (3)

jones-chris
jones-chris

Reputation: 691

I know this is an old question, but I came across it in a google search and realized that you already have the .xlsx extension in the strPath variable, but you add it to the string variable, strFile, also.

Const strPath As String = "C:\Users\cparson\Documents\Survey_Eqpm\SiteSurveyData.xlsx"

strFile = Dir(strPath & "*.xls")

I might be wrong, but just wanted to point it out.

Upvotes: 1

Bruno Leite
Bruno Leite

Reputation: 1477

You can try too ADO, it's a easy way in my opnion

YourConnObj.execute "SELECT * INTO  YourTableName from [Excel 14.0;DATABASE=c:\temp\data copy.xlsx].[Sheet1]"

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149287

You have an End Sub but no procedure name?

Option Compare Database
Option Explicit

Const strPath As String = "C:\Users\cparson\Documents\Survey_Eqpm\SiteSurveyData.xlsx"

Dim strFile As String
Dim strFileList() As String
Dim intFile As Integer

Sub Sample() '<~~ You are missing this...
    strFile = Dir(strPath & "*.xls")

    '~~> Rest of your code
End Sub

Upvotes: 1

Related Questions