Import Spreadsheet Problem

[fishqqq]

Is there a way to create a macro that will ask the user which .xls
file (on their hard drive) they wish to a particular table in a .mdb
file?

I have customers emailing me .xls files which have only one row of
data in them and i would like the user to click a button and be asked
which .xls file they wish to import and have this data pasted into
[table1] in my .mde file.

Once the data is in the table I know how to get it to do what i wish.
What i don't understand is how to create a macro that will ask the
user which file to import.
The "importexportspreadsheet" action will let me import a
predetermined file but i need something that will ask me for the
specific file i wish to import.

I know i can do this with the external data options but when i create
my .mde file the end users will not have the ability to do this. i
need something 'built in' to access which will allow them to do this.

any ideas are greatly appreciated.

tks
Steve

 

[Ken Snell]

See this web page for various code options:
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

 

[fishqqq]

See this web page for various code options:http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

--

        Ken Snellhttp://www.accessmvp.com/KDSnell/

Is there a way to create a macro that will ask the user which .xls
file (on their hard drive) they wish to a particular table in a .mdb
file?

I have customers emailing me .xls files which have only one row of
data in them and i would like the user to click a button and be asked
which .xls file they wish to import and have this data pasted into
[table1] in my .mde file.

Once the data is in the table I know how to get it to do what i wish.
What i don't understand is how to create a macro that will ask the
user which file to import.
The "importexportspreadsheet" action will let me import a
predetermined file but i need something that will ask me for the
specific file i wish to import.

I know i can do this with the external data options but when i create
my .mde file the end users will not have the ability to do this. i
need something 'built in' to access which will allow them to do this.

any ideas are greatly appreciated.

tks
Steve

I am looking to have this code (above) triggered at the beginning of a
macro sequence and I believe the best way to do this would be to use
the :
"Run Code" expression . But in order to do this I need to insert the
"function name".

the code I would like to run is an event procedure:

Private Sub Command439_Click()
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "C:\FMS\SHAN\"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "1 Shan Ship Download"

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
Kill strPathFile

strFile = Dir()
Loop

End Sub

If i wanted to run this before the rest of my macro with the "run
code" expression - what would the function name be?
I know i'm trying to combine code with macros here but i have a a lot
going on in the macro that i need done AFTER the above code has been
run. And instead of having the code triggered on one command button
and the macro triggered with another - i would like to have both run
when the user clicks one button (instread of two)

thanks
Steve

 

[Ken Snell]

I'm assuming that you want to run a macro on the OnClick event of the
Command439 control on the form? Assuming yes, copy the code you have posted
and put it into a public module (Visual Basic Editor | Insert Module).
Rename the procedure
Public Function DoTheImport

and change End Sub to End Function.

Then create a macro and make its first step RunCode and use the DoTheImport
name as the function name. Then add other steps that you want to run in the
macro. Assign the macro to the OnClick event of the Command439 control.
--

Ken Snell
http://www.accessmvp.com/KDSnell/

See this web page for various code
options:http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

--

Ken Snellhttp://www.accessmvp.com/KDSnell/

Is there a way to create a macro that will ask the user which .xls
file (on their hard drive) they wish to a particular table in a .mdb
file?

I have customers emailing me .xls files which have only one row of
data in them and i would like the user to click a button and be asked
which .xls file they wish to import and have this data pasted into
[table1] in my .mde file.

Once the data is in the table I know how to get it to do what i wish.
What i don't understand is how to create a macro that will ask the
user which file to import.
The "importexportspreadsheet" action will let me import a
predetermined file but i need something that will ask me for the
specific file i wish to import.

I know i can do this with the external data options but when i create
my .mde file the end users will not have the ability to do this. i
need something 'built in' to access which will allow them to do this.

any ideas are greatly appreciated.

tks
Steve

I am looking to have this code (above) triggered at the beginning of a
macro sequence and I believe the best way to do this would be to use
the :
"Run Code" expression . But in order to do this I need to insert the
"function name".

the code I would like to run is an event procedure:

Private Sub Command439_Click()
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "C:\FMS\SHAN\"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "1 Shan Ship Download"

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
Kill strPathFile

strFile = Dir()
Loop

End Sub

If i wanted to run this before the rest of my macro with the "run
code" expression - what would the function name be?
I know i'm trying to combine code with macros here but i have a a lot
going on in the macro that i need done AFTER the above code has been
run. And instead of having the code triggered on one command button
and the macro triggered with another - i would like to have both run
when the user clicks one button (instread of two)

thanks
Steve