I am somewhat familar with access, but have no clue to modules and coding. I am trying to develop code or a module that will enable a user to import a specific excel spreadsheet and its' data into a table by pressing a command button. I am able to import excel, however it will not attach to existing spreadsheet. I find myself, having to build an append query for each import which is time consuming and inefficient. I have already checked, my excel columns have the same data. Essentially, I need my command button to browse for the excel file, and import one of the spreadsheets into a specified table. Please help.
Alaurie10@hotma il.com
4 2031 FishVal 2,653
Recognized Expert Specialist
Hi, there. -
I am able to import excel, however it will not attach to existing spreadsheet. I find myself, having to build an append query for each import which is time consuming and inefficient.
Programmatical iterating through spreadsheet and appending rows to a table will be far more inneficient and time consuming. I suggest you for import purposes to temporarily link Excel ranges and perform append query.
Something like -
With DoCmd
-
.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, _
-
"tblTempLink","X:\qqq.xls", , "Sheet2!A:D"
-
.RunSQL "INSERT INTO tblImport ( fld1, fld2, fld3, fld3) " & _
-
"SELECT tblTempLink.F1, tblTempLink.F2, tblTempLink.F3, tblTempLink.F4 " & _
-
"FROM tblTempLink;"
-
.DeleteObject acTable,"tblTempLink"
-
End With
-
- export is much more simple issue
-
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
-
"tblSomeTable", "X:\qqq1.xls", , "Sheet1!A:D"
-
- and finally concerning file browsing:
take a look at this thread file browse button on a form
Thanks FishVal:
I your assistance is greatly appreciated. Please pardon me if I am a little more than naive, but a couple of more questions if I may....
1. Do I paste the code you gave me into the Event Procedure of the command button?
2. I am unfamilar with the color coding, do I change the code written in red with my own?
3. I did perform the browsing code, in a module, and it worked. Do I paste this code before or after the import code that you gave me in the same module?
4. How do I link the code in the module to a macro that will enable me to call the code or link the module directly to the command button?
4. Finally, do I need to create the table that will hold my imported data first, or will access automatically create a temp import table with the code each time? If so, how can I keep the data in another table?
FishVal 2,653
Recognized Expert Specialist
Ok. To answer your questions I've written a small example.
DB contains:
Table: tblImport with 4 fields: fld1, fld2, fld3, fld4
Form: contains one button [btnImportXLData]
Here is the code of button [btnImportXLData] Click event handler. -
Private Sub btnImportXLData_Click()
-
-
Dim dlgPickFiles As Office.FileDialog
-
Dim strFileName As String
-
-
Set dlgPickFiles = Application.FileDialog(msoFileDialogFilePicker)
-
-
With dlgPickFiles
-
.AllowMultiSelect = False
-
With .Filters
-
.Clear
-
.Add "Excel files", "*.xls"
-
End With
-
.Show
-
strFileName = .SelectedItems(1)
-
End With
-
-
Set dlgPickFiles = Nothing
-
-
With DoCmd
-
.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, _
-
"tblTempLink", strFileName, False, "Sheet2!A:D"
-
.RunSQL "INSERT INTO tblImport (fld1, fld2, fld3, fld4) " & _
-
"SELECT tblTempLink.F1, tblTempLink.F2, tblTempLink.F3, tblTempLink.F4 " & _
-
"FROM tblTempLink;"
-
.DeleteObject acTable, "tblTempLink"
-
End With
-
-
End Sub
-
This simple example imports data to table [tblImport] from chosen XL file, Sheet2, Columns A:B. Get this working and will proceed to more friendly interface including selection of sheet to import data from.
Not a problem, give me a couple of days, Have a trainning and unfortunately it is not for programming. Will get this going by Friday.
Thanks
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: RustyR |
last post by:
Hi,
I have 5 excel spreadsheets that are exported to me weekly.
At first, I could not get them to import. Then I did a File -> Save as and
noticed that the Filenames of the spreadsheets contained Quotes around
them - hence the problem.
Is there any way to remove those quotes prior to import (other than "Save
As")???
|
by: Mads Petersen |
last post by:
I'm stuck in this code. Hope you can and will help me. I launch it
from excel.
I have made the following code work, but not as i whant. I need the
ranges to be working with something like xlDown.
eg. this only transferes the first record in the area.
..Fields("Uge").Value = ws.Range("A98").Value
Sub SelectMaster()
|
by: T2 |
last post by:
I need some code ideas to import Excel into Access using
VB. I need to use VB because the Excel column formats are
not always consistent.
I'm importing a fairly simple spreadsheet.
Any assistance would be greatly appreciated.
|
by: winzy |
last post by:
I have a table in Access which needs updating from an Excel file.
Instead of importing the whole Excel like a new table in Acess, how do
I do a partial import/update??
For example, Access table has 100 rows of records and 10 columns of
attributes. The Excel table has only 50 rows of records and and 2
attributes. The key IDs for the 50 rows...
|
by: mukeshsrivastav |
last post by:
dear sir
i want to move form excel to access.
i have 5 excel file having same formats and fields.now i want to import all data in one access table.
importing one file is easy .but importing and appending second file in last of the access table created earlier by importing data.
thanks .plz help me.
| |
by: kkadakia |
last post by:
I get a daily excel file for a entire month which I want to transfer into Access at the end of the month. So, there are around 20-25 excel files I get by the end of the month, and I would like to import them into Access using a button. The data contained in the excel files is similar, so there should no formatting issues while importing.
I...
|
by: upadhyayanuj |
last post by:
Hi I have an error while uploading file and then importing it into my sql table
The process cannot access the file 'c:\inetpub\wwwroot\FlowLine\Excel File\Fan_Details.xls' because it is being used by another process
Now Some times its run successfully, i don't no how but somtimes it gives error,
I figure out there is security issue of...
|
by: OfficeDummy |
last post by:
Hello all!
I searched the Internet and this forum for a similar problem, but I found no help... Also, I'm a complete newbie to the fascinating world of programming, VBA and Access, so my question can very well be very stupid.
The tasks are:
1)Import an Excel table into Access
2)Add a new column and fill it with variables of date/time...
|
by: keirnus |
last post by:
hello,
been searching for a solution on this but all i get are advices for importing data from excel directly to MS Access DB.
my case here is different...the sheet in Excel file is not ready for importing...
the data are scattered from different cells from different sheets...
this is because these sheets are automated for their own...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |