Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default Importing EXCEL Spreadsheets into ACCESS -- Macro or VBA?

I'm not certain if this one belongs in the EXCEL or ACCESS Newgroup ...
====================================
I have an Aggregate Transfer Spreadsheet Macro that runs a series of Delete
Queries and Individual Transfer Spreadsheet Macros to import my EXCEL data
into ACCESS. I've been tasked with additional criteria that I'm having
problems with:

At this point, I'm not even sure if a Macro can handle all these additional
criteria -- Is there a way (In ACCESS) to accomplish the following:

1. Have a form (or similar interface) where a user could specify which
corresponding EXCEL Spreadsheets they would like the Macro to Transfer?

For example: Say, my directory path is C:\\Temp\ and I have 8
individual workbooks -- (in my Macros, I've specified the range:
PC_Budget_Upload_SR-X!A4:R257) -- I just need a way for the user to choose
"Import All Workbooks" or to "Specify Individual Workbooks" to import -- I'm
guess that a form might be the way to go on this, but how would you work the
logic behind the drop-down selection? In other words, how does the user
selection on the form get updated in the Macro (or VBA code)?

Here's the (modified) code I have that allows me to "enter" the File Name &
Worksheet Name for a single file (Note: This code was originally written to
Loop through a directory and import all files having *.xls -- I need for the
code to allow me to choose individual files from that directory:

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 = "E:\AL1403 05-06\" '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")
strFileName = InputBox("Enter the name of the file.")
strSheetName = InputBox("Enter the worksheet name.")
'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, , _
"Wednesday_Check", strPath & strFile, True, strSheetName &
"!A4:T257"
'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
================================================== =======
Here is some code which allows you to select Multiple Files to open -- could
a variation of this code give me what I'm looking for? And IF so, how would
I incorporate it into my original code?

Sub OpenMultipleFiles()
Dim Filter As String, Title As String, msg As String
Dim i As Integer, FilterIndex As Integer
Dim Filename As Variant
' File filters
Filter = "Excel Files (*.xls),*.xls," & _
"Text Files (*.txt),*.txt," & _
"All Files (*.*),*.*"
' Default filter to *.*
FilterIndex = 3
' Set Dialog Caption
Title = "Select File(s) to Open"
' Select Start Drive & Path
ChDrive ("E")
ChDir ("E:\AL1403 05-06")
With Application
' Set File Name Array to selected Files (allow multiple)
Filename = .GetOpenFilename(Filter, FilterIndex, Title, , True)
' Reset Start Drive/Path
ChDrive (Left(.DefaultFilePath, 1))
ChDir (.DefaultFilePath)
End With
' Exit on Cancel
If Not IsArray(Filename) Then
MsgBox "No file was selected."
Exit Sub
End If
' Open Files
For i = LBound(Filename) To UBound(Filename)
msg = msg & Filename(i) & vbCrLf ' This can be removed
Workbooks.Open Filename(i)
Next i
MsgBox msg, vbInformation, "Files Opened" ' This can be removed
End Sub


2. The other requirement is that we need the File Path of the imported
EXCEL file (as well as the Date/Time of the import into ACCESS) to be
populated in the destination table in ACCESS (Note: these (2) data fields
do not currently exist in the Spreadsheets being imported -- they need to be
created once they're imported into ACCESS.

Any thoughts on how I can approach this will be greatly appreciated --
thanks in advance.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Importing Tables from Access into Excel using Macros or VBA CLamar Excel Discussion (Misc queries) 4 May 23rd 06 09:23 PM
importing links from access to excel to template jwr Links and Linking in Excel 1 February 5th 06 07:42 PM
Using Access in Excel VB Code Michael Excel Discussion (Misc queries) 1 February 2nd 06 08:31 PM
Urgent Help Required on Excel Macro Problem Sachin Shah Excel Discussion (Misc queries) 1 August 17th 05 06:26 AM
Importing Data from Access into Excel vnvkatz Excel Discussion (Misc queries) 3 June 9th 05 05:02 PM


All times are GMT +1. The time now is 12:33 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"