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.
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)
write code to post Excel data directly to Peachtree using VBA rather than the export/ import process?
View 5 Replies View RelatedI 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
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() ....
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?
I've got a spreadsheet listing applications on PCs (assets), one line per application, and need to import that info into another document for each PC and save it with a file name which = the PC name (asset number). I've attached a couple of sample files to try to explain what i mean.For asset number PC037014 I need the corresponding fields filled and the list of applications pasted under where is says Additional Software List (inserting more rows if there are too many applications to fit in). Once the information has been populated, i'd like the file to be saved in a specific folder then move onto the next asset number in the list.
View 13 Replies View RelatedI have recorded a macro to import web data, from a sporting site,
problem is URL is date and event specific.
I've created an Auto_Run macro that imports a CSV file into a specified worksheet. That worksheet is referenced by VlookUp in another worksheet within the workbook. Everything works perfectly EXCEPT, in the midst of running the macro, the user is prompted to (RE)choose the csv file already specified in the macro. Neither the folder nor the filename ever changes, so I'm not sure why I have to keep indicating the file every time the macro runs.
PHP Code:
 'Sub Auto_Run() ' ' Auto_Run Macro ' '    Sheets("CSVData").Select    Cells.Select   Â
Selection.Delete Shift:=xlUp    With ActiveSheet.QueryTables.Add(Connection:= _  "TEXT;
G:TestProjectsWorkOrderTest.csv", Destination:=Range("$A$1"))       Â
.Name = "WorkOrderTest"        .FieldNames = True        .RowNumbers = False       Â
[Code] .......
I've never actually had a reason or need to use macros for data import or manipulation. At the moment I'm trying to work on a modeling problem which will output the data in the form of a large text file. The data is divided up using 1000 energy bins, and 1000 timing bins for each tally (unique set of output data) like in this file [URL].....Unfortunately because of the applications for the data I can't reduce the number of bins, and the software being used for the modeling will not provide a more convenient form for the output file.
How to create a macro that can extract this data from the output file and import it into a designated excel spreadsheet. It would be preferable to import each set of tally data from the initial output file into it's own spreadsheet, but as I said I have no experience using macros and I'm not even sure where to begin in making something that would work. or even a basic source for examples
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.
I am attempting to modify VBA code from [URL] .....
The section of the code I want to modify is below dealing with importing selective text. The original function returned a "False" if the line contained a keyword specified in the Array function. I have reworked it so that it shows a "True" if the line contains a keyword specified in the Array function.
My only problem is that it only does it when the keyword appears in the beginning of the line, as it uses the "Left" operator in looking at line. How do I configure the function so that it returns a "True" if the keywords appears anywhere in the line?
I'm having difficulty trying to get the second file appended to the bottom of the 1st imported file. I get "run-time error '13' type mismatch". There is no difference between the two files. I'm thinking there is a problem with my range statement in the second file import, but this range works fine in other macros. Here's my code so far:
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;\server1usersmydataIMPORT1.CSV", Destination _
:=Range("$A$1"))
.Name = "IMPORT1"
.FieldNames = True
.RowNumbers = False
[Code] .........
I don't know if this is possible, but is there a way to have a macro in Excel copy and paste charts into a Powerpoint file? I've got a program that creates all the material needed for a Powerpoint slide deck and I want to automate the copy/paste work.
View 2 Replies View RelatedI am trying to write a macro which creates a new worksheet with the name kenmerk (i) and then import five different textfiles in this worksheet.
I was wondering how to define the name of the textfiles in the code. The code I get with record macro is: ...
Is it possible to write a macro that can import VBA code in a text file then execute it? I need this functionality as I have produced a corporate spreadsheet template that goes out to many people and I will need to be able to update it as requirements change once it has been distributed. So my idea was to build a macro in the template that has the code in it to import the "update code" from a text file that I would send to all the folks that have the template. If the template was centralized, that would save me from this issue but it is however going to be distributed widely.
View 5 Replies View RelatedI am writing some code to open up a file and use the text import as the files are data files. i currently have
View 4 Replies View RelatedMacro to export and import a module and assigning different macro automatically ...
View 9 Replies View RelatedI'm trying to get the following code to work (as a practice run for a bigger set of spreadsheets) but it doesnt seem to work - no errors either. There are 3 workbooks all with 1 worksheet called "Data 1" with 3 columns of data (under 20 rows). These (theoretically) should copy into 1 called " Totals". The code I have used is: ....
View 9 Replies View RelatedI do not know VB coding but is in need of help on data filtering/matching. I have 2 very big excel workbooks with a few thousand lines and am trying to do some filtering/matching. Example below.
The two workbooks I have contains same data in column A and also column D from the first data set is the same as column B in the 2nd data set. I am trying to add column C and D from the 2nd data set to the 1st data set and need to make sure they match up to column D from 1st set and B from 2nd set. It is quite strenuous to do this manually,
A1 2 3 100A
A1 2 3 100B
A1 2 3 100C
A1 2 3 100D
A1 100A AA BA
A1 100B AB BB
A1 100C AC BC
A1 100D AD BD
i want to create a command button that would sort my data in descending order.
View 7 Replies View RelatedI have a worksheet that is automatically updated based on actions in other sheets. There are 10 columns of data (A-J) Headings are on row 6 and data starts on row 8. I have created 3 buttons to sort worksheet by client column (B), year-end column (E) and to bring it to original order (by numbers in column A). I have created the following codes that I have assigned to each button however it does not work.
Sub Macroclient()
Columns("A:J").Sort Key1:=Range("B8"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub
[Code]....
I have an excel spread sheet with the page title "Main". I would like to add a macro by pushing a button and a window pops up. It will have 4 columns or 4 questions. The first question will ask what is the date? You enter date, next question asks, What is the fundamental? You enter the fundamental. Next question is what is your name? you enter name and next question is enter what you need fixed. Once you enter that and press finished, the data is then place in the spreadsheet. All rows shift down 1 spot. B2 answer is posted in B2, question 2 answer is placed in C2, question 3 answer is placed in D2 and question 4 is answered in E2.
Each time someone presses the NEW ENTRY button this window pops up asking these 4 questions. The answers are then posted in cells B2 through E2 after shifting the entire page down 1 row.
I have written the below code to enter some formulas, validation, and formatting. I keep running into a problem when trying to add the first validation.
The debug msg is...
Run-time error '-2147417848 (80010108)':
Automation error
The Object invoked has disconnected from its clients.
Debug highlighted portion is...
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=PRODUCT" ....................
I'm using Excel 2010 and XP. I have data in rows 1 -4 that can not be altered. There are headings in Row 5, B-L & Row 6, F-J.
Data starts in Row 7 and follows the headings. This repeats for several thousand rows. I need a way to move Row 6, F-J and put it on Row 5, M-Q
Row 6, F-J and put it on Row 5, M-Q
Row 8, F-J and put it on Row 7, M-Q
Row 10, F-J and put it on Row 9, M-Q
Row 12, F-J and put it on Row 11, M-Q
....
....
....
Then delete blank rows below Row 5
I need a macro code to add and rename sheets as per data given in column "A".
ie if column A has
SBC
DFG
UY
IKJ
4 new sheets to be created with name given above (Column A). PS:- the data given in column A will not be limited to 4 rows, it ll be > or < 4.
the following code filters data and copies to worksheets specified in code, can this code be shortened to make the macro run faster. Also in my second criteria how can I put "Contains "PT2" in the criteria as nothing happens when i run the macro, is *PT2?
With shtRec
.AutoFilterMode = False
.Range("A7").AutoFilter Field:=8, Criteria1:="LEHMAN", Operator:=xlAnd
With .AutoFilter.Range
On Error Resume Next
Set rng = .Resize(.Rows.Count - 1).Offset(1).SpecialCells(12)
On Error GoTo 0
End With
If rng Is Nothing Then
MsgBox "No data to copy"
Else
rng.Copy shtLehman.Range("A6")
End If
.AutoFilterMode = False
End With
Following syntax is incorrect, what is wrong? This should happen: Select op a specific worksheet (named DATA) in the workbook Report.xls and I empty the contents
Then I try with a 'With' statement to read the contents of a file named "sourcedata.xls' with helds a worksheet named "source". The values in this sheet should be tranfered to the sheet DATA in the workbook "Report.xls"
Sub FetchDataFromClosedWorkbook()
Dim FileName As String
Dim SheetName As String
Dim cellRange As String
Const ReportPath = "C:ExcelReports"
Windows("Report.xls").Activate
Sheets("DATA").Select
Cells.Select....................
I have only ever created macros in existing workbooks. Is there an easy way to copy a macro from one workbook to another, without having to run a new macro in the new workbook and then paste the code into it from the old workbook?
View 7 Replies View RelatedI have managed to come up with the following macro. The problem I have is that some of the data are imported in text format, while other data are imported in general format.
I have a large data set, with more then 5,000 rows, and 2,000 columns, and I would like ALL the data imported at text. How can I modify this macro to make that happen?
Code:
Sub ImportData()
With ActiveSheet.QueryTables.Add(Connection:= _"TEXT;C:Users*****ATTRIBUTES-ModifiedExport.csv", Destination:=Range("$A$1"))
.Name = "ATTRIBUTES-ModifiedExport.csv"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
[Code]...
