Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Import Data Via VBA Script


I've just been task to create a VBA script macro button in Excel so it automatilly pulls data from my SQL database. Can anyone show/point me to the right direction on how to accomplish this?


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Import Data From The Web (VBA)
there is i website that i want to imoport data from it to my excel sheet using vba, but the poblem is that the website will need my username and password...

i search around for possible threads and i found a very close one but couldnt have it do the submit option. also i donot want it to show me the intenet explorer window. i just wantit to do these steps:
after clicking a button:

1-logon to the site using my user and password
2-redirecting to the report link and improt what ever is in that page
3- all this with out opening IE.

Public Declare Function ShowWindow Lib "user32" (ByVal lHwnd As Long, ByVal lCmdShow As Long) As Boolean

Private Sub LoginTone()

'Dim ie As InternetExplorer
'Set ie = New InternetExplorer
Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")

With ie
.Navigate "http://s8.brono.com:80/mongol/fiona/index.php" ; this is the login address
Do While .busy And .readyState 4: DoEvents: Loop.......................


View Replies!   View Related
Macro/vba: Grouping Data From Csv Then Import Into Excel
I have a data file saved as csv like this:

Risk Postcode,Effective Year Month,Written Premium,Written Units,Earned Premium,Earned Units,Claim Cost,Claim Count,Glass Manufacture Decade,
2600,200507,0,0,5.7354,0.0765,0,0,1920,
2600,200508,0,0,5.7354,0.0765,0,0,1920,
2600,200509,0,0,7.1693,0.09563,0,0,1920,
2700,200510,0,0,5.7354,0.0765,0,0,1920,
2700,200507,0,0,5.7756,0.0885,0,0,1930

what i want to do is to group the record with the same postcode together and just return one record for each postcode in excel.

so the output will have the postcode, the sum of written premium, written units.

I understand i can use Access to do it, but I want just to use excel for this task.

View Replies!   View Related
Code For Data Directly To Peachtree Using VBA Rather Than The Export/ Import Process
write code to post Excel data directly to Peachtree using VBA rather than the export/ import process?

View Replies!   View Related
VBA Code To Import Text Files & Process Imported Data
I have two macros one of which imports the data and the other processes the data. These macros were created at different times and need to be joined into a single macro that will combine their operations with slight modification to the importing macro. Let me describe what each of the macros does:

PROCESS macro:
Cycles through the CONTROL CELL on the 1 tab using the VALUES TO TEST. For each value to test it copies the values form the output tabs and pastes them into the A+B tab. Then it removes the duplicate rows there.

Option Explicit
'======================================
Sub PROCESS() 'This subroutine is called when clicking on the first button
Dim i As Long, j As Long, k As Long 'Those are the variables that will browse through the whole sheets
Dim l As Integer 'this will be used to browse through the values to test
Dim TheSearch(1 To 2) As Object 'This is an array of objects that will be used for the search
Dim TheRange As Range 'This will be used to flag the range of duplicates in the output

'Searching for the Text string 'VALUES To TEST' in the sheet "1" in order to know where the array of values to be tested start
Set TheSearch(1) = Range(Cells(1, 1), Cells(ActiveSheet.UsedRange.Rows.Count + 10, ActiveSheet.UsedRange.Columns.Count)).Find(What:="VALUES TO TEST", LookIn:=xlValues, Lookat:=xlWhole)

View Replies!   View Related
VBA Import From Web
Is there any way to change parts of a URL within a macro using the import from web function?

The import from web function as a macro is the following:

