Importing excel file using VBA

[Tiffany]

Hi,

I am using the following code:
Function Get_FileName(prmstrDialog, prmstrFileName) As String
Dim strFilter As String
Dim lngFlags As Long
Dim loclngJ As Long
Dim loclngTempPos As Long
Dim loclngPosition As Long
Dim locblnFinished As Boolean

WrongFileName:

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", _
"*.xls")
strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
If (gblstrDirectory = "") Then
gblstrDirectory = "C:\"
End If
Get_FileName = ahtCommonFileOpenSave(InitialDir:=gblstrDirectory, _
Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _
DialogTitle:=prmstrDialog)
If (Get_FileName <> "") Then
If (InStr(1, Get_FileName, prmstrFileName) = 0) Then
MsgBox "Error you have selected the wrong filename" & vbCrLf &
vbCrLf & _
prmstrDialog & ".", vbCritical
GoTo WrongFileName
Else
loclngPosition = 1
locblnFinished = False
Do Until locblnFinished
loclngTempPos = InStr(loclngPosition + 1, Get_FileName, "\")
If (loclngTempPos <> 0) Then
loclngPosition = loclngTempPos
Else
locblnFinished = True
gblstrDirectory = Mid(Get_FileName, 1, loclngPosition - 1)
End If
Loop
End If
Else
End
End If
End Function

And then using the TransferSpreadsheet argument in the macro to import the
actual file. How can I use VBA and use the RunCode() argument to import the
file??

 

[Ken Snell \(MVP\)]

See this article:

Browse to a single EXCEL File and Import Data from that EXCEL File via
TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ImpBrowseFile

No need to use RunCode in a macro.

 

[Tiffany]

This helps heaps,
If I don't use RunCode() in the macro, how do I call the function??
Also, how do I put a parameter in it, so it is like an input mask so the
user selects the correct file?? Or can I specify what the name of the file
needs to be called to select.
In another database, I use the following code but I have put the filename in
the TransferSpreadsheet in the macro not in the VBA code, but would like to
put it in the VBA code:
Function Get_FileName(prmstrDialog, prmstrFileName) As String
Dim strFilter As String
Dim lngFlags As Long
Dim loclngJ As Long
Dim loclngTempPos As Long
Dim loclngPosition As Long
Dim locblnFinished As Boolean

WrongFileName:

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", _
"*.xls")
strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
If (gblstrDirectory = "") Then
gblstrDirectory = "C:\"
End If
Get_FileName = ahtCommonFileOpenSave(InitialDir:=gblstrDirectory, _
Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _
DialogTitle:=prmstrDialog)
If (Get_FileName <> "") Then
If (InStr(1, Get_FileName, prmstrFileName) = 0) Then
MsgBox "Error you have selected the wrong filename" & vbCrLf &
vbCrLf & _
prmstrDialog & ".", vbCritical
GoTo WrongFileName
Else
loclngPosition = 1
locblnFinished = False
Do Until locblnFinished
loclngTempPos = InStr(loclngPosition + 1, Get_FileName, "\")
If (loclngTempPos <> 0) Then
loclngPosition = loclngTempPos
Else
locblnFinished = True
gblstrDirectory = Mid(Get_FileName, 1, loclngPosition - 1)
End If
Loop
End If
Else
End
End If
End Function

 

[Tiffany]

Also, If I use the VBA code to import the spreadsheet, how can I import it
from cell A2, as the TransferSpreadsheet macro may be the only way??

 

[pietlinden]

- Show quoted text -

read this article:
http://www.mvps.org/access/general/gen0008.htm

 

[dymondjack]

As far as macros are concerned, many experienced programmers write extensive
applications in access without ever using a single macro.

To get VBA functionality without using macros, most people would assign code
to a control or command button on a form. So if you had a button on a form
named btnTransfer, you could use the OnClick event (properties -> Events tab
-> Code Builder), and your code would probably resemble something like this:

Private Sub btnTransfer_Click()

Dim sFilePath as String
sFilePath = Get_FileName(arg1, arg2)
DoCmd.TransferSpreadsheet acImport, arg3, arg4, sFilePath, etc...

End Sub

Replace arg1, arg2 etc with whatever arguments you are currently passing
using the macro that you have.

The Get_FileName function can be located in the same module as the form, or
in any standard module, provided you put "Public" at the beginning of the
declaration line

Public Function Get_FileName(prmstrDialog, prmstrFileName)

hope this helps
--
Jack Leach
www.tristatemachine.com

- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away." - Antoine De Saint
Exupery

 

[Ken Snell \(MVP\)]

dymondjack has shown how you can use VBA code to run a function.

If you want to run the function from a macro, you must hardcode the two
arguments for the function Get_FileName directly in the macro. So, the
Function Name argument for the RunCode action would be something like this:

Get_FileName("String for prmstrDialog", "String for prmstrFileName")

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/