Open Excel file and copy any sheet into Access table (1 Viewer)
- Thread starter Moore71
- Start date
Ranman256
Well-known member
- Local time
- Today, 14:33
- Joined
- Apr 9, 2015
- Messages
- 4,339
you use either: transferspreadsheet
or
attach the sheet as an external table , then run import query
or
save as to the same file everytime: c:\temp\File2Import.xlsx
attach this file as an external linked table: tFile2Import
build an append query to add this data to the internal data table
put this query into a macro: mImportXL
then the steps will be:
1. overwrite the file, c:\temp\File2Import.xlsx, with the new data
2. run import macro: mImportXL
done
or
attach the sheet as an external table , then run import query
Code:
vFile = "c:\folder\myFile.xlsx"
vQry = "qsExport2Excel"
docmd.TransferSpreadsheet acImport ,acSpreadsheetTypeExcel12,vQry, vFile, true,vSheetNameor
save as to the same file everytime: c:\temp\File2Import.xlsx
attach this file as an external linked table: tFile2Import
build an append query to add this data to the internal data table
put this query into a macro: mImportXL
then the steps will be:
1. overwrite the file, c:\temp\File2Import.xlsx, with the new data
2. run import macro: mImportXL
done
Do you mean that you want to choose the excel file and it will import every sheet in the file, or do you mean you want to select a sheet or sheets within the excel file (file contains 5 tabs and you want to import sheet 3 and 4)?
I mean import any sheet (one sheet at a time) from one single excel file.
I want access to open excel and give me opportunity to select whichever of the sheet I want to import (whatever range in the sheet), and when I close excel, the selected sheet/range is imported into my access table.
Thank you for response
I want access to open excel and give me opportunity to select whichever of the sheet I want to import (whatever range in the sheet), and when I close excel, the selected sheet/range is imported into my access table.
Thank you for response
I extracted this from an application I have. Form opens based on a selection from another form and am assuming that you already have a method set up to determine the file name. Otherwise you would need to modify it.
Looks like you are using "vFile", so you can either change the name of my code from stImport to vFile, or the other way around.
This would go in the place where you select the excel file.
Then you would add the form into your existing access file
I extracted from my program quickly, so haven't tested it, I commented out the lines of code that won't apply to your situation. However, left it in because perhaps you may want to do something similar if you want to keep the form open to select another tab from the same file and this would give you a starting point for that.
Assuming it will work as is, the form will function properly when the excel file is chosen from your first form, then the code line above will cause the form allowing you to choose which excel sheet tab to import will open. As I'm passing data back and forth between two forms, you may need to figure out passing the value back or if will run the transfer spreadsheet within this form. Alternatively, you might copy the code elements to select the tab you need from this form into your existing form and not deal with pop up form stuff.
Looks like you are using "vFile", so you can either change the name of my code from stImport to vFile, or the other way around.
This would go in the place where you select the excel file.
Code:
DoCmd.OpenForm "frmTabList", Windowmode:=acDialog, OpenArgs:=stImportI extracted from my program quickly, so haven't tested it, I commented out the lines of code that won't apply to your situation. However, left it in because perhaps you may want to do something similar if you want to keep the form open to select another tab from the same file and this would give you a starting point for that.
Assuming it will work as is, the form will function properly when the excel file is chosen from your first form, then the code line above will cause the form allowing you to choose which excel sheet tab to import will open. As I'm passing data back and forth between two forms, you may need to figure out passing the value back or if will run the transfer spreadsheet within this form. Alternatively, you might copy the code elements to select the tab you need from this form into your existing form and not deal with pop up form stuff.
