Over 1 million tech questions and answers.

Excel Macro - VBA code to import access data to excel

Q: Excel Macro - VBA code to import access data to excel

I'm working on a Bill of Material creation automation project that requires some expertise in VBA, and I have none.
The objective is to run a macro from an excel spreadsheet called "PGE BOM", to do the following:
1) Go to the folder "C:\Documents and Settings\Desktop\Auto Project"
2) Find all the .mdb databases in this folder
3) Find "HistoricalMaterialItemsAll" table in EACH of those databases in step 2, and import the data from the columns listed below into PGE BOM.xls's columns C through G:
DrawingNumber
ItemNumber
Quantity
PgeCode
Description
The following is a VBA code that my friend had written in Excel 2007. Unfortunately I have an older version (2000) and the code does not seem to be compatible with Excel 2000.
Sub ImportAccessData()
Stop
dPath = "C:\Documents and Settings\Desktop\Auto Project\"
sFile = "*.MDB"
strSrch = dPath & sFile
Set TargetWB = Application.ActiveWorkbook
Set TargetWS = TargetWB.ActiveSheet
sRow = 2
bFile = False
If Dir(strSrch) <> "" Then
strFlNm = Dir(strSrch)
bFile = True
End If
Do Until bFile = False
strPath = dPath & strFlNm
Call GetData(strPath)
strFlNm = Dir
If strFlNm = "" Then bFile = False
Loop
End Sub
Sub GetData(fl)
Stop
strSQL = "Select HistoricalMaterialItemsAll.* From HistoricalMaterialItemsAll"
Workbooks.OpenDatabase fl, strSQL, xlCmdTable
Set WB = Application.ActiveWorkbook
Set WS = Application.ActiveSheet
iRow = 2
Do Until WS.Cells(iRow, 1) = ""
TargetWS.Cells(sRow, 7) = WS.Cells(iRow, 5) 'Get the Description
iRow = iRow + 1
sRow = sRow + 1
Loop
Application.DisplayAlerts = False
WB.Close
Application.DisplayAlerts = True
End Sub
The "ImportAcessData" sub procedure above works just fine. However, the red statement "Workbooks.OpenDatabase fl, strSQL, xlCmdTable" line in the "GetData" sub procedure seems to be incompatible with excel 2000. Could someone please help debug this?? Thanks!

RELEVANCY SCORE 200
Preferred Solution: Excel Macro - VBA code to import access data to excel

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Excel Macro - VBA code to import access data to excel

Hello, and welcome to the board!

When posting code, please use CODE tags, which extremely helps with readability.

Perhaps you could try using the code constant???...
Code:

Option Explicit

Sub ImportAccessData()
Dim dPath As String, sFile As String, strSrch As String
Dim TargetWB As Workbook, TargetWS As Worksheet
Dim sRow As Long, bFile As Boolean, strFlNm As String, strPath As String
dPath = "C:\Documents and Settings\Desktop\Auto Project\"
sFile = "*.MDB"
strSrch = dPath & sFile
sRow = 2
bFile = False
Set TargetWB = Application.ActiveWorkbook
Set TargetWS = TargetWB.ActiveSheet
If Dir(strSrch) <> "" Then
strFlNm = Dir(strSrch)
bFile = True
End If
Do Until bFile = False
strPath = dPath & strFlNm
Call GetData(strPath, TargetWB, TargetWS)
strFlNm = Dir()
If strFlNm = "" Then bFile = False
Loop
End Sub

Sub GetData(fl As String, WB As Workbook, ws As Worksheet)
Dim strSQL As String, iRow As Long, sRow As Long
strSQL = "SELECT HistoricalMaterialItemsAll.* FROM HistoricalMaterialItemsAll"
Workbooks.OpenDatabase fl, strSQL, 3 'xlCmdTable
iRow = 2
Do Until ws.Cells(iRow, 1) = ""
ws.Cells(sRow, 7) = ws.Cells(iRow, 5) 'Get the Description
iRow = iRow + 1
sRow = sRow + 1
Loop
Application.DisplayAlerts = False
WB.Close
Application.DisplayAlerts = True
End Sub

Read other 1 answers
RELEVANCY SCORE 108

Hi,

I've got a DB that works like this: I export about 10 tables from Access to Excel. Each table will be exported to one worksheet in the same file. Then I update the data in the worksheets without modifying the field names. Finally, I want to import the data in the Excel to Access.

I could use the Import function in Access but it would mean I need to perform the import action 10 times. I found a line of code that looks that it's what I need but how do I modify it to tell it to import the correct worksheet into the correct table?

I'm really a novice at this so I would appreciate if you could use the following example. For example, tbl1 and tbl2 is exported into the same file A and worksheet is names tbl1 and tbl2. How do I write the code?

Line of code I found:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Import", Me.txtFileName, True

Thanks a million
 

A:Import from Excel to Access using code

Read other 8 answers
RELEVANCY SCORE 105.2

Hello,

I have two problems:

