473,735 Members | 2,090 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Access Import from Excel

Hello everybody, your help will be very very welcome on a very classic
question...

I have some Excell Macro background, a little bit of VBA knowledge.
I import daily from Internet series of data. They are setup as follow,
with a few more columns on the rights as stock prices, dividend and so
on as:

Date Code Country Adjust Reason Name Shares
25/02/2005 FR0000045072 FR CREDIT AGRICOLE
25/02/2005 FR0000054900 FR TF1

I have setup an excel macro that save the date once I have downloaded
them, with a sheet name including the daily date as:
DivivendData28_ Feb_05. Tomorrow it will thus be saved as
DivivendData29_ Feb_05

Here difficulties arise.

I wish to store these data in a single access database. I did begin in
a naïve way as I am not an expert at excel. I did create a blank
database and did manually import one the spreadsheet. Went fine. But as
I import the second one I do receive error messages stating that:
"Method 'Columns' of object 'IllmexGrid'fai led" and then
"File was not imported"

Anyway, I would like to automatize from Access or Excel, if it is
possible.

Regards
Daniel

Nov 13 '05 #1
15 4215
Rog
Check out the TransferSpreads heet method in the help file.

Use a variable for the filename argument, e.g.
strFileName = "DividendDa ta" & Format(Date, "dd") & "_" * Format(Date,
"mmm") & "_" & Format(Date, "yy")

....although I would recommend using the yyyymmdd format. Oh, and I
think you will find tomorrow is not February 29 :-)


da*****@gmail.c om wrote:
Hello everybody, your help will be very very welcome on a very classic question...

I have some Excell Macro background, a little bit of VBA knowledge.
I import daily from Internet series of data. They are setup as follow, with a few more columns on the rights as stock prices, dividend and so on as:

Date Code Country Adjust Reason Name Shares
25/02/2005 FR0000045072 FR CREDIT AGRICOLE
25/02/2005 FR0000054900 FR TF1

I have setup an excel macro that save the date once I have downloaded
them, with a sheet name including the daily date as:
DivivendData28_ Feb_05. Tomorrow it will thus be saved as
DivivendData29_ Feb_05

Here difficulties arise.

I wish to store these data in a single access database. I did begin in a naïve way as I am not an expert at excel. I did create a blank
database and did manually import one the spreadsheet. Went fine. But as I import the second one I do receive error messages stating that:
"Method 'Columns' of object 'IllmexGrid'fai led" and then
"File was not imported"

Anyway, I would like to automatize from Access or Excel, if it is
possible.

Regards
Daniel


Nov 13 '05 #2
Thanks, I am having a look I come back to you.

Good spot I just realized what year are we ...

Nov 13 '05 #3
I do not want to abuse from your kindness but I am really dumb, I have
a problem on the file name setup.
Here is how the excel macro save the daily file on a folder:

ChDir "C:\Documen ts and Settings\Daniel \My Documents\Divid end and
Index Pricing\CAC 40\Dividend Data"
ActiveWorkbook. SaveAs Filename:= _
"C:\Documen ts and Settings\Daniel \My Documents\Divid end and
Index Pricing\CAC 40\Dividend Data" & _
Format(Date, "dd_mmm_yy" ) & ".xls" _
, FileFormat:=xlN ormal, Password:="", WriteResPasswor d:="", _
ReadOnlyRecomme nded:=False, CreateBackup:=F alse

Thus, what should I write in the FileName field in Access ? I know I
push a little bit far away the boundary of stupidity ...

Regards
DR

Nov 13 '05 #4
Rog
dani,
Never underestimate yourself :-)

dim strFilename as string
strFilename = Format(Date, "dd_mmm_yy" ) & ".xls"
docmd.transfers preadsheet acImport, , Table1, strFilename

You can leave the second argument blank (hence 2 commas after
acImport).
Replace Table1 by your table name.
Add another argument , True after strFilename if the spreasheets have a
first row containing the field names.

Hope this helps.

Nov 13 '05 #5
Thanks mate, but do not overestimate me neither as I will ask you
something really dumb.
In Access, I click on the left panel on macro.
I am trying to enter the function in the field name. Obviously it does
not work at ALL. I am missing a few points and steps isn'it ? How do
you even access the Visual Basic panel on Access? I am really Access
illiterate...

Regards
DR

Nov 13 '05 #6
Rog
Dani,

OK, we're not on the same page here. I'm not talking about a macro,
but about VB code. You cannot make a "flexible" macro, all the
arguments are fixed. So what you need to do is write code to
accomplish this. Although there are other methods, the easiest thing
to do is create a form and, in design mode, place a command button on
it. Cancel out of the command button wizard if that opens. Select the
button, go into its properties (the properties window should be open
but if it isn't, right click and select properties), select the Event
tab, Select the On Click property, click on the three dots next to it,
and select Code builder. Now, after the Private sub Commandx_Click
line, enter the code I gave you. Save the form, open it and click the
button to perform the import.

Nov 13 '05 #7
Thank you once again
I know that I am a real pain in the ...

I am thus editing the code like this

Private Sub Command0_Click( )

Dim strFilename As String
strFilename = Format(Date, "dd_mmm_yy" ) & ".xls"
DoCmd.TransferS preadsheet acImport, , CAC40_Weighting s, strFilename,
True

End Sub

But when I run it by clicking on the form button, the debugger opens
with a pop-up window stating that:
"Compile Error
Expected function or variable"
highlighting "CAC40_Weightin gs" in the code, that is to say the table I
want to update.

If you want to call it a day and tell me to ... off I can understand as
it is a complete waste of time for you ! ,-)

Meanwhile, regards
Daniel

Nov 13 '05 #8
How about this:

Unless you have like a gazillion tabs in your spreadsheet, just copy
and paste!

Since you have done one successfull import already, you should have a
table in your database. Go into excel, select the data you want to
import (that you havn't before) and select it all. Do an Edit->Copy.

Go back into access and open the table. Scroll down to the bottom of
the table and select the last (blank) line. Then go to Edit -> Paste
Append.

Repeat as necessary.

If the table is setup correctly, it should correctly interpret all of
the fields.

Alex.

da*****@gmail.c om wrote:
Hello everybody, your help will be very very welcome on a very classic question...

I have some Excell Macro background, a little bit of VBA knowledge.
I import daily from Internet series of data. They are setup as follow, with a few more columns on the rights as stock prices, dividend and so on as:

Date Code Country Adjust Reason Name Shares
25/02/2005 FR0000045072 FR CREDIT AGRICOLE
25/02/2005 FR0000054900 FR TF1

I have setup an excel macro that save the date once I have downloaded
them, with a sheet name including the daily date as:
DivivendData28_ Feb_05. Tomorrow it will thus be saved as
DivivendData29_ Feb_05

Here difficulties arise.

I wish to store these data in a single access database. I did begin in a naïve way as I am not an expert at excel. I did create a blank
database and did manually import one the spreadsheet. Went fine. But as I import the second one I do receive error messages stating that:
"Method 'Columns' of object 'IllmexGrid'fai led" and then
"File was not imported"

Anyway, I would like to automatize from Access or Excel, if it is
possible.

Regards
Daniel


Nov 13 '05 #9
Simple fix for you Dani...

CAC40_Weighting s needs to have double quotes around it to work
properly...

It is not a variable so you have to put quotes around it to make VB
realise what you mean (i.e. EXACTLY this NOT a variable that is called
CAC40_Weighting s that could contain anything).

Does that help you now?

Can you get it to work?

Rob.

Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
6486
by: Fred | last post by:
Hi. How do I import while mapping an excel table to an access table please??? I've searched around and all I can find is a software product or code that does the same thing as the access wizard..... If I have to write vb code, where is a sample??? Or do I import the excel in to a new spreadsheet and then write some kind of querey to move the data from that table
2
15510
by: Fred | last post by:
Hi. How do I import while mapping an excel table to an access table please??? I've searched around and all I can find is a software product or code that does the same thing as the access wizard..... If I have to write vb code, where is a sample??? Or do I import the excel in to a new spreadsheet and then write some kind of querey to move the data from that table
3
1600
by: Tlaker | last post by:
MS Works 6.0 to Access 2000. Tried saving to *.csv and dbf as well as converting to Excel then loading but to no avail. My Works database has about 4000 records, each with about 30 fields. Is there a way to convert to Access without re-entering the information 1 record at a time? TIA
8
3371
by: mytfein | last post by:
Hi Everyone, Background: Another department intends to ftp a .txt file from the mainframe, for me to process. The objective is to write a vb script that would be scheduled to run daily to process this .txt file. Goal: I am working on a vba script to:
5
5433
by: Tammy | last post by:
I am doing some genealogy research and I have discovered that there is a lot of data available on the web in text format. The problem is that the columns in the files are lined up by spaces. I'd like to be able to import these files into Access or Excel so that they can be sorted. What I plan on doing is adding in commas at the end of each field to create a csv file. What I need to know is how can I easily remove the spaces between...
5
3176
by: hharriel | last post by:
Hi, I am hoping someone can help me with an issue I am having with excel and ms access. I have collected data (which are in individual excel files) from 49 different school districts. All districts have used the same excel template and populated the same 32 data fields (columns). I created one large excel file from all 49 files which gives me a master table of 60,000 or so records. I have tried to import this master table into access...
11
5384
by: MD | last post by:
Hello, I need to import a sheet of 884 different excel-file with same lay- out. The sheet name is 'Totaal' and is the same in all different files. Is there a script (module) in order to: 1. create a table with all the different names of the 884 files 2. create the import queryfor the 884 different files, but also mentioning the file name of the excel-file in a colomn
0
767
by: DrewYK | last post by:
I have the project that may never end in front of me. I am creating a routine that will take SpreadSheets from Excel and bring them into Access. I am not using any "DoCmd"s because the goal is for the import code to be moved to a stand alone VB app which will use the Access DB as a workspace to process the data from the spreadsheets. Quite honestly, done right this may not even require Access or Excel to be on the users machine. ...
1
5163
by: thadson | last post by:
Hi, I'm trying to import specific cells from MS Excel 2000 spreadsheets to MS Access 2000 tables then move the spreadsheets to a different directory. I'm very new to this and I'm having trouble to implement this. I have worked out so far the code to import certain cells into 1 table, but I do not know how to import some other cells into another tables so the data would be connected and remain together. So lets say that I have 2 tables...
8
9655
by: shenkel55 | last post by:
I'm using Access and Excel 2003. Using either the import wizard or code, I have the same problem. This problem only happens with Excel files automatically generated by Corp IT. If I try to do an import and the Excel file isn't open I get the following error: "The wizard is unable to access information in the file "...path info... "Please check that the file exists and is in the correct format." If the files are opened directly in Excel, it...
0
8784
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9462
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9248
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 most users, this new feature is actually very convenient. If you want to control the update process,...
0
8199
agi2029
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 launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6747
isladogs
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4558
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4821
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3270
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2187
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.