A.D.
You are absolutely right. I worked on the code to link to the sheet and kept getting errors. Now that I think of it I did not add the dollar sign in my attempts.
But, hey, that's okay. I expanded my knowledge of automation.
Bill
--- In MS_Access_Professionals@yahoogroups.com, "A.D. Tejpal" <adtp@...> wrote:
>
> Bill,
>
> Interestingly, it is seen from tests at my end (Access 2003 and Excel 2003) that direct use of excel worksheet's name, suffixed with "$", also works, without first naming a range as per that sheet's name. For example, the statement below, on its own, is found ok:
>
> '===============================
> DoCmd.TransferSpreadsheet acImport, _
> , ImpTblName, ExcelFilePath, True, _
> SheetName & "$"
> '===============================
>
> Perhaps you might like to cross check, as it would be helpful to investigate the circumstances under which, such an approach might not succeed.
>
> Best wishes,
> A.D. Tejpal
> ------------
>
> ----- Original Message -----
> From: Bill Mosca
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Wednesday, August 08, 2012 02:15
> Subject: [MS_AccessPros] Re: Import Excel spreadsheet into Access
>
>
> Phucon
>
> I just went through this nightmare. You have to specify a range name. If the entire sheet is what you want to import you can do it this way:
>
> To name the sheet as a range (Note my global constant which is the name of the sheet I want). Call this function before you run yours to import.
>
> Option Compare Database
> Option Explicit
> Private Const pconSheet As String = "Subscriber"
>
> Public Function NameRange(strfile As String) As Boolean
> 'Purpose : Name UsedRange as a range on the selected sheet.
> 'DateTime : 8/06/2012 11:28
> 'Author : Bill Mosca
> 'Requires :
> Dim objXL As Object 'Excel.Application
> Dim objWrkBk As Object 'Excel.Workbook
> Dim objSheet As Object 'Excel.Worksheet
> Dim objRange As Object 'Excel.Range
>
> If gbolErrorTrapOff = False Then On Error GoTo err_PROC
>
> Set objXL = CreateObject("Excel.Application")
> Set objWrkBk = objXL.Workbooks.Open(strfile)
> Set objSheet = objWrkBk.Sheets(pconSheet)
> Set objRange = objSheet.UsedRange
> objRange.Name = pconSheet
> objWrkBk.Save
>
>
> NameRange = True
>
> exit_PROC:
> On Error Resume Next
> Set objRange = Nothing
> Set objSheet = Nothing
> objWrkBk.Close
> objXL.Quit
> Set objWrkBk = Nothing
> Set objXL = Nothing
> Exit Function
> End Function
>
> Then add the range to your TransferSpreadsheet line like this:
> DoCmd.TransferSpreadsheet acImport, _
> acSpreadsheetTypeExcel9, "IDNumber", strFilePath, _
> True, pconSheet & "$"
>
> Be sure to use the $ so the function knows it's a range name.
>
> Regards,
> Bill Mosca, Founder - MS_Access_Professionals
> http://www.thatlldoit.com
> Microsoft Office Access MVP
> https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E
> My nothing-to-do-with-Access blog
> http://wrmosca.wordpress.com
>
>
> --- In MS_Access_Professionals@yahoogroups.com, "saigonf7q5" <saigonf7q5@> wrote:
> >
> > 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
>
> [Non-text portions of this message have been removed]
>
Tidak ada komentar:
Posting Komentar