1.
I have a lot of scanning data, all date and time stamped. All the scans show different status of shipped packages (all shipments have unique ID#, but there are multiple scans on route for all of them to track the packages). I want to find all the packages, for which there was "misdirected" scan, then for all of them, show the scan before. So, the end result would be all the scans that show for each package where it was misdirected (the terminal# of the scan before the misdirect scan).

2.
I want to automate this: run 5 queries every two hours between 8am and 10pm, copy and paste all results into Excel to specified areas (e.g. first query results to A1, second to A5000, third to A10000, etc. or onto different tabs), save as .csv to a specific folder, then send it as attachment to a given e-mail address. Even partial solution can be good.
I only know macros (somewhat), not as much VBA, so I tried that with the TransferSpreadsheet command, but can't get it work.

Any help is greatly appreciated.
 

A:Solved: ACCESS: Show second latest data and export data to excel from macro

Read other 14 answers
RELEVANCY SCORE 102

Hi,

I was wondering if anyone could help me. I need to extract data from multiple excel files into a new excel file and sum the values. The data comes from excel files that are made from a template so the data is always in the same place or same cells. For example, i have data saved daily in excel files with the following naming convention 080204, 080304, 080404,..... and so on. What i would like to do is have a macro that will create a week ending sheet by importing and summing the data from that week.

Thanks for reading!
 

A:excel macro for copying and pasting data from multiple excel files

thanks for all those who read!!!! let me know if anyone needs the code!
 

Read other 1 answers
RELEVANCY SCORE 102

Hello again,

I have a word doc that has a form in it. The form may have any number of fields (boxes) to type text answers into, or be a drop down list to select an answer from. I need an excel macro that extracts all data from the form, but with every five fields starting a new row in excel. So all data from the form will appear within 5 columns in an excel spreadsheet.

field 1 field 2 field 3 field 4 field 5
field 6 field 7 field 8 field 9 field 10
etc

Is that possible? I have had no luck to do it myself.

Sample form attached, thank you
____________________________
 

A:Solved: Excel macro to extract Word form data into Excel

Read other 7 answers
RELEVANCY SCORE 100.8

Hello-

While importing the essbase retrieved data from excel spreadsheet to access database using the folling Access VBA command, I get 65 K of blank rows into the table. This only happens for essbase retrieved data, other excel files works just fine. Is there a reason, and how can I prevent this 65 K blank rows? Please help. Thank you much.

VBA command to import excel data

DoCmd.TransferSpreadsheet acImport, nSpreadSheetType, "SPECTRUM_CALCULATION", _
cFileName, Me!ChkBox_FieldNames

Santosh
 

A:import essbase retrieved excel data to access table adds 65 K blank rows

essbase is probably using empty strings, which Access picks up as data. Instead of importing to a new table, set up your table with a primary key, one that corresponds to the Excel column most likely to have data in it every time. Set allow empty strings to no on that field as well. That should import only the "filled" records.
 

Read other 1 answers
RELEVANCY SCORE 97.6

I am new to VBA Macro. I am trying to create a VBA macro to import CSV file to excel. The following is the code that I recorded it, however, I manually finished writing the last part of the code because there was an error saying "Too Many Line Continuations" however, it is not liking it. I am not sure where I did wrong. Some times I get the error message saying statement too complex. It is actually pretty straightforward. The most part is the datatype. The file I am trying to import has about 3700 columns. It would be great if someone can help me out here. Thank you.
Code:

Sub VBAMacroInpatient()
'
' VBAMacroInpatient Macro
'
' Keyboard Shortcut: Ctrl+Shift+I
'
With ActiveSheet.QueryTables.Add(Connection:="TEXT;Z:\Alex\Inpatient.csv", _
Destination:=Range("$A$1"))
.Name = "Inpatient"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
... Read more

A:HELP: VBA Macro to Import CSV to Excel

Read other 16 answers
RELEVANCY SCORE 96

Hi,

I want to know how i can export the data from Access to excel using Access VBA for the specified sheet using data linkage with access database. Like we used to do it manually in excel as external data from access.

So that the excel size won't be that big and also it saves processing time.
 

A:Solved: Access data export into Excel as the data linked to excel.

Read other 16 answers
RELEVANCY SCORE 95.2

Moved from Software Development
Hi, We are a team of 25 who all use the same contact list (which contains addresses, organisation name, tel numbers, emails, contact names etc for approx 600 organisations). We regularly have to bcc all these contacts in to emails. We previously all held the distribution lists (sorted alphabetically by email) and had to let the rest of the team know when details changed so they could be updated by all. The problem with this was that the distribution lists do not hold all the information needed (e.g. addresses, organisation names) and contact information was not always updated as it should have been. I have updated an excel spreadsheet containing all the information and I would like to create a macro for all the team to periodicaly import this data in to Outlook Contacts and possibly create distribution lists from these. I trust this makes sense I am using Outlook 2003.
Thanks
 

Read other answers
RELEVANCY SCORE 94.4

Hi, I am looking for a macro to import contacts from excel. We are a team of 25 who all use the same contact list (which contains addresses, organisation name, tel numbers, emails, contact names etc for approx 600 organisations). We regularly have to bcc all these contacts in emails. We previously all held the distribution lists (sorted alphabetically by email) and had to let the rest of the team know when details changed. The problem with this was that the distribution lists do not hold all the information needed (e.g. addresses, organisation names) and contact information was not always updated as it should have been. I would like to create the macro for all the team so that they can periodicaly update their contacts. I trust this makes sense
Thanks
Moved to Business Applications.
 

Read other answers
RELEVANCY SCORE 94.4

Hello guys

I need some help please in Excel macro and VBA.

I am a coop student, and my my boss wants me to do the following :

A software on my pc collects various csv files from several other pc's on the plant. There are 3 different files.

What I would like to do is to run a macro every day which opens that software, import all the csv files to an excel spreadsheet, add the specific header to each file ( I have 3 different headers in one excel file, one for each type of file) and then save the files in xls format.
Could someone provide me with the VBA code for this please?

Thanks
 

A:Running excel macro to import csv files from a software on my pc

Read other 8 answers
RELEVANCY SCORE 92.8

Hi there,

I don't have enough experience in VBA excel or word, and would you mind helping for the following:
Q1
The below macro code add row below the last row. And Would you mind telling me what is the code that can add row for word document table between the last row and the row before the last?

ActiveDocument.Tables(1).Rows.Add

Q2:
My second question is related to export/import between two different programs or files ( Excel and word document)
Would you mind providing me the proper code for
Wordtable.copy
Workbook.xls - sheet1.past speical value
Suppose the word tables excel workbookD sheet 1 are as follow

Table 1 in word document
--------------------
Cell 11 - Cell 12
Cell 12 - Cell 22
--------------------
Table 2 in Word document
------------------------------------
Cell 11 - Cell 12 - Cell 13
Cell 21 - Cell 22 - Cell 23
------------------------------------

WorkbookD.xls Sheet1
Rows Column
A B C D
1
2 wt1.Cell 11 WT2.Cell 11 WT2.Cell 12 WT2.Cell 13
3 WT2.Cell 21 WT2.Cell 22 WT2.Cell 23

How I write a macro to make the value or text in Cell 11. in word table 1 to be copied and past in Cell A2 in sheet1 of WorkbookD.xls
and the value or text of the range from Cell 11 to Cell23 of table 2 or word.doc to be copied and past in sheet1 of WorkbookD.xls from range B23 for n rows in table1 and table 2 of word.doc.

Thanks

Hamdy"
 

Read other answers
RELEVANCY SCORE 92.4

I'm trying to run an Excel 2007 macro from Access 2007, when I get to the point where I want the macro stored in the active workbook I'm getting the following run-time error: run-time error '91' object variable or with block variable not set . It won't run the macro at all. I would greatly appreciate it if someone could help me fix my code so the macro will run. The code is listed below; step 10 is where I get the error.
Code:
Option Compare Database
Sub GetJournal_Entry_Data_transfer_to_Excel()
'Step 1: Declare your Variables
Dim MyConnect As String
Dim MyRecordset As ADODB.Recordset
Dim MyQueryDef As DAO.QueryDef
Dim MyDatabase As DAO.Database
Dim MySQL As String
Dim MyRange As String
Dim s As String


Dim Db As Database
Dim xl As Excel.Application
Set xl = CreateObject("Excel.Application")
Dim xlwkbk As Excel.Workbook
'Dim xlworkbk As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim i As Integer


'Step 2:Declare your connection string
MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0; Persist Security Info = False;Data Source= P:\FINANCE\Balance Sheet\Inventory\Project TAN\Project TAN.accdb; User ID = Admin;"

'Step 3: Build Your SQL Statement
MySQL = "Select* From [mtb-TantasticJE's]Where [mtb-TantasticJE's].[Dscrptn_Text]='Culls_Stat34'and [mtb-TantasticJE's].[Co_Code]='1381'"

'Step 4: Instantiate and specify your recordset
... Read more

A:Access 2007 VB code to run Excel 2007 macro in active sheet

Read other 7 answers
RELEVANCY SCORE 91.6

Hi,

how to download or import data from website to Excel automatically? is there any software's for that please provide me information and assist me

regards
satya
 

A:Import data from web to Excel?

Read other 6 answers
RELEVANCY SCORE 90.8

Hi All,

I have a situation where I need to take data from this format
NAME
Clown, Bozo (8430)
Bunny, Bugs (2940)
Runner, Road (5023)

(basically in column 1 we have a list of last name (comma) first name and in parenthesis the last four of their SSN.)

I want to move the data from that sheet into another where the format is
First Name Last Name
Bozo Clown
Bugs Bunny
Road Runner

I would like the data imported to this new sheet sheet with the first name and last name in two columns instead of one (delimited by comma) and no last four, I want it in this format so I can mail merge that data into a word doc (I already know how to merge) but I don't know how to import the data to have the first and last names separated by columns with no last four of their SSN.
Can anyone help?
- Thanks
 

A:Excel data import problem

Hi there,
Try this macro
Code:

Option Explicit

Sub SplitNames()
Dim s1 As Worksheet, s2 As Worksheet
Dim p1 As Integer, lstRow As Long, xrow As Long, iRow As Long
[COLOR="Red"]Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")[/COLOR]
lstRow = s1.Range("A" & Rows.Count).End(xlUp).Row
iRow = s2.Range("A" & Rows.Count).End(xlUp).Row
If s2.Range("A1") = "" Then s2.Range("A1") = "First Name": s2.Range("B1") = "Last Name"
For xrow = 2 To lstRow
p1 = InStr(1, Cells(xrow, "A"), ",")
p1 = p1 - 1
If p1 > 0 Then
iRow = iRow + 1
s2.Cells(iRow, "B") = Left(Cells(xrow, "A"), p1)
s2.Cells(iRow, "A") = Mid(Cells(xrow, "A"), (p1 + 2), InStr(p1, Cells(xrow, "A"), "(") - (p1 + 2))
End If
Next xrow
End Sub

Just check the two lines in Red to see if your sheet's names exist, else change them accordingly

The Instr() function determines in the first case where the , is and in the second place to determine the (

The , and ( must be presnt for this to work.
 

Read other 3 answers
RELEVANCY SCORE 90.8

I am trying to do a web query on http://www.tntexpress.com.au/interac...imes_tntau.asp with information entered into the text boxes and the result into excel.

I have tried using a macro (in the workbook vba of excel) but it failed and I'm not sure if I am supposed to use post or some other way for text boxes :

Sub Login_WebQuery()
Dim MyPost As String
Const MyUrl As String = "http://www.tntexpress.com.au/interac...esX_tntau.asp?" 'DONT FORGET TO COPY LINK LOCATION BECAUSE TECH SUPPORT GUY SHORTENS THE ADDRESS
Const PostOSuburb As String = "txtOSuburb=CRANBOURNE"
Const PostOState As String = "&txtOState=VIC"
Const PostOCode As String = "&txtOPcode=3977"
Const PostDSuburb As String = "&txtDSuburb=ASPENDALE"
Const PostDState As String = "&txtDState=VIC"
Const PostDCode As String = "&txtDPcode=3195"
Const Postcolmonth As String = "&colmonth=December"
Const Postcolyear As String = "&colyear=2008"
Const Postcolhour As String = "&colhour=15"
Const Postcolmin As String = "&colmin=00"

MyPost = PostOSuburb & PostOState & PostOPcode & PostDSuburb & PostDState & PostDPcode & Postcolmonth & Postcolyear & Postcolhour & Postcolmin

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & MyUrl, Destination:=Cells(1, 1))
.PostText = MyPost
.BackgroundQuery = True
.TablesOnlyFro... Read more

A:Import Data from website to excel using vba

Please don't post duplicate threads. Continue the problem resolution here: http://forums.techguy.org/business-applications/782450-excel-web-query.html
 

Read other 1 answers
RELEVANCY SCORE 90.4

When i export my inventory from my DB into an excel file, my trailer brands are listed as they are in my DB (of course). But when i bulk upload them with XML feed to trailer inventory sites (HTW.com & HTT.com for our example here). My inventory is listed on their site, but it's not searchable because the brand search is a drop down menu on these sites.

Say i goto HTW.com and do a search for 'Blue Ribbon' trailers none of my blue ribbon trailers show up because all of mine are listed as 'BLUE RIBBON TRAILERS, LTD' and the site is searching for 'Blue Ribbon' and although they are the same trailer brand HTW.com does not see them.

What i'm looking for is maybe a Macro or even formula that once i tell it what site i'm working on, will go through my list and replace all my spellings with the proper ones for that site...

Attached is an XLS file with 3 columns, the first is my list as is from my DB, the other 2 columns are the proper spelling for each corresponding site...

Excel's find and replace feature is a VERY crude fix to this, but since it's so time consuming it's NOT a viable solution.

Any takers??

Slurpee? Zack? Anyone??
 

A:Solved: Excel -Macro or Formula? Replace my data with similar required data - sample

for the record, a complete solution would be GREAT, but not necessary...
I just need someone to point me in the right direction and maybe a small working sample of like 5 brands and i'll expand the list from there...

cheers
 

Read other 3 answers
RELEVANCY SCORE 89.6

I need some code or formula to automatically add data from one sheet to my main sheet where I would like to put all of my data together. I am able to download sheets off of the net and they are in numerical sequence by date. "19991118damlbmp_zone", "19991119damlbmp_zone" and so on. I need to create a button or something to gather the info from the first sheet and paste it in my main sheet and then gather the info from the second sheet and paste that into the main sheet in the next available row.

Please help if you can
 

A:Excel: Automatically import data from another workbook

bparker said:

I need some code or formula to automatically add data from one sheet to my main sheet where I would like to put all of my data together. I am able to download sheets off of the net and they are in numerical sequence by date. "19991118damlbmp_zone", "19991119damlbmp_zone" and so on. I need to create a button or something to gather the info from the first sheet and paste it in my main sheet and then gather the info from the second sheet and paste that into the main sheet in the next available row.

Please help if you canClick to expand...

Hi bparker - Not sure I'm reading your intent right, but if you can save the "19991118damlbmp_zone" etc sheets into a book on the desktop you name "Book11" and then open a new book called "Book10" and run the code from Book10 - you can use something like this:

Code:
[SIZE=2]Sub COPYIT()
'this is run from the book named Book10
Dim N%
Application.ScreenUpdating = False
On Error Resume Next '<< error is - "books already open"
Workbooks.Open Filename:="C:\WINDOWS\Desktop\Book11.xls"
For N = 1 To Sheets.Count
Workbooks("Book11.xls").Activate
Sheets("Sheet" & N).Select
Range("A1", Range("A65536").End(xlUp).Rows.EntireRow).Select
Selection.Copy
Windows("Book10.xls").Activate
Sheet... Read more

Read other 3 answers
RELEVANCY SCORE 89.6

I need some code or formula to automatically add data from one sheet to my main sheet where I would like to put all of my data together. I am able to download sheets off of the net and they are in numerical sequence by date. "19991118damlbmp_zone", "19991119damlbmp_zone" and so on. I need to create a button or something to gather the info from the first sheet and paste it in my main sheet and then gather the info from the second sheet and paste that into the main sheet in the next available row.

Please help if you can
 

Read other answers
RELEVANCY SCORE 89.6

I've got a macro set up on a worksheet in workbook A, which once an hour is exporting data to a worksheet in workbook B. This is housed on a shared drive. I'm trying to set up Workbook C to have a worksheet setup that imports the data from workbook B, so that management can view hourly updates without having to check their email. I've set a query on workbook C to refresh at regular intervals to pull the updates each hour. The problem is that once workbook C refreshes, it locks workbook b preventing any further writes from workbook A. I've tried setting longer intervals between the refreshes, setting the workbook B as shared, and attempted to change the Mode in the OLE QUERY from Share Deny Right to Read.

TL;DR -Basically it boils down to the auto-refresh on workbook c maintaining a connection to workbook b, preventing workbook b from being updated. Is there a way to only have workbook C connect to workbook b during the refresh, instead of maintaining a constant connection as long as auto-refresh is enabled?

Any help is greatly appreciated.
 

A:Excel Import External Data help needed!!

Read other 7 answers
RELEVANCY SCORE 89.2

Sub GetData()
Application.ScreenUpdating = False
If ThisWorkbook.Sheets("Raw Data").Range("DD").Value = "YesData" Then
Worksheets("DD").Range("A1:Y100").Value = Worksheets("Raw Data").Range("A1:Y34").Value
End If

In the above code I am trying to populate another sheet "DD" with specific information from "Raw Data". In the If statement I want the data populated to only retrieve data rows from "RawData" that contains the word "YesData" in the D column.

Please help.
 

Read other answers
RELEVANCY SCORE 88.8

Hi there ... I want to write a macro that would take the user to the next new unique data in a column. I have a list of account numbers that may be listed 3 times back to back in the column or up to 50 times back to back. I have a particular cell pointed at the top of the column, (ie, "=A1") and I want a macro to take the user to the next new account number in that column, which could be any number of rows down.

Suggestions? The cell I want the reference to change is in sheet1 and the account numbers are in a different sheet, same workbook.

Thanks!
 

A:Macro for Excel - next new data

Read other 14 answers
RELEVANCY SCORE 88.8

I have tried using a web query for what I am trying to do, and it just wont work. So I am wondering how I can copy an open webpage to excel automatically.
 

A:Solved: How do I import data from a webpage that is open to excel?

Read other 7 answers
RELEVANCY SCORE 88.8

I have run into an interesting problem here... We run an AS400 server from which data is imported into excel 2003 format for billing and other items... We have a user that has run in to a problem. Any X-fers of data in excess of 16000 lines will cause a statement from excel stating that the data will not fit onto the worksheet. As some may or may not know, the current limitation of 2003 is ~ 65K lines. The CPU itself is not an issue, or atleast should not be an issue P4 2.26 256 RAM... ideas?
 

A:Excel 2003 Data Import Limit 16K lines?

Can I ask?
How is the data imported?
From File-Open with Excel?
Or opening a CSV?
Or what?

Are you absolutely certain that an older version of Excel is not being launched when he opens the file?

If the type to be saved under Tools-Options-Transition just "Excel workbook" or some lesser version (tho I don't know if it would cause this issue).
 

Read other 3 answers
RELEVANCY SCORE 88.8

Hi there,

I'm trying to import data from an Excel spreadsheet into a Word document that already has the mail merge set up in it. Whenever I try to do this, I select the source after which it just sits there with a message in Word indicating 'Initiating DDE Link with Microsoft Excel.' I tried it with the same document on another machine and it worked... Is there another way other than reinstalling Word and Excel to fix an issue like this. I already tried /unregserver and /regserver and also tried creating another document with a mail merge.

Thanks a lot in advance!!
 

Read other answers
RELEVANCY SCORE 88.8

Hi,
I'm completely new to vba and trying to modify a code I found here but failed.
While the original code exports cell data (1 row only) from excel into the form fields of a Word document, I tried to modify the code to work for multiple rows, one after the other.
My code works for the first row and generate a word file as supposed, but immediately stoped with an error message "Runtime Error -2147417848 (80010108), "The Object Invoked Has Disconnected from Its Clients"
Could anyone help me modify the code so that it works please? Thanks a lot!
I'm using 2003 excel and word.
P.S. I know this can be easily done by mail merge but I would like to use Word form for further process.

Code:
Option Explicit
'Requires reference to Microsoft Word (11.0) Object Library
Sub Copy_Cells_To_Word_Document()
Dim rng As Range
Dim row As Range
Dim wdApp As Word.Application
Dim wdDoc As Word.Document

Set rng = Range("A2:E4")
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Open(Filename:="C:\Desktop\file.doc", ReadOnly:=False)
wdApp.Visible = True

For Each row In rng.Rows
Copy_Cell_To_Form_Field wdDoc, Range("D" & ActiveCell.row).Value, "Surname"
Copy_Cell_To_Form_Field wdDoc, Range("E" & ActiveCell.row).Value, "Forenames"
Copy_Cell_To_Form_Field wdDoc, Range("B" & ActiveCell.row).Value, "Ref"

wdDoc.SaveAs ("C:\... Read more

A:import excel data into word form fields

Read other 6 answers
RELEVANCY SCORE 88.4

I've actually written a line of code that is too long to fit in the macro window. How can I continue the code on another line in the programming window? I've tried using &'s but it doesn't work. Thanks.
 

A:Excel Macro - Code Continuation

Read other 6 answers
RELEVANCY SCORE 88.4

Hello to All!

Does anyone have a code/macro that will find a certain text in cells (*total) in a column, then based on the text found, search another column for the totals association with it, format the color & bold that total, and calc the sum in a 3rd cell?
Column 1 would have something like, abcTotals
defTotals
ghiTotals .........etc.
Column 2 ...something like, $63.00 (blue & bold formatting when found) ........ etc.
Column 3 would hold the sum of all the*Totals

Thanks!!!
 

A:Solved: Excel 03 Macro/Code

Read other 8 answers
RELEVANCY SCORE 88.4

Hi,

I'm a very new VBA user and have spent the better part of 2 day on this great website looking for a similar problem i've run into.

My problem is that i want information from "test" workbook~"results" worksheet to automatically update the "Tech Data" worksheet as well as export an update to the "Master" workbook.

FYI, two separate people input data: iron&manganese and Corrosion&scale inhibitor residuals. This data is exported to the 'results' worksheet and the same simple formula can be used to export to "tech data" worksheet. I am having a hard time getting the 'tech data' worksheet to export to the master workbook. I'm hoping someone can help me ensure the master file compiles all informaiton being exported to it. Note that the master file has several other headings as i was hoping to expand on the starter macro.

I created the tech data worksheet so data placement matched the master workbook format.
 

A:Solved: Excel-macro code help

Read other 15 answers
RELEVANCY SCORE 88.4

I have a macro in excel which auto fills a specified column with the formula in the first cell.

Assuming that Row 1 contains my headers, the code looks as follows:

Range("M2").Select
Selection.AutoFill Destination:=Range(M2:M1845)

Is there a way instead of specifying the range to ask the macro to just carry on filling the formula until it reaches the end of the data entered, i.e. the last row of data?

I'm sure it was something like Range = xlDown, but every attempt seems to fail.

I am running Excel 2007 by the way.
 

A:Solved: Excel Macro Code

Try using

Selection.AutoFill Destination:=Range("M2:M" & Cells(Rows.Count, "M").End(xlUp).Row)

Rollin
 

Read other 2 answers
RELEVANCY SCORE 88.4

I'm trying to code a macro with (among other things) a section using countif(). For now let's just assume that the range is fixed, say C3:C43. The criteria needs to reference 2 other cells. I want the countif to equal

(the # of values <= the # in cell(31)) - (the # of values <= the # in cell(30)), both in a different column.

I can select the range but I don't know excel's code for using countif() in a macro. Here's what I tried so far:

Range("C3:C43").Select
a = Selection.CountIf("<=m-<=p")

where m=# in cell(31) and p=# in cell(30)
I get an Object doesn't support this method error.

How can I get there? TIA
 

A:excel vb macro code for countif()

Read other 7 answers
RELEVANCY SCORE 88

I would like to write an Excel macro that includes pausing for data entry from the keyboard. For instance I have a spreadsheet for a simple bookkeeping project. I would like a macro that, when invoked, would do this:
Type "Current income" in the cell the cursor is on.
Move two columns to the right.
Stop for me to (1) enter a dollar amount and (2) hit the "enter" key.
Move two columns to the right.
Stop for me to (1) enter a dollar amount and (2) hit the "enter" key.
End of macro.

My thanks for any help!!
 

A:Excel macro for entering data

Read other 11 answers
RELEVANCY SCORE 88

Hello - I want a macro in excel 2003 or (workspace pro 6.5 or Happy Harvester2) that collects info from the web sub page, one case after another case, till done. It should put selected info in excel, each case on a different line. However, the excel "repeat" under "edit" that I am using, fails to accomplish moving to the next line; instead it repeats the first line. I have made the macro with mouse clicks, and dont understand VBA:

Desired excel result"
ADDRESS CITY OWNER LENDER CASE DATE
123 Maple | Xenia, Ohio | Able Jones | Bell Mortgage | cv1223 | 11/04/2010
380 bell st | Dayton, Ohio | Blule | Bank America | dv 3348 | 10/03/2010

Web page listing cases URL: http://www.co.greene.oh.us/pa/pa.urd/PAMW6500
Able jones cv 1223
brown cv 129
blule cv 3348

Sub page with details on one case
URL: http://www.co.greene.oh.us/pa/pa.urd/pamw2000*o_case_sum?473007
Bell Mortgage vs Able JOnes cv1223
address: 123 Maple
Xenia, Ohio 45385

filed 11/04/2010 status open
Thanks, thanks so much.
 

A:Excel macro geting web data

What program did you actually create the macro in? Can you post your existing code?

Rollin
 

Read other 1 answers
RELEVANCY SCORE 87.6

Im trying to use i believe DAO from an access 2007 database into Excel 2007. The database far as i know was created in Access 2007 on the same server that im trying to run Excel 2007 on and everytime i put in the dang connection string i get an "unrecognized database format" error. This is driving me mad.

I want to read the database and start scrolling through records and pulling in only the data i want into specific cells. I've done this before and never had this problem.

Basically the user will put a date in a cell. Then i want to start reading records and everytime i find a certain value in a cell i need to pull parts of the record into cells in a worksheet, then move onto the next record on that date. But i can't get past the damn connection.

I have the Microsoft DAO 3.6 object library and the Microsoft Office 12 reference and nothing....tried on 2 different versions of excel and i get the same message regardless.

Any ideas hints suggestions.

Code:

Sub CrankUpdate()

Dim db As Database, rs As Recordset
Dim R As Integer, C As Integer

Set db = OpenDatabase("W:\WORK ORDERS 2010_1.accdb")
Set rs = db.OpenRecordset("Talisman")

RecordCount = rs.RecordCount
For R = 1 To 75


Next R

End Sub
 

A:Trying to Import from Access into Excel

I am not familiar with how Excel 2007 talks to Access 2007, but in earlier versions you could either Copy & Past Special Link to a database query or create an Excel Query.
Or you could use DDE (Direct Data Exchange).
 

Read other 3 answers
RELEVANCY SCORE 87.6

Hello,
I am attempting to build a macro to import a specific range of cells from Excel into MS Access. Is it possible to import the range A3:A7 from excel into column A of access, then the cell A8 into column B. It would also be from a sheet within the excel sheet named "Selected users". The added twist to it is that there are multiple excel files to import from, but the ranges will all be the same.
I know this is a very specific question but would anyone have any VBA code to support this action.

Thank You,
Izzy
 

Read other answers
RELEVANCY SCORE 87.2

Hi all, hope someone can help.

I have a list of contracts, each run for 3 years exactly.
One starting 12/04/2007 ends: 11/04/2010.

I have the following code:

Code:

Sub Add3Years()
If IsDate(Range("B2")) Then
ActiveCell.FormulaR1C1 = DateAdd("yyyy", 3, Range("B2"))
ActiveCell.FormulaR1C1 = DateAdd("d", -1, ActiveCell.FormulaR1C1)
End If
End Sub

However, as you can see the initial range is as the specific cell B2. What I need is for the macro to see that I am on row 20, goto Column $B on row 20 and use that value instead of
Range("B2")Click to expand...

.

I've tried offsetting, but that doesn't really work, and I've seen
varRowNumber=Activecell.Row will return the row numberClick to expand...

If someone could help join the dots, that would be appreciated.
Thanks.
 

A:excel vba/macro: relative reference in code.

Read other 7 answers
RELEVANCY SCORE 87.2

Hello,
I have the following bit of code as part of an Excel macro:

'In G1, enter formula to apply 6 character French code for AUT vehicles
ActiveWindow.ScrollColumn = 1
Application.Goto Reference:="R1C7"
Range("G1").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNUMBER(FIND(""AUTFORD"",RC[-6])),""FORD00"",IF(ISNUMBER(FIND(""AUTVAUX"",RC[-6])),""VAUXHA"",IF(ISNUMBER(FIND(""VNVF1"",RC[-6])),""NISSAN"",IF(ISNUMBER(FIND(""VF1"",RC[-6])),""RENAUL"",IF(ISBLANK(RC[-6]),"""",""MISC00"")))))"
Range("G1").Select

I'd like to be able to add more IFs to it, but there are already 7 conditions in there, which I believe is the max number of nested IFs allowed.
Specifically, at present, I want to add:

IF(ISNUMBER(FIND(""AUTNIS"",RC[-6])),""NISSAN""

But I may need to add more in the future.

Can someone help me write this in a better way? I'm no VB genius...
 

A:Excel 2003 macro - rewrite a little bit of VB Code

Read other 16 answers
RELEVANCY SCORE 87.2

Is there a code to where I can have a macro run every 5 seconds? If so, where would I place the code?
 

A:Solved: Excel Macro/Run Code Every 5 Seconds

Hi
here's how Ive done it. In this example C1will contain a single digit representing the # seconds so that I can control it from the sheet rather than editing the macro. If you just want to hard code the 5 seconds you can set Freq="00:00:05" or get rid of that line and edit the next one to

TimeToRun = Now + TimeValue("00:00:05")

There are 2 macros involved ....

the first sets the time period to run the routine you want and then "calls" that macro
the second is the routine you want to run every 5 seconds. The final line in this macro is to call the first macro to start the timer again
Code:

[B]Sub ScheduleCopyPriceOver[/B]()
Freq = "00:00:0" & Range("C1")
TimeToRun = Now + TimeValue(Freq)
Application.OnTime TimeToRun, "[B][COLOR=red]CopyPriceOver[/COLOR][/B]"
[B]End Sub[/B]
'
[B]Sub CopyPriceOver[/B]()
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'Do what you need to do
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Call [B][COLOR=red]ScheduleCopyPriceOver[/COLOR][/B]
[B]End Sub[/B]
Finally, if you want to start the timer when the the file is opened you can add another macro like this
Code:
Sub auto_open()
Call ScheduleCopyPriceOver
End Sub
All of these macros can be in a normal module.
 

Read other 2 answers
RELEVANCY SCORE 87.2

one more little problem.

I have this code (which I got from recording a macro) for placing a chart on a page:

Range("C920").Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("FixedPointsBlank").Range("C920"), PlotBy _
:=xlColumns
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).XValues = "=FixedPointsBlank!R9C3:R20C3"
ActiveChart.SeriesCollection(1).Name = "=""Frequency"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="FixedPointsBlank"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Frequency Plot for Fixed Measurements"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _
"Upper End Value (cpm)"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Frequency"
End With
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = False

On the 2 lines that have the Range function I have replaced inside the parenthesis with: Cells(30, 8), Cells(lastUEV, 9)
which works just fine. I have to do this because from one run to another the range size will change.

The other line that needs to be changed the same way is:

ActiveChart.SeriesCollection(1).XValues = "=FixedPointsBlank!R9C3:R20C3"

However, when I replace the R9C3:R20C3 with Cells(30, 8), Cells(lastUEV, 9) I get an App... Read more

A:Solved: excel vb macro code for chart

Read other 10 answers
RELEVANCY SCORE 87.2

Hey,
Can someone tell me what I need to do to have a macro in excel, open another file?
For example, I need the following file opened:
c:\FolderA\FolderB\FileC.xls
How do I do this, and also, at the end of the macro, I want that file closed. Please help!
Thanks,
 

A:VBA Code to open file in Excel Macro

Give one of these lines of code a shot...
Code:

Workbooks.Open("Book2.xls")

or
Code:

Workbooks.Open("C:\MYFILES\BOOK2.XLS")

What are you going to do with the spreadsheet once opened?
 

Read other 2 answers
RELEVANCY SCORE 87.2

I have a column that will have values changing but not in consecutive order. For example if I had range A1:A10, only A1, A4, and A9 might have values, but the rest of the cells would be blank. I need to be able to generate A1, A4, and A9 into the next column consecutively as B1, B2, and B3. The A column values could change (including the number of values) and I need the B column to react accordingly. So the next time I run the data, I might have A2, A3, A4, A6, and A10 with data in them - so I need column B to be able to react and put the values (in order) as B1, B2, B3, B4, and B5. Oh and I need all this done in a MsgBox. Can you help? Thanks so much.
 

A:Excel Macro Advice - Data Consolidation

Read other 9 answers
RELEVANCY SCORE 87.2

I need assistance with creating a macro. I need to create a lot of different reports but I want the macro to create them for me. Is there way to create a macro by: having a list of data, sorting it all by column A, then copying whatever is the same name in column A (and whatever is associate with column A- to the right) and inserting it into a new worksheet and then doing it for all the different data that is in column A. Then once it is put into the worksheet, to save it with whatever the name is from Column A in the first worksheet.

I figured that there needs to be a loop statement in the end so it will get all the grouped data together into seperate worksheets.

Thanks for your help. I have attached a spreahsheet to get a better idea of what I was looking for.
 

A:EXCEL MACRO- sort, copy different data

Read other 6 answers
RELEVANCY SCORE 87.2

I have a need to move data from one worksheet that has an associated date to the correct spot in another worksheet with column that runs from 1/1 to 12/21.
Worksheet 2
1/1
1/2
1/3
1/4
...
12/31

Worksheet 1 has:
1/29 45
1/30 23
2/4 13
The macro would read the date in column A of worksheet 1 and put the data into the correct row of column B in worksheet 2. So that after running the macro worksheet 2 would look like this:

1/1
...
1/28
1/29 45
1/30 23
2/1
2/2
2/3
2/4 13
...
12/31

I hope my intention is clear and understandable.

Thanks in advance for any help,
tom
 

A:Excel Macro to move data into correct rom

Read other 8 answers
RELEVANCY SCORE 87.2

Hey,

Posting here before, I received several prompt replies. Due to my success, I will try once again, but this time with a different issue/question.

I have a spreadsheet with 1 column and 3000 rows. In each row, there is a 1 cell block with a series of numbers/characters. I need some type of formatting/macro that will extract only certain characters from each row and place them somewhere else. In example, I have the following rows:

7r74$#@$i4 i3o45$#---$#@$#/4432438
4327$##@$#4343u3i33$$( #343432822
4324*& 345435**(&^*&^342434234324

I need to extra the 12, 13, & 14th character of each row, whether it be a letter, number or space, it doesn't matter. Now the 12, 13 and 14th character need to be placed in there own newcolumn to the right of where they were prior. So the results, would look like this:

7r74$#@$i4 i3o45$#---$#@$#/4432438 i3
4327$##@$#4343u3i33$$( #343432822 343
4324*& 345435**(&^*&^342434234324 435

The post here will not let me space it correctly, but to the right of the first entry would be " i3", the second, "343" and the third "435".

So, it seems pretty simple, but I am not knowledgable enough with formulas, macros or vb to be able to figure this out. I also need to be able to adjust the formatting/macro/vb script every couple months as the "character extraction" place could change.

Thank you, hopefully someone is familiar with what I am trying to do and can understand this basic i... Read more

A:Excel, Formatting/Macro Creation for Data

Are you okay with just a formula? Try this if your data is in A1:

=Mid(A1,12,3)

12 is the starting position, and 3 is the number of characters to return.

Let us know if you do need it in a macro.

HTH,
 

Read other 3 answers
RELEVANCY SCORE 87.2

Hi,
I have a major problem crunching large amounts of numbers coming off an instrument I work with but the file format it puts the results into is terrible and I cannot change it. So I must export the results in a format which needs some serious formating before it becomes of any use to me.

I have used excel macros to delete rows and rows with specific text values. But now I would like to create a macro that will reorganise this data in a more user friendly format. I did use the sort asending command to partially solve the formating problem but it would still require alot of work/time to organise it into the final format I need.

This is the current format of the instrument date:

Analyte Meas. Intensity
Rh 10
Y 98
Cs 66
Co 98
Analyte Meas. Intensity
Rh 10
Y 114
Cs 72
Co 86
Analyte Meas. Intensity
Rh 20
Y 108
Cs 44
Co 90
Analyte Meas. Intensity
Rh 20
Y 68
Cs 44
Co 66
Analyte Meas. Intensity
Rh 490.008
Y 58
Cs 34
Co 78
Used the "sort asending" command to separate data into useful form but need to get the data into either of the two tables shown below

Analyte Meas. Intensity
Analyte Meas. Intensity
Analyte Meas. Intensity
Analyte Meas. Intensity
Analyte Meas. Intensity
Co 98
Co 86
Co 90
Co 66
Co 78
Cs 66
Cs 72
Cs 44
Cs 44
Cs 34
Rh 10
Rh 10
Rh 20
Rh 20
Rh 490.008
Y 98
Y 114
Y 108
Y 68
Y 58
I would like to turn the data into this final format using a macro.

Co Cs Rh Y
98 66 10 98
86 72 10 114
90 44 20 108
66 44 20 68
78 34 490.008 58

Can somebody help me please ... Read more

A:create an excel macro to reorganise data?

ZIP and post a sample workbook.
Rollin
 

Read other 1 answers
RELEVANCY SCORE 87.2

Hi,
I am trying to run a macro which will insert a row of formula to the last row of my data. Say for example.

A1: Formula
A2: 1
A3: 2
A4: 3

I wish to set the macro to always insert into the last row of column A, instead of at Cell A4. Something like,

A1: Formula
A2: 1
A3: 2
A4: 3
A5: Formula

something to do with lastrow formula? Any help would be greatly appreciated.

my current macro is written like this,
'
Rows("13:13").Select <- the formula row
Selection.Copy <- copying the formula
Rows("17:17").Select <- i wish this to go to the last row instead**
Selection.Insert Shift:=xlDown
End Sub

Any help would be much appreciated.

Thanks
 

A:Excel Macro : inserting copied data to the last row

Howdy, and welcome to the board.

Something like this:
Code:

Sub test()
Dim lngLastRow As Long
lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row
MsgBox lngLastRow
End Sub

then you could use for the next formula row:

Cells(lngLastRow + 1,1)
 

Read other 3 answers