Action: TransferSpreadsheet, Transfer Type: Import Paul Dennis 14.09.2006 11:17:01 I have a Module which uses TransferSpreadsheet to imports an Excel file and it works fine, however I want to import multiple xls files which are all the same just with different data. What I would like to do is to use a Module that imports all xls files in a directory, this way I don't have to import one file at a time and then I can save the file to another directory for reference. The code I currently have is which finds the files and moves them is: On Error Resume Next Dim strCurrFile As String Dim strCurrFolder As String Dim strNewFolder As String strCurrFolder = "C:\Service Improvement\ToDo\" strNewFolder = "C:\Service Improvement\Done\" strCurrFile = Dir(strCurrFolder & "*.*") ' Dir Do While Len(strCurrFile) > 0 <*** Need to enter XLS import text here ***> Name strCurrFolder & strCurrFile As strNewFolder & strCurrFile strCurrFile = Dir() Loop Can you help confirm the action statement and syntax to accomplish this please?

Re: Action: TransferSpreadsheet, Transfer Type: Import "Ken Snell \(MVP\)" <kthsneisllis9[ at ]ncoomcastt.renaetl> 14.09.2006 12:09:29 Same loop logic and use of Dir function. Just include a DoCmd.TransferSpreadsheet action in the loop and give it the location (path and file name) of the EXCEL file that is to be imported. -- Ken Snell <MS ACCESS MVP> "Paul Dennis" <PaulDennis[ at ]discussions.microsoft.com> wrote in message news:DA57947D-D1BF-4FEB-88F4-20BC23AAC416[ at ]microsoft.com... [Quoted Text] >I have a Module which uses TransferSpreadsheet to imports an Excel file and > it works fine, however I want to import multiple xls files which are all > the > same just with different data. > > What I would like to do is to use a Module that imports all xls files in a > directory, this way I don't have to import one file at a time and then I > can > save the file to another directory for reference. The code I currently > have > is which finds the files and moves them is: > > On Error Resume Next > > Dim strCurrFile As String > Dim strCurrFolder As String > Dim strNewFolder As String > > strCurrFolder = "C:\Service Improvement\ToDo\" > strNewFolder = "C:\Service Improvement\Done\" > strCurrFile = Dir(strCurrFolder & "*.*") > ' Dir > Do While Len(strCurrFile) > 0 > <*** Need to enter XLS import text here ***> > Name strCurrFolder & strCurrFile As strNewFolder & strCurrFile > strCurrFile = Dir() > Loop > > Can you help confirm the action statement and syntax to accomplish this > please?

Re: Action: TransferSpreadsheet, Transfer Type: Import Paul Dennis 14.09.2006 13:34:01 Thanks for this but can you help as my syntax is incorrect....I'm getting a compile error. DoCmd.TransferSpreadsheet(acImport,acSpreadsheetTypeExcel9,[SIR Imported],strCurrName,Yes,ReqInput) [SIR Imported] = tables name to import to strCurrName = is the file name including directory e.g."C:\Service Improvement\ToDo\Service Improvement Request Form - Paul Dennis.xls" ReqInput = name of the xls sheet to import "Ken Snell (MVP)" wrote: [Quoted Text] > Same loop logic and use of Dir function. Just include a > DoCmd.TransferSpreadsheet action in the loop and give it the location (path > and file name) of the EXCEL file that is to be imported. > > -- > > Ken Snell > <MS ACCESS MVP> > > "Paul Dennis" <PaulDennis[ at ]discussions.microsoft.com> wrote in message > news:DA57947D-D1BF-4FEB-88F4-20BC23AAC416[ at ]microsoft.com... > >I have a Module which uses TransferSpreadsheet to imports an Excel file and > > it works fine, however I want to import multiple xls files which are all > > the > > same just with different data. > > > > What I would like to do is to use a Module that imports all xls files in a > > directory, this way I don't have to import one file at a time and then I > > can > > save the file to another directory for reference. The code I currently > > have > > is which finds the files and moves them is: > > > > On Error Resume Next > > > > Dim strCurrFile As String > > Dim strCurrFolder As String > > Dim strNewFolder As String > > > > strCurrFolder = "C:\Service Improvement\ToDo\" > > strNewFolder = "C:\Service Improvement\Done\" > > strCurrFile = Dir(strCurrFolder & "*.*") > > ' Dir > > Do While Len(strCurrFile) > 0 > > <*** Need to enter XLS import text here ***> > > Name strCurrFolder & strCurrFile As strNewFolder & strCurrFile > > strCurrFile = Dir() > > Loop > > > > Can you help confirm the action statement and syntax to accomplish this > > please? > > >

Re: Action: TransferSpreadsheet, Transfer Type: Import "Ken Snell \(MVP\)" <kthsneisllis9[ at ]ncoomcastt.renaetl> 14.09.2006 14:34:09 Remove the parentheses: DoCmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel9,[SIR Imported],strCurrName,Yes,ReqInput -- Ken Snell <MS ACCESS MVP> "Paul Dennis" <PaulDennis[ at ]discussions.microsoft.com> wrote in message news:DEBF0B49-E5BE-4C3B-8BBB-DCDE84B2A390[ at ]microsoft.com... [Quoted Text] > Thanks for this but can you help as my syntax is incorrect....I'm getting > a > compile error. > DoCmd.TransferSpreadsheet(acImport,acSpreadsheetTypeExcel9,[SIR > Imported],strCurrName,Yes,ReqInput) > > [SIR Imported] = tables name to import to > > strCurrName = is the file name including directory e.g."C:\Service > Improvement\ToDo\Service Improvement Request Form - Paul Dennis.xls" > > ReqInput = name of the xls sheet to import > > "Ken Snell (MVP)" wrote: > >> Same loop logic and use of Dir function. Just include a >> DoCmd.TransferSpreadsheet action in the loop and give it the location >> (path >> and file name) of the EXCEL file that is to be imported. >> >> -- >> >> Ken Snell >> <MS ACCESS MVP> >> >> "Paul Dennis" <PaulDennis[ at ]discussions.microsoft.com> wrote in message >> news:DA57947D-D1BF-4FEB-88F4-20BC23AAC416[ at ]microsoft.com... >> >I have a Module which uses TransferSpreadsheet to imports an Excel file >> >and >> > it works fine, however I want to import multiple xls files which are >> > all >> > the >> > same just with different data. >> > >> > What I would like to do is to use a Module that imports all xls files >> > in a >> > directory, this way I don't have to import one file at a time and then >> > I >> > can >> > save the file to another directory for reference. The code I currently >> > have >> > is which finds the files and moves them is: >> > >> > On Error Resume Next >> > >> > Dim strCurrFile As String >> > Dim strCurrFolder As String >> > Dim strNewFolder As String >> > >> > strCurrFolder = "C:\Service Improvement\ToDo\" >> > strNewFolder = "C:\Service Improvement\Done\" >> > strCurrFile = Dir(strCurrFolder & "*.*") >> > ' Dir >> > Do While Len(strCurrFile) > 0 >> > <*** Need to enter XLS import text here ***> >> > Name strCurrFolder & strCurrFile As strNewFolder & strCurrFile >> > strCurrFile = Dir() >> > Loop >> > >> > Can you help confirm the action statement and syntax to accomplish this >> > please? >> >> >>