With ActiveSheet.QueryTables.Add(Connection:= _

I would like say the "4457" part to be dynamic. It can either change by user input, from another function..etc doesn't really matter. The problem is can this change? by string concatenation perhaps? or something else?

View Replies!   View Related
Import From Access Using Excel VBA
I use Excel 2003 and am trying to import information from an access database. The recordset I am looking for is based on Cell B3 on a worksheet named Import.

Is there a way that Excel VBA Can do this?

The recordset contains text in the standard of Memos, Will I be able to import the entire Memo?

View Replies!   View Related
VBA To Import A Couple Of Userforms
I am trying to create a new workbook out of an existing one by exporting three sheets. I need to bring along a couple of userforms.

View Replies!   View Related
Import Web Page With VBA
I need to get VBA code to import a web page one at a time based on a condition. When one of the urls below has "1 mins to post", I want that page imported into a worksheet.
The pages require a login and here is the source code for login - "acctnum" and password - "zipcode". The name for "1 mins to post" is name ="MTP".

I would want to keep the page loaded until "MTP" = OFF. Then whenever the next URL goes to "1 mins to Post", load that web page and keep it loaded until "OFF" and so on.

This would need to continually scan through all of the following urls:

http://tote.phonebet.com/WPSARL-C.PHP
http://tote.phonebet.com/WPSBEL-C.PHP
http://tote.phonebet.com/WPSCAL-C.PHP
http://tote.phonebet.com/WPSCAN-C.PHP
http://tote.phonebet.com/WPSCTN-C.PHP
http://tote.phonebet.com/WPSCTN-C.PHP
http://tote.phonebet.com/WPSCOL-C.PHP
http://tote.phonebet.com/WPSDPK-C.PHP
http://tote.phonebet.com/WPSELL-C.PHP
http://tote.phonebet.com/WPSEMD-C.PHP
http://tote.phonebet.com/WPSFMD-C.PHP
http://tote.phonebet.com/WPSFLK-C.PHP
http://tote.phonebet.com/WPSFTE-C.PHP
http://tote.phonebet.com/WPSHAS-C.PHP


View Replies!   View Related
Import Specific Fields From Access (VBA)
I have working code to copy the matching records of an Access table into an Excel worksheet, but now I am trying to modify this code so that I can select a couple of specific fields from the table instead of just copying all of the fields (there are 15 fields in the table, but I only actually need 4 of them for this particular report).

Here is the code I am currently using to copy all of the fields over with the matching records:

Const myDB = "DSD Errors DB tester.mdb"
Private Sub CommandButton4_Click()
' Test Field Select button
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sSQL As String

sSQL = "SELECT * FROM DSD_Invoice_Requests WHERE `Paid?` IS NULL"

How can I modify this so that I put specific fields from the table into columns in Excel that will not line up with the layout of the table? For the sake of the example, let's say that I need fields 2, 4, 6, and 8 of the table to go into columns 1, 2, 3, and 4 in Excel.

View Replies!   View Related
Import VBA To Visual Studio2005 Professional
i want to import Excel data into Visual studio 2005 professional.

how to import excel?

View Replies!   View Related
Import Outlook Calender Automatically Using VBA
I am trying to import outlook calender automaticly using vb. I have found this site http://www.kittytours.org/calendarproject/trouble.html that has the macro ImportCalendarData. I try to run it but I get syntax error at the row

With ActiveSheet.QueryTables.Add(Connection:= Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User ID=Admin;Data

and a yellow arrow is pointing at the top line(sub impo...)

View Replies!   View Related
Paste With Text Import Wizard In VBA
I am pasting query results from WinSQL into Excel and use 'Paste' and then, from the little Paste Options icon, 'Use Text Import Wizard'. I choose tab delimited and then reset the decimal separater using the 'Advanced' button on step 3. Since I do this daily I would like to create a macro to do it all for me, namely paste what I have already copied to the clipboard using the appropriate settings. Recording doesn't reflect the steps I executed. How can I call the text import wizard from vba with the clipboard contents as input as opposed to a text file?

View Replies!   View Related
Date Format- Data Retrieved Via Import Data Tools
I have the following data retrieved via import data tools ...

View Replies!   View Related
Can VBA Be Used To Do A Fixed Widths Import Of A Txt File?
I have lots of files I need to read every day. I need to read them in as fixed width - EX: column A is 1-8, column B is 9-12, column C is 13-22, etc. Is there a way to create a VBA macro or something so that I don't have to point and click to draw lines every day when I read in these files?

View Replies!   View Related
Import, Format, Convert, Print VBA Module
I was wondering if its possible to build a module that would do the following: a) import a txt file to excel formatting it as text.

b) then in column D would remove the preceding space.

c) then convert data in D according to a separately kept Conversion table (or conversion table could be integrated into the code) and print conversion results into column J.

d) the last step is to print/copy columns A and J so that it looks like the final table in Sheet2.
Here are files attached.

sample data.txt
sample data.xls
conversion table.xls

To summarize I need to go from a txt file like the one attached and arrive at the table in Sheet2 of xls file attached.

View Replies!   View Related
VBA If Cell Is Blank Import Value From Adjacent Column
I'd like a macro that cycles down 50000 rows of column B. If it comes across a blank cell it will import the value from its adjacent cell in column A. then the macro will move onto the next cell.

View Replies!   View Related
External Data In Spreadsheet Without Having To Import All The Data
I'm wondering if I can use external data in an excel spreadsheet without having to import all the data. I basically need a persistent connection with the external data and I want the excel spreadsheet to get the item descriptions by using the item number as the user types in the item number. Writing out the descriptions is killing me and I have all my products in an excel spreadsheet already. I really need someway to link two separate excel files together. One with data and one with user input that searches that data as I type in the item number.

