Advertisement

04.18.2008 at 12:15PM PDT, ID: 23335301
[x]
Attachment Details

Select excel worksheet to import.

Asked by mvarner2000 in Microsoft Access Database, Visual Basic Programming, Access Coding/Macros

Tags: Visual Basic

I have an access database that imports an excel spread sheet.  I need to do the same thing for another appication but this time there are multiple worksheets in the spreadsheet.  I need to know how to modify my code to select the worksheet I want to import.

Regards'

MarshallStart Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:

Option Compare Database
Global strExportPathAndFileName As String
' GetOpenFileName Declaration
    Declare Function Open_File_Name_Dialog_Box Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As OpenFilename) As Long
' OpenFileName Structure
    Type OpenFilename
        lStructSize As Long
        hwndOwner As Long
        hInstance As Long
        lpstrFilter As String
        lpstrCustomFilter As String
        nMaxCustFilter As Long
        nFilterIndex As Long
        lpstrFile As String
        nMaxFile As Long
        lpstrFileTitle As String
        nMaxFileTitle As Long
        lpstrInitialDir As String
        lpstrTitle As String
        Flags As Long
        nFileOffset As Integer
        nFileExtension As Integer
        lpstrDefExt As String
        lCustData As Long
        lpfnHook As Long
        lpTemplateName As String
    End Type
 
Public Function OpenDialogBox(strSource As String)
' Enable error handler for this routine
    On Error GoTo err_OpenDialogBox
' Dimension a variable for the return value of GetOpenFileName
    Dim ReturnValue As Long
    Dim strFullPath As String
    Dim lnImportType As Long
' Dimension a Variable Of the OPENFILENAME type
    Dim Open_File_Name_Structure As OpenFilename
' Dimension variables used for filters
    Dim strFilter1 As String, strFilter2 As String, Filter As String, strIDir As String
' Set the filters
    strFilter1 = "Select the File to Import(*.xls)" + Chr$(0) + "*.xls" + Chr$(0)
    strFilter2 = "Excel files (*.xls)" + Chr$(0) + "*.xls" + Chr$(0)
    Filter = strFilter1 + strFilter2 + Chr$(0)
' Assign the structure members
        Open_File_Name_Structure.lStructSize = Len(Open_File_Name_Structure)
        Open_File_Name_Structure.hwndOwner = Screen.ActiveForm.Hwnd
        Open_File_Name_Structure.lpstrFilter = Filter
        Open_File_Name_Structure.nFilterIndex = 1
        Open_File_Name_Structure.lpstrFile = Chr$(0) & Space$(255) & Chr$(0)
        Open_File_Name_Structure.nMaxFile = 10000
        Open_File_Name_Structure.lpstrFileTitle = Chr$(0) & Space$(255) & Chr$(0)
        Open_File_Name_Structure.nMaxFileTitle = 10000
        Open_File_Name_Structure.lpstrDefExt = ".xls" & Chr$(0)
        Open_File_Name_Structure.lpstrInitialDir = "M:\" + Chr$(0)
        Open_File_Name_Structure.nFileOffset = 0
        Open_File_Name_Structure.nFileExtension = 0
        Open_File_Name_Structure.lpstrTitle = "Select a File"
' Call the function to open the DialogBox
    ReturnValue = Open_File_Name_Dialog_Box(Open_File_Name_Structure)
' Put the Path and File Name onto the form
    strFullPath = Left(Open_File_Name_Structure.lpstrFile, InStr(Open_File_Name_Structure.lpstrFile, Chr$(0)) - 1)
    Select Case strSource
    Case "txtPathFile"
        Forms!FrmImport(strSource).Value = strFullPath '<-----------------------------WILL NEED YOUR FORM'S NAME HERE
    End Select
' Exit the Function
    Exit Function
' Error handler for this routine
err_OpenDialogBox:
    Select Case Err
        Case 3315
            Exit Function
        Case Else
            MsgBox "Function modOpen_Dialog_Box_Code.OpenDialogBox." & vbCrLf & "You have error number " & Err & ".  " & Err.Description
    End Select
End Function
[+][-]04.19.2008 at 01:49AM PDT, ID: 21391679

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]04.21.2008 at 05:56PM PDT, ID: 21407418

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]04.26.2008 at 05:19PM PDT, ID: 21447272

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]04.27.2008 at 12:22PM PDT, ID: 21450093

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: Microsoft Access Database, Visual Basic Programming, Access Coding/Macros
Tags: Visual Basic
Sign Up Now!
Solution Provided By: matthewspatrick
Participating Experts: 2
Solution Grade: A
 
 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628