Re: Action: TransferSpreadsheet, Transfer Type: Import Paul Dennis 14.09.2006 17:54:02 It works and it doesn't, i.e. syntax is now fine and it executes however it doesn't import any records. I have checked all the parameters and they are ok. No errors pop up and no error table is generated. Any ideas ? "Ken Snell (MVP)" wrote: [Quoted Text] > Remove the parentheses: > > DoCmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel9,[SIR > Imported],strCurrName,Yes,ReqInput > > > -- > > Ken Snell > <MS ACCESS MVP> > > "Paul Dennis" <PaulDennis[ at ]discussions.microsoft.com> wrote in message > news:DEBF0B49-E5BE-4C3B-8BBB-DCDE84B2A390[ at ]microsoft.com... > > Thanks for this but can you help as my syntax is incorrect....I'm getting > > a > > compile error. > > DoCmd.TransferSpreadsheet(acImport,acSpreadsheetTypeExcel9,[SIR > > Imported],strCurrName,Yes,ReqInput) > > > > [SIR Imported] = tables name to import to > > > > strCurrName = is the file name including directory e.g."C:\Service > > Improvement\ToDo\Service Improvement Request Form - Paul Dennis.xls" > > > > ReqInput = name of the xls sheet to import > > > > "Ken Snell (MVP)" wrote: > > > >> Same loop logic and use of Dir function. Just include a > >> DoCmd.TransferSpreadsheet action in the loop and give it the location > >> (path > >> and file name) of the EXCEL file that is to be imported. > >> > >> -- > >> > >> Ken Snell > >> <MS ACCESS MVP> > >> > >> "Paul Dennis" <PaulDennis[ at ]discussions.microsoft.com> wrote in message > >> news:DA57947D-D1BF-4FEB-88F4-20BC23AAC416[ at ]microsoft.com... > >> >I have a Module which uses TransferSpreadsheet to imports an Excel file > >> >and > >> > it works fine, however I want to import multiple xls files which are > >> > all > >> > the > >> > same just with different data. > >> > > >> > What I would like to do is to use a Module that imports all xls files > >> > in a > >> > directory, this way I don't have to import one file at a time and then > >> > I > >> > can > >> > save the file to another directory for reference. The code I currently > >> > have > >> > is which finds the files and moves them is: > >> > > >> > On Error Resume Next > >> > > >> > Dim strCurrFile As String > >> > Dim strCurrFolder As String > >> > Dim strNewFolder As String > >> > > >> > strCurrFolder = "C:\Service Improvement\ToDo\" > >> > strNewFolder = "C:\Service Improvement\Done\" > >> > strCurrFile = Dir(strCurrFolder & "*.*") > >> > ' Dir > >> > Do While Len(strCurrFile) > 0 > >> > <*** Need to enter XLS import text here ***> > >> > Name strCurrFolder & strCurrFile As strNewFolder & strCurrFile > >> > strCurrFile = Dir() > >> > Loop > >> > > >> > Can you help confirm the action statement and syntax to accomplish this > >> > please? > >> > >> > >> > > >

Re: Action: TransferSpreadsheet, Transfer Type: Import Paul Dennis 14.09.2006 18:16:01 sorted thx - the Yes needed to be True thanks for your help "Ken Snell (MVP)" wrote: [Quoted Text] > Remove the parentheses: > > DoCmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel9,[SIR > Imported],strCurrName,Yes,ReqInput > > > -- > > Ken Snell > <MS ACCESS MVP> > > "Paul Dennis" <PaulDennis[ at ]discussions.microsoft.com> wrote in message > news:DEBF0B49-E5BE-4C3B-8BBB-DCDE84B2A390[ at ]microsoft.com... > > Thanks for this but can you help as my syntax is incorrect....I'm getting > > a > > compile error. > > DoCmd.TransferSpreadsheet(acImport,acSpreadsheetTypeExcel9,[SIR > > Imported],strCurrName,Yes,ReqInput) > > > > [SIR Imported] = tables name to import to > > > > strCurrName = is the file name including directory e.g."C:\Service > > Improvement\ToDo\Service Improvement Request Form - Paul Dennis.xls" > > > > ReqInput = name of the xls sheet to import > > > > "Ken Snell (MVP)" wrote: > > > >> Same loop logic and use of Dir function. Just include a > >> DoCmd.TransferSpreadsheet action in the loop and give it the location > >> (path > >> and file name) of the EXCEL file that is to be imported. > >> > >> -- > >> > >> Ken Snell > >> <MS ACCESS MVP> > >> > >> "Paul Dennis" <PaulDennis[ at ]discussions.microsoft.com> wrote in message > >> news:DA57947D-D1BF-4FEB-88F4-20BC23AAC416[ at ]microsoft.com... > >> >I have a Module which uses TransferSpreadsheet to imports an Excel file > >> >and > >> > it works fine, however I want to import multiple xls files which are > >> > all > >> > the > >> > same just with different data. > >> > > >> > What I would like to do is to use a Module that imports all xls files > >> > in a > >> > directory, this way I don't have to import one file at a time and then > >> > I > >> > can > >> > save the file to another directory for reference. The code I currently > >> > have > >> > is which finds the files and moves them is: > >> > > >> > On Error Resume Next > >> > > >> > Dim strCurrFile As String > >> > Dim strCurrFolder As String > >> > Dim strNewFolder As String > >> > > >> > strCurrFolder = "C:\Service Improvement\ToDo\" > >> > strNewFolder = "C:\Service Improvement\Done\" > >> > strCurrFile = Dir(strCurrFolder & "*.*") > >> > ' Dir > >> > Do While Len(strCurrFile) > 0 > >> > <*** Need to enter XLS import text here ***> > >> > Name strCurrFolder & strCurrFile As strNewFolder & strCurrFile > >> > strCurrFile = Dir() > >> > Loop > >> > > >> > Can you help confirm the action statement and syntax to accomplish this > >> > please? > >> > >> > >> > > >