View Replies!   View Related
Import Multiple Text Files & Copy Each Import
I'm attempting to import around 200 (and growing!) separate text files into Excel. I am using the formula below to import the text file and then using a separate macro to select the information I need, copy it into another spreadsheet, and then run the import macro again.

However, I have a problem in that my import macro gives me 'Run-time error '1004:

Application defined or user defined error''. At first this wasn't a problem as the information is pasted into the spreadsheet despite the error anyway. However, now that I am looping the macro it is obviously causing more problems as it prevents the loop. I would really appreciate it if anyone knows of a work-around or can spot an error in the coding to resolve this!

The code below shows is for the import macro only:

Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

View Replies!   View Related
Import Data
I have this VBA macro which imports data from a text file. I do not know exactly how to make it good.

I have two sheets, named <INDATA> <Drawing_list>

On sheet <Drawing_List> I made a command button with this ....

View Replies!   View Related
Import Of Data ...
The task that I have to make is a little bit complicated, especially for reason that I am not v VBA programmer. But however here is the task (and is not a school assignment).

First I have to clear the worksheet Invoice_Plan (file Financialplan.xls), but the whole row(s) that have data in column F (comment) have to stay on worksheet.

Than I have a worksheet Data (file invoices.xls where are all invoices from year 2009) and in column A are the data, that I have to import into in worksheet Invoice_Plan to (second file FinancialPlan.xls) in column A data under following rules (other must be skipped):

View Replies!   View Related
Import CSV Every X Minutes Without Import Dialog Appearing
I have a simple macro importing a .csv data file to a worksheet every 30 minutes.

When the macro runs an Import Text File dialog box displays.

What do I need to do to have the macro run to completion without the dialog box interruption?

Sub RefreshHourlyData()

htime = Now + TimeValue("00:30:00")

Application .OnTime htime, "RefreshHourlyData"

Sheets("H1Updates").Select
Sheets("H1Updates").UsedRange.Select

Selection.QueryTable.Refresh BackgroundQuery:=False

Range("A1:A1").Select

End Sub

View Replies!   View Related
Import External Data
I'm trying to pull in data from another Excel workbook which gets updated on a regular basis. Using the Import External Data fucntion in Excel I created a query which pulls the data from the original workbook into a tab in the workbook I'm using. The issue I'm having is that not all of the column headers get pulled in when using the Import External Data function. I tried typing the column headers in myself but they disappear again after refreshing the data set. Any ideas what is causing the issue?


View Replies!   View Related
Normalizing Data For Import
I need to import a massive spreadsheet into QuickBase. It will have close to twentyG records when(if) I can do this thing.

The table i'm importing into (Groups) is a details table which carries the 'Many' attribute in a M:N relationship.

There is a column in the spreadsheet (Groups) that is distributed over several columns:

UserID---------------Groups
=====================================
12345---------------SPE-UID, SPE-PRE, SPR-DDR
34567---------------ADR-NNB
56789---------------NNI-ZIY, HII-PRE, SPR-DDR

In order to import into QuickBase, I need these data to be normalized:

UserID---------------Groups
=====================================
12345---------------SPE-UID
12345---------------SPE-PRE
12345---------------SPR-DDR
34567---------------ADR-NNB
56789---------------NNI-ZIY
56789---------------HII-PRE
56789---------------SPR-DDR

Can Excel create a normalized arrangement like what I have above without my having to manually work this? It would take days if I had to.


View Replies!   View Related
Import Appropriate Data To Worksheet
I have a workbook that contains an 'AllData' sheet. That sheet has 6 columns titled "category, keyword, bid, advertisers, search vol, online". Each category has its own worksheet and i want to put the data for each category onto the corresponding worksheet. I am having a difficult time describing this so I have attached a brief example. I have to do this for 145 categories so I would much prefer to do it automatically rather than manually.

View Replies!   View Related
Import Data From Several Worksheets
I have several of the attached files that come in every week for me to process. I would like to be able to put all of them in a folder and have the highlighted columns copy to one master file and eliminate all the extra blank rows. I also need it to sort by salesman with all of their sales in the different catagories grouped together. I am not sure if this can be done given the way the salesman's names are not on each line with their sales.

View Replies!   View Related
Import Of External Data
Ive got a question regarding import of external data in excel. The workbook I am conceiving at the moment does have a database query from another excelfile(a report from my business system) via the standard Excel functionality and it works perfectly fine.
What I cant figure out is how to re-direct my query to any file/path I choose.

