Importing EXCEL Spreadsheet into ACCESS using TransferSpreadsheet method

G

Guest

I have the VBA code which will search the Directory Path and Import EXCEL
files into ACCESS. Here's what I need the code to do:

1. The existing code Imports the data in the entire EXCEL Workbooks -- I
need to be able to specify Certain Worksheets -- and then, only specified
Cells within these Worksheets -- what would be the best way to approach this
requirement?

Here's the code I have:
========================================
Sub Import_From_Excel()
'Macro Loops through the specified directory (strPath)
'and imports ALL Excel files to specified table in the Access
'Database.

Const strPath As String = "C:\Temp\" 'Directory Path
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number

'Loop through the folder & build file list
strFile = Dir(strPath & "*.xls")
While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend
'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If
'cycle through the list of files & import to Access
'creating a new table called MyTable
For intFile = 1 To UBound(strFileList)
DoCmd.TransferSpreadsheet acImport, , _
"NewTable", strPath & strFileList(intFile), True, "A1:J50"
'Check out the TransferSpreadsheet options in the Access
'Visual Basic Help file for a full description & list of
'optional settings
Next
MsgBox UBound(strFileList) & " Files were Imported"
End Sub
 
F

fredg

I have the VBA code which will search the Directory Path and Import EXCEL
files into ACCESS. Here's what I need the code to do:

1. The existing code Imports the data in the entire EXCEL Workbooks -- I
need to be able to specify Certain Worksheets -- and then, only specified
Cells within these Worksheets -- what would be the best way to approach this
requirement?

Here's the code I have:
========================================
Sub Import_From_Excel()
'Macro Loops through the specified directory (strPath)
'and imports ALL Excel files to specified table in the Access
'Database.

Const strPath As String = "C:\Temp\" 'Directory Path
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number

'Loop through the folder & build file list
strFile = Dir(strPath & "*.xls")
While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend
'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If
'cycle through the list of files & import to Access
'creating a new table called MyTable
For intFile = 1 To UBound(strFileList)
DoCmd.TransferSpreadsheet acImport, , _
"NewTable", strPath & strFileList(intFile), True, "A1:J50"
'Check out the TransferSpreadsheet options in the Access
'Visual Basic Help file for a full description & list of
'optional settings
Next
MsgBox UBound(strFileList) & " Files were Imported"
End Sub
Click to expand...


Nowhere in your code do I see what the worksheet name is that you wish
to import. If it is the same worksheet name in each of the
spreadsheets, then:

DoCmd.TransferSpreadsheet acImport, , "NewTable", strPath &
strFileList(intFile), True, "WorksheetNameHere!A1:J50"

If the name is derived from a variable, then:
Dim strSheetName as String
strSheetName = "AWorksheetName"
DoCmd.TransferSpreadsheet acImport, , "NewTable", strPath &
strFileList(intFile), True, strSheetName & "!A1:J50"
 
G

Guest

Fred,

Thank you for your assistance -- ideally, I'd like to be able to select the
WorkSheet (as a Variable) -- is there a way that I can design an Input Field
(whereas the user could enter the desired WorkSheet) and have that value
captured in the strSheetName = "AWorksheetName" line? (where Input Name is
the WorkSheetName).

Is there an advantage in assigning a SheetName variable using
Dim strSheetName as String
strSheetName = "AWorksheetName"
DoCmd.TransferSpreadsheet acImport, , "NewTable", strPath &
strFileList(intFile), True, strSheetName & "!A1:J50"

As opposed to hard-coding it preceeding the Range? (for example -- will
assigning a variable (AS ABOVE) bring up an Input Window each time where the
user can enter the WorkSheet?) (See Below)

DoCmd.TransferSpreadsheet acImport, , _
"Ala 1403 Budget_Fields", strPath & strFileList(intFile), True,
"PC_Budget_Upload-X!A4:R257"

Sub Import_From_Excel()
'Macro Loops through the specified directory (strPath)
'and imports ALL Excel files to specified table in the Access
'Database.

Const strPath As String = "C:\Temp\" 'Directory Path
Dim strSheetName As String 'Worksheet Name
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number

