Hi there
This code (below) works great if the Excel file has only 1 sheet. If the Excel file has multiple sheets, as I noted, it always imports the 1st sheet.
My temporary solution for this was to drag the sheet that I want to the first sheet's position, this trick works for now, Is it possible to modify this code to import the specific sheet I need ?
Private Sub cmdImport2_Click()
On Error GoTo ErrorHandler
Dim strFilePath As String
strFilePath = "C:\TempTestOnly\IDnumber.xlsx"
Dim dlg As FileDialog
Set dlg = Application.FileDialog(msoFileDialogFolderPicker)
With dlg
.Title = "Select the Excel file to import"
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excel", "*.xlsx; *.xls", 1
.Filters.Add "All Files", "*.*", 2
.InitialFileName = strFilePath
If .Show = True Then
strFilePath = .SelectedItems(1)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "IDNumber", strFilePath, True
Else
GoTo ExitProcedure
End If
End With
ExitProcedure:
Exit Sub
ErrorHandler:
MsgBox Err.Number & vbNewLine & Err.Description
Resume ExitProcedure
End Sub
--- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@...> wrote:
>
> You're welcome, Phucon. I think not seeing the problem is common to us all. Just earlier I tested a sub to use an ADO recordset as a form's recordset and it kept blowing up. I had an underscore instead of a hyphen in the server name.
>
> Then suddenly it popped out at me. Funny.
>
> Bill
>
> --- In MS_Access_Professionals@yahoogroups.com, "saigonf7q5" <saigonf7q5@> wrote:
> >
> >
> >
> >
> > Hello Mr.Mosca
> >
> > Thank you so much. Before I posted my question, I checked the code that I typed and compared it (like 5 times) to the code where I copied from. I didn't see the error I made at all (and I did have my glasses on though :-)) until you pointed it out. Perhaps it's not becaused I have poor eyesight, it's because the knowledge and experience that you have. Thanks again sir.
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@> wrote:
> > >
> > > Phucon
> > >
> > > You're using the folder picker. You need to change that to msoFileDialogFilePicker.
> > >
> > > Bill Mosca
> > >
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, "saigonf7q5" <saigonf7q5@> wrote:
> > > >
> > > > I copied the body of code from a book to use it for importing Excel files, when I ran it, it stopped at the line ".Filters.Add "Excel", "*.xlsx; *.xls", 1" . The error it gives out is "438 Object doesn't support this property or method".
> > > >
> > > > I thought it was version problem, but the version of Access that I am using is Access 2007, so does the code that I copied from is Access VBA 2007. What's mistake did I make?
> > > >
> > > > Private Sub cmdImport2_Click()
> > > > On Error GoTo ErrorHandler
> > > > Dim strFilePath As String
> > > > strFilePath = "C:\TempTestOnly\IDnumber.xlsx"
> > > >
> > > > Dim dlg As FileDialog
> > > > Set dlg = Application.FileDialog(msoFileDialogFolderPicker)
> > > > With dlg
> > > > .Title = "Select the Excel file to import"
> > > > .AllowMultiSelect = False
> > > > '.Filters.Clear
> > > > .Filters.Add "Excel", "*.xlsx; *.xls", 1
> > > > .Filters.Add "All Files", "*.*", 2
> > > > .InitialFileName = strFilePath
> > > > If .Show = True Then
> > > > strFilePath = .SelectedItems(1)
> > > > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "IDNumber", strFilePath, True
> > > > Else
> > > > GoTo ExitProcedure
> > > > End If
> > > > End With
> > > > ExitProcedure:
> > > > Exit Sub
> > > > ErrorHandler:
> > > > MsgBox Err.Number & vbNewLine & Err.Description
> > > > Resume ExitProcedure
> > > > End Sub
> > > >
> > > >
> > > > Phucon
> > > >
> > >
> >
>
Tidak ada komentar:
Posting Komentar