At the moment I just replace my original file with a new freshly updated one but I am stuck with the original path and filename or else it wont work.


View Replies!   View Related
External Data Import
I am trying to import the data on the url below. When I go to the external data inport the page is loaded and I click the indicator to load the central table but when the data loads the page is severally corrupted. I have found this on a few pages but some pages load correctly.

View Replies!   View Related
Import Data From Different Spreadsheets
I am trying to import data from different spreadsheets "about 100" to a principal spreadsheet which will have all the centralized data. I included the principal spreadsheet layout and a sample of two detail spreadsheets. The information that I want to import is from column B 1 to 5 from the detail spreadsheets and put into the principal spreadsheet from row D to G.

Is there any way to import this information automatically, like creating a macro or something similar.


View Replies!   View Related
Access Data Import
I am having some issues importing data from Excel to Access. I am attempting to import about 45000 rows of data from Excel to Access. It appears Access is only importing about 16000 of the 45000 rows. I am using 'import' in 'get external data'.

View Replies!   View Related
Not Enough Memory On Data Import
I'm trying to import data into my sheet with formulae like this in row1(Columns A:I): ='F:Dir[File.xls]Sheet'!A2.

The files I'm importing the data from all have 50.000 rows and 9 columns, so I copy the formulae downward 50.000 times. This generates an error ('Not enough memory'), not surprisingly. I've also tried to do it in steps by way of VBA; first two thousand, copy and paste values, next two thousand etc. But that doesn't work either.

Does anyone know of a solution to this memory problem or a way to work around it (without opening the files with the data in them).

View Replies!   View Related
Import Data Or Lookup
I am trying to import data from an external source file (Excel) into an Excel sheet that will get uploaded into a db accounting system.

The source data varies month to month in the number of rows and need I to import 7 columns.

The upload file will be saved as a text file for the upload.
I tried to use Import External Data, but since the rows vary in size (anywhere from 1 - 2000 rows) I had to create a named range for the entire column. Upon saving this caused all rows beyond the last row of data to have quotes in them which will kill the upload.
Is there a way around this with the Import External Data, or is there a better way through some combination of INDEX, MATCH, INDIRECT or the like?

View Replies!   View Related
Import Data Grayed Out
This is on the Data- Import External Data menu.

Please how can I enable it to continue my work?

View Replies!   View Related
Import Data To New Sheet
how can I import data to another sheet in VB in excel as in my coursework i have 5 drivers in seperate workbooks and I also have a main workbook. from my main workbook I got a drop down box so if i choose a particular driver it will import it into a new sheet.

View Replies!   View Related
Transposing Data For Import
I need to transpose some data for import into our accounts package, the budgets are produced in a fairly standard horizontal format with periods across the top & account codes vertically.

The import requires period data to be be transposed vertically

I have attached a worksheet with an example, is this possible in excel. the only way I have managed to accomplish this is via MS Access

View Replies!   View Related
Import Data Via Combobox
I am trying to trigger an event when a value from a combobox in Excel is chosen. E.g If i choose "Option 1" from the combobox then push Command_Button 1 (for example) i would like excel to connect to my database and pull all information on "Option 1", if i change my mind and want to see all information on ("Option 10"), i would like it to go and get "Option 10" info anfter the button is pushed. I have had a look around and cant see anything that comes close to completing this. I though about using the worksheet change event to trigger this but the application comes back stating "Application Error or object defined error".

View Replies!   View Related
Import Bloomberg Data
If it is actually not possible to change the way that bloomberg exports the information to excel then i think there might be two potential ways around it...neither of which i am sure how to do...

1) write some sort of macro which takes data from the new workbook, pastes it into the active workbook then closes the newly created workbook. Potential problem here is that whenever it creates a new workbook it has a slightly different name. eg. "abc1" then "abc2" etc.

2) Write some sort of macro which gets a list of companies from bloomberg with upcoming events over the next week, put this into the active workbook and then simply use the bloomberg get functions to fill out the info.

View Replies!   View Related
Import Data From Another File
My task is to make an excelfile that import data from other excelfiles. These data should then be summarized. There's not always same files. I should be able to add more files.

View Replies!   View Related
Macro Code To Import Data
I am trying to use a dynamic VBA code to import data into a xcel sheet from another sheet. I currently have it working but in the code I have the directory and the file name listed for the file to be imported. I would like to change this to a cell referance inside my sheet so that I can make changes to the name of the file or the directory location and the VBA code would use the cell reference as the source data.