Re: Action: TransferSpreadsheet, Transfer Type: Import "Ken Snell \(MVP\)" <kthsneisllis9[ at ]ncoomcastt.renaetl> 14.09.2006 23:16:43 Sorry about that.. I missed the Yes entirely when I read your post. -- Ken Snell <MS ACCESS MVP> "Paul Dennis" <PaulDennis[ at ]discussions.microsoft.com> wrote in message news:29F14968-A65B-49CA-A57A-50F5709D1BA8[ at ]microsoft.com... [Quoted Text] > sorted thx - the Yes needed to be True > > thanks for your help > > "Ken Snell (MVP)" wrote: > >> Remove the parentheses: >> >> DoCmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel9,[SIR >> Imported],strCurrName,Yes,ReqInput >> >> >> -- >> >> Ken Snell >> <MS ACCESS MVP> >> >> "Paul Dennis" <PaulDennis[ at ]discussions.microsoft.com> wrote in message >> news:DEBF0B49-E5BE-4C3B-8BBB-DCDE84B2A390[ at ]microsoft.com... >> > Thanks for this but can you help as my syntax is incorrect....I'm >> > getting >> > a >> > compile error. >> > DoCmd.TransferSpreadsheet(acImport,acSpreadsheetTypeExcel9,[SIR >> > Imported],strCurrName,Yes,ReqInput) >> > >> > [SIR Imported] = tables name to import to >> > >> > strCurrName = is the file name including directory e.g."C:\Service >> > Improvement\ToDo\Service Improvement Request Form - Paul Dennis.xls" >> > >> > ReqInput = name of the xls sheet to import >> > >> > "Ken Snell (MVP)" wrote: >> > >> >> Same loop logic and use of Dir function. Just include a >> >> DoCmd.TransferSpreadsheet action in the loop and give it the location >> >> (path >> >> and file name) of the EXCEL file that is to be imported. >> >> >> >> -- >> >> >> >> Ken Snell >> >> <MS ACCESS MVP> >> >> >> >> "Paul Dennis" <PaulDennis[ at ]discussions.microsoft.com> wrote in message >> >> news:DA57947D-D1BF-4FEB-88F4-20BC23AAC416[ at ]microsoft.com... >> >> >I have a Module which uses TransferSpreadsheet to imports an Excel >> >> >file >> >> >and >> >> > it works fine, however I want to import multiple xls files which are >> >> > all >> >> > the >> >> > same just with different data. >> >> > >> >> > What I would like to do is to use a Module that imports all xls >> >> > files >> >> > in a >> >> > directory, this way I don't have to import one file at a time and >> >> > then >> >> > I >> >> > can >> >> > save the file to another directory for reference. The code I >> >> > currently >> >> > have >> >> > is which finds the files and moves them is: >> >> > >> >> > On Error Resume Next >> >> > >> >> > Dim strCurrFile As String >> >> > Dim strCurrFolder As String >> >> > Dim strNewFolder As String >> >> > >> >> > strCurrFolder = "C:\Service Improvement\ToDo\" >> >> > strNewFolder = "C:\Service Improvement\Done\" >> >> > strCurrFile = Dir(strCurrFolder & "*.*") >> >> > ' Dir >> >> > Do While Len(strCurrFile) > 0 >> >> > <*** Need to enter XLS import text here ***> >> >> > Name strCurrFolder & strCurrFile As strNewFolder & strCurrFile >> >> > strCurrFile = Dir() >> >> > Loop >> >> > >> >> > Can you help confirm the action statement and syntax to accomplish >> >> > this >> >> > please? >> >> >> >> >> >> >> >> >>