Can VBA take user input (form?), search and locate file, retreive data and output?

Hi everyone. I am a VBAby that needs to learn fast, so any help is truly
appreciated. My first problem is to write a program that accepts user input,
say a file name from a form or even a cell in Excel, locates the file (even
if it's in another directory), extracts data from the file based on a given
range name (more user input) and prints the data to an output line contained
in the same sheet/form as the input. Can all this be done? Can any of it?
Any help to get me started will be most welcome!

The first thing to do would be to ask in a newsgroup about Excel like
microsoft.public.excel.

The second thing would be to split your problem in its basic parts and if
necessary, ask each part (or question) into its appropriate newsgroup. For
exemple, locating a file by making a full search of all disk drives and
repertories has more to do with files and scripting technologies (search
Google for Scripting.FileSystemObject) than with Excel or Access.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


wrote in message
...
Hi everyone. I am a VBAby that needs to learn fast, so any help is truly
appreciated. My first problem is to write a program that accepts user
input, say a file name from a form or even a cell in Excel, locates the
file (even if it's in another directory), extracts data from the file
based on a given range name (more user input) and prints the data to an
output line contained in the same sheet/form as the input. Can all this be
done? Can any of it? Any help to get me started will be most welcome!

Dr Jones,

All of this and much more can certainly be done, but your question is
too broad, and requires too lengthy a reply to be answered in the NG's.
And, sorry, no magic wands either... it will take quite some time if you
don't know where to start from, so if you're really pressed for time I'd
suggest you either look for a readily available tool to buy, if one
exists that covers your needs, or commission the job to an experienced
developer.

Sorry to disappoint you, but such is life!

Nikos

Thank you Sylvain -- I'd reposted my question (a few mins after this one)
breaking down the tasks. Here's what I need (to begin with):
=======================
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

"Sylvain Lafontaine" sylvain aei ca (fill the blanks, no spam please)
wrote in message ...
The first thing to do would be to ask in a newsgroup about Excel like
microsoft.public.excel.

The second thing would be to split your problem in its basic parts and if
necessary, ask each part (or question) into its appropriate newsgroup.
For exemple, locating a file by making a full search of all disk drives
and repertories has more to do with files and scripting technologies
(search Google for Scripting.FileSystemObject) than with Excel or Access.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


wrote in message
...
Hi everyone. I am a VBAby that needs to learn fast, so any help is truly
appreciated. My first problem is to write a program that accepts user
input, say a file name from a form or even a cell in Excel, locates the
file (even if it's in another directory), extracts data from the file
based on a given range name (more user input) and prints the data to an
output line contained in the same sheet/form as the input. Can all this
be done? Can any of it? Any help to get me started will be most welcome!

Using DoCmd.TransferSpreadsheet is one of many method to import data from an
Excel spreadsheet. You can also use a linked table, the OLEDB driver for
Excel or Automation or whatever else. You will get more info by making a
search with Google or asking in a newsgroup about Excel.

Personally, I know near to nothing about Excel.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


wrote in message
...
Thank you Sylvain -- I'd reposted my question (a few mins after this one)
breaking down the tasks. Here's what I need (to begin with):
=======================
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

"Sylvain Lafontaine" sylvain aei ca (fill the blanks, no spam please)
wrote in message ...
The first thing to do would be to ask in a newsgroup about Excel like
microsoft.public.excel.

The second thing would be to split your problem in its basic parts and if
necessary, ask each part (or question) into its appropriate newsgroup.
For exemple, locating a file by making a full search of all disk drives
and repertories has more to do with files and scripting technologies
(search Google for Scripting.FileSystemObject) than with Excel or Access.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


wrote in message
...
Hi everyone. I am a VBAby that needs to learn fast, so any help is truly
appreciated. My first problem is to write a program that accepts user
input, say a file name from a form or even a cell in Excel, locates the
file (even if it's in another directory), extracts data from the file
based on a given range name (more user input) and prints the data to an
output line contained in the same sheet/form as the input. Can all this
be done? Can any of it? Any help to get me started will be most welcome!