View Replies!   View Related
Batch Import External Data
I have several thousand text files, each with three columns of numbers. My goal is to convert each of these to a dbf file with three distinct columns (which is why simply renaming each with .dbf at the end doesn't work.) I can do this for an individual file by using the "Import External Data" Tool in Excel, selecting the "Fixed Width" option (which divides the figures into three distinct columns), and then saving the file as a dbf. I need to do this for several thousand files, however, so I can't do each by hand.

View Replies!   View Related
Macro To Import TXT Data
I have a TXT file that is properly format for a nice print out but not in any fashion to work in an excel or access file.

My txt file looks something like this
Header info Row 1
Header info Row 2
Header info Row 3
ACCT# Value Descripton ~ Part number
Customer Name ~ Vendor Name
Customer add1 ~ Vend add1
Customer add2 ~ Vend add2
Custom city, st, zip ~ vend city, st, zip

Repeat about 5 or 6 more entries
do a page bread and repeat the headers and then 5 or 6 more entries

I want to automatically copy the entries and place the data into Row 1 of Excel starting in Column 1 through Column 12

Then the 2nd entry would be in row 2 etc

View Replies!   View Related
Specify Format Of Access Data Import
I tried to upload my spreadsheet but the zipped excel spreadsheet was 78KB over the forum max. Its hard for me to explan this without you looking at the spreadsheet. Basicly I'm exporting data from Access to Excel into a spreadsheet. When the data exports into excel I want the data to be formated in such a way and this is where some code will need to be written. I've made up 2 spreadsheets within excel. One sheet is how the data comes into excel from access and the other sheet is how I'd like the data to look without me having to doctor the sheet every time I export.

View Replies!   View Related
Automating Data Import And Calculations
I have a huge database of textual files all of which have the same format - space separated text files. I need to find the average of the last column of all these files.

Each file is named uniquely. Is there an stress-free way to set up an automated procedure to go through each of these files automatically and to record the average of the last column along with the name of the file. I attached a few text files - these are daily prices of a few securities. The last column is Volume. I need to find and record the average of this column for each of these files.

The text files are here:
[url]

View Replies!   View Related
Data Layout Import To A Database
I have a spreadsheet that tracks hours for employes and I need to get the data in a different layout to import to a database i'm building. The example spreadsheet has 2 tabs, the first is the format it's currently in and the second in the one I need it in.

View Replies!   View Related
Import Data Into Current Workbook
I'm trying to import data in to an existing workbook. When i run the macro in the existing workbook, a new workbook opens and the data is dropped there. What do i need to change to get the data to pull back in to the existing workbook?

View Replies!   View Related
Macro Button: Import Data
how to make a button(s) which will import data. I have to make a report and import some cells from 350 other excel files. Report is attached to this post. In the yellow cells should be buttons. The code for first one is :

Sub CopyRanges() ....

View Replies!   View Related
Garbled Data After CSV Import
I have excel 2003 & when I import a csv file, the left column is all garbled & not readable.

The program I used to create the file is called auction yen, which analyses eBay's Want It now forum & allows you to save it as a .csv file

Here are some pics of the file & also a link to download it.
[url]

View Replies!   View Related
Import Data From From Sheets/Cells
I have to make something what will import data from other documents.

I made a template report. Now I need a button or something what will open a file (a want to choose it) and import some cells from 5 sheets (not whole sheets) to the contfirmed cells in the template. All of the files are the same (i make inspections of some stuff) only data is different.

It will save a lot of time, cuz i have about 300 something documents from which i have to make the reports.

View Replies!   View Related
Import Data And Delete Macro
after importing data (data >> get external data >> import text file) and running a macro that contains

Cells(i, 5).EntireRow.Delete Shift:=xlShiftUp


i cannot insert any data into the next column below. why is this so? is it because of the excel memory that remembers that a macro has been previously ran in that cell before? example i import text file into B1 then the data will be imported into different cells using delimiters. the data will occupy from row 1 to 22. After i run the macro which combines fields with similar data the data will occupy row 1 to 15. then when i try to import data into B16. the "import text file" option will be grayed out. why is this so? how do i solve the problem?

View Replies!   View Related
Import Data From Closed Workbook
What is the best way to import data from another closed workbook? I used the macro recorder and it gives me something like this

ActiveCell.FormulaR1C1 = "='[CASHFLOW.xls]Sheet1'!R66C5"

I don't think this is the best way, since it asks me to update links when I reopen the file.

View Replies!   View Related
Linking To Another Workbook (Import Data)
I am importing data from an outside excel file to several other files. I requested that the data be refreshed every time I open the file that references the outside excel file. Whenever I have a file open that references the outside excel file, I can only open the outside excel file in a read-only state. Why is this, and is there any way around this issue?

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved