473,320 Members | 2,158 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Import from excel to access

Tabasco
25
Is it possible to preset all the steps in import from excel buttom in access in vba? Or well, not all the steps the user should only need to find the excel file on the computor?

ive tried the follow syntax

Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Sheet1", "C:\test.xls", True
  2.  
however, the excelfile alwas change name so i want the user to be able to search (and fill out) that part
Dec 20 '12 #1

✓ answered by ADezii

In my opinion, the simplest approach would be to:
  1. Open the standard Office Dialog filtered for Excel Files only and allow the User to select only a single Excel File (*.xls). You can also allow other Excel Extensions if needed.
  2. Import the Selected Excel File into a Table named Sheet1. This can also be easily modified for varying Table Names.
    Expand|Select|Wrap|Line Numbers
    1. 'Must 1st set a Reference to the Microsoft Office XX.X Object Library
    2. Dim dlgOpen As FileDialog
    3.  
    4. Set dlgOpen = Application.FileDialog(msoFileDialogOpen)
    5.  
    6. With dlgOpen
    7.   .AllowMultiSelect = False         'Only a Single File
    8.   .ButtonName = "Import"
    9.   .InitialView = msoFileDialogViewLargeIcons
    10.   .InitialFileName = CurrentProject.Path
    11.     .Filters.Clear
    12.     .Filters.Add "Excel Files", "*.xls"
    13.        If .Show = -1 Then
    14.          DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Sheet1", _
    15.                                    .SelectedItems(1), True
    16.        End If
    17. End With
    18.  
    19. 'Set the object variable to Nothing.
    20. Set dlgOpen = Nothing

3 3094
ADezii
8,834 Expert 8TB
In my opinion, the simplest approach would be to:
  1. Open the standard Office Dialog filtered for Excel Files only and allow the User to select only a single Excel File (*.xls). You can also allow other Excel Extensions if needed.
  2. Import the Selected Excel File into a Table named Sheet1. This can also be easily modified for varying Table Names.
    Expand|Select|Wrap|Line Numbers
    1. 'Must 1st set a Reference to the Microsoft Office XX.X Object Library
    2. Dim dlgOpen As FileDialog
    3.  
    4. Set dlgOpen = Application.FileDialog(msoFileDialogOpen)
    5.  
    6. With dlgOpen
    7.   .AllowMultiSelect = False         'Only a Single File
    8.   .ButtonName = "Import"
    9.   .InitialView = msoFileDialogViewLargeIcons
    10.   .InitialFileName = CurrentProject.Path
    11.     .Filters.Clear
    12.     .Filters.Add "Excel Files", "*.xls"
    13.        If .Show = -1 Then
    14.          DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Sheet1", _
    15.                                    .SelectedItems(1), True
    16.        End If
    17. End With
    18.  
    19. 'Set the object variable to Nothing.
    20. Set dlgOpen = Nothing
Dec 20 '12 #2
NeoPa
32,556 Expert Mod 16PB
Any parameter can be passed as a variable rather than a literal reference. This means anything that is passed as a parameter (and this includes the names of the Access object to export as well as the name of the file) can be determined in advance using whatever code you want to use, then passed to the procedure. Essentially then, the answer to your question is "Yes, as far as the parameters are concerned".
Dec 20 '12 #3
Tabasco
25
It worked beautifully! Thank you very much! This is much more simple than to use Acces buttom "Import from excel"
Jan 9 '13 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Jarod | last post by:
Hey How to import excel to my application ? I need access to all fileds in spreadsheets just to take data. Excel file works as a "dictionary" in my case. Can you tell me how to use it in...
1
by: Reggae | last post by:
Hello, I am looking for the most efficient way to programmatically batch import excel files into MS Access. The excel files are not lined up in a row and currently I am going to each file so I...
5
nehashri
by: nehashri | last post by:
hello i have my data stored in different Ms Excell sheets. now I am planning to make a database of the data i have. but i m confused whether to use Postgresql or mySQL for the same. i had plans to...
3
by: vj83 | last post by:
Hi, I have a C#.net application in which i have read the datas from excel sheet and displayed in a datagrid in my Aspx form. The code is here private void Button2_Click(object sender,...
2
by: solargovind | last post by:
Hi, Can anybody suggest me how to take data from an Excel file without import into Access as a Table..? Bcoz...I hve several file. if i import, it would take more time and increase the file...
2
by: Dave Williams | last post by:
Im not a techie but run a small business in UK. We take instructions from clients from emails hard copy, spreadsheets etc and put them on excel templates then import to Access. A client wants us to...
0
by: satenova | last post by:
Hello Friends, I am newbie when it comes to vb.net and i need to import excel sheet into access db using vb.net application. Here is the code i use for now to upload excel sheet and it works...
8
by: qfchen | last post by:
Hi, A piece of simple code to import excel file to a database, as shown below, I have problem when I tried to open the connection, the error message shows Excel driver is not correct. where shall...
1
by: jollyroger | last post by:
I have searched the web forums, and can't seem to find an answer to this particular problem I have. In an excel sheet, cells in one column have formatted text in the "wrapped" cells. For many of...
4
by: dowlingm815 | last post by:
I am importing an Excel 97-2003 file into Access 2003. The number of rows within the Excel file is 101,9992. When completed the Access import, only 65,535 are import. Is Access limited, if so,...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.