'Loop through the folder & build file list
strFile = Dir(strPath & "*.xls")
strSheetName = ""
While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend
'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If
'cycle through the list of files & import to Access
'creating a new table called MyTable
For intFile = 1 To UBound(strFileList)
DoCmd.TransferSpreadsheet acImport, , _
"Ala 1403 Budget_Fields", strPath & strFileList(intFile), True,
"PC_Budget_Upload-X!A4:R257"
'Check out the TransferSpreadsheet options in the Access
'Visual Basic Help file for a full description & list of
'optional settings
Next
MsgBox UBound(strFileList) & " Files were Imported"
End Sub

Additionally, the Network folks are prone to change the path to the affected
files -- I'm not sure if this is a possibility or not:

Is there a way where I could allow the user to locate the file (say, using
Windows Explorer) and upon clicking on the desired EXCEL Workbook, the path
would be entered into the strPath (ie. O:\Financial Projects\M Shane\AL1403
05-06 is the path today -- next month it may be N:\Financial Projects\M
Shane\AL1403 05-06)

Shane
==================================
 
F

fredg

Fred,

Thank you for your assistance -- ideally, I'd like to be able to select the
WorkSheet (as a Variable) -- is there a way that I can design an Input Field
(whereas the user could enter the desired WorkSheet) and have that value
captured in the strSheetName = "AWorksheetName" line? (where Input Name is
the WorkSheetName).

Is there an advantage in assigning a SheetName variable using
Dim strSheetName as String
strSheetName = "AWorksheetName"
DoCmd.TransferSpreadsheet acImport, , "NewTable", strPath &
strFileList(intFile), True, strSheetName & "!A1:J50"

As opposed to hard-coding it preceeding the Range? (for example -- will
assigning a variable (AS ABOVE) bring up an Input Window each time where the
user can enter the WorkSheet?) (See Below)

DoCmd.TransferSpreadsheet acImport, , _
"Ala 1403 Budget_Fields", strPath & strFileList(intFile), True,
"PC_Budget_Upload-X!A4:R257"

Sub Import_From_Excel()
'Macro Loops through the specified directory (strPath)
'and imports ALL Excel files to specified table in the Access
'Database.

Const strPath As String = "C:\Temp\" 'Directory Path
Dim strSheetName As String 'Worksheet Name
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number

'Loop through the folder & build file list
strFile = Dir(strPath & "*.xls")
strSheetName = ""
While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend
'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If
'cycle through the list of files & import to Access
'creating a new table called MyTable
For intFile = 1 To UBound(strFileList)
DoCmd.TransferSpreadsheet acImport, , _
"Ala 1403 Budget_Fields", strPath & strFileList(intFile), True,
"PC_Budget_Upload-X!A4:R257"
'Check out the TransferSpreadsheet options in the Access
'Visual Basic Help file for a full description & list of
'optional settings
Next
MsgBox UBound(strFileList) & " Files were Imported"
End Sub

Additionally, the Network folks are prone to change the path to the affected
files -- I'm not sure if this is a possibility or not:

Is there a way where I could allow the user to locate the file (say, using
Windows Explorer) and upon clicking on the desired EXCEL Workbook, the path
would be entered into the strPath (ie. O:\Financial Projects\M Shane\AL1403
05-06 is the path today -- next month it may be N:\Financial Projects\M
Shane\AL1403 05-06)

Shane
================================== ** snipped **
Click to expand...


To input a Worksheet name, you can use:
Dim strSheetName as String
strSheetName = InputBox("Enter the worksheet name.")

You can also use a form:
Dim strSheetName as String
strSheetName = forms!FormName!ControlName
the form must be open when this code is run.

To find the path to a file or folder, look and see what is available
here:
http://www.mvps.org/access/api/index.html

Why in the world are you cycling and importing all spreadsheets if you
only want a particular worksheet and range in one spreadsheet?
 
D

dbahooker

Excel isn't a database

tell your beancounters to grow up and stop using Excel.

-Aaron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Word macro that can call cells out of Excel for a find and replace 0
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Import multiple csv and add filename in additional field 4
Importing EXCEL Spreadsheets into Access -- Macro or VBA? 1
Import Table / Field Code Data from Word 1
VBA Export to PDF 0
Code too large Extracting Txt Srtings 1
Importing to Access Records 6

Top