I have created an AddIn for a project I'm working on using the 'Open' command in Excel Vb. While this worked perfectly for the file I was testing it on (1,740,754 bytes) it doesn't work on a new file (121,445,125 bytes).
On testing the code, one line at a time, it gets stuck on
Line Input #1, DataLine
The files only contain one line so I suspect there is too much data for the command to handle.
Is there an alternative?
I have a multipage userform with about 7 pages. When page 5 is activated, it caused excel to completely lock up. This does not happen everytime but randomly, and I can't seem to figure out what is causing this.
Here is the code -
If Me.MultiPage1.Value = 5 Then
Call LaborCosts
End If
Sub LaborCosts()
If Not Range("A46").Text = "" Then
frmpricingtemplate.Label29.Visible = True
frmpricingtemplate.TextBox46.Visible = True
frmpricingtemplate.Label29.Caption = Range("A46").Text
Else
Is it possible that when you press a command button, that the first thing it does is to execute the code assigned to another command button (IE in another sub).
View 9 Replies View RelatedIs there any way to run a command line from excel?
E.g. i want excel to call this command:
ping 10.100.100.100 and then return the result to a txt file?
i hv following code
(i use generate macro)
my question is how to arrange the code from one line to multiple like :-
following code show in excel macro environment is one striaght line.
' Create new var on yr , and replace 2006 to CY06.
ActiveCell.FormulaR1C1 = _
How can I get at parameters passed on a command line to Excel 2007?
For example invoking Excel from Vista's Run command with:
"C:ProgramDataMicrosoftWindowsStart MenuProgramsMicrosoft OfficeMicrosoft Office Excel 2007" "C:GZNORPWeather StationParamTest1.xlsm" /e/abc/def
Should reveal the parameters "abc" and "def". But this does not happen!
Supposedly, the API function GetCommandLine does this, but I cannot seem to get it to work. Here's a sample of what I've been trying:
I've got an Excel file that uses 7-zip. It can execute a command line that will use 7-zip to go to a folder and zip each of the folder's files into its own zip file, placing the zips in a single, specified destination folder. I can specify whether to look in the source folder, or to also look in all of the source folder's subdirectories.
Question is, how do I tell 7-zip to just zip ONE file, and how do I tell 7-zip to zip a SPECIFIC LIST of files that I name (instead of looking through a whole folder)? how to use command line...
Code:
Sub a_zip_with_sub_directories()
Shell "cmd /c for /r ""C:Users aiDocumentsmonthly reportsFinal Copies of Monthly Reports"" %i in (*.*) do ""%ProgramFiles%7-Zip7za.exe"" a -tzip ""C:DATA aiipped\%~ni"" ""%i"""
End Sub
Sub a_zip_no_sub_directories()
Shell "cmd /c for %i in (""C:Users aiDocumentsmonthly reportsFinal Copies of Monthly Reports*.*"") do ""%ProgramFiles%7-Zip7za.exe"" a -tzip ""C:DATA aiipped\%~ni"" ""%i"""
End Sub
i have batch file that run from VBAexcel But the thing is i dont want to run batch file
i want everything on VBA . for example you use vba to cmd and then run this line of command
i have try but i just new to VBA and programing
how can i do it
here the code
this is from batch
Code:
@echo off
>summary.txt (
for %%F in (*chkpackage.log) do findstr /l %1 "%%F" nul||echo %%F:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A
)
and this is code in vba
Code:
FileSet = Sheet1.Range("C13")
txtFpath = Sheet1.Range("C7").Value
FilePath = txtFpath & "
es.bat"
[Code]...
i have to admit that i'm really not good in programing if you can add some explaination is a great thing for me to try to study
Is there a way to print an Excel Workbook from the command line? Is there a command line reference anywhere for Excel?
View 9 Replies View RelatedI found the following link that got me started [url], so now I have my code for reading/analyzing comand line parameters. Here is an example of the parm I'm passing in ....
View 9 Replies View RelatedI have a userform with a numerous textboxes, which I have labeled with similar text and a unique number at the end (num1_txt, num2_txt, num3_txt, etc.). I am trying to create a for loop that sequentially references the numbers of the text boxes and pulls the user-entered value in the text boxes into an Excel document.
Here is the code I have so far...
VB:
i = 1
For i = 1 To 18
Dim cyltrack As String
cyltrack = "Me.cyltrack" & i & "_txt.Text"
If Me.cyltrack.Value = "" Then
Cells((cellcount + i), 13).Value = "*"
Else
Cells((cellcount + i), 13).Value = cyltrack
End If
Next i
I am running into problems in trying to convert the string variable, cyltrack, into hard code that I could insert into Me.cyltrack.Value.
I have attached a spreadsheet example. I would like to be able to use a command button to pop up a series of questions for the user to input a value and have that value populate the directed cell.
Example from spread sheet:Click - "How many bid" button
First question should be " How many for Joe"
Input the number i.e. 2
The number input should go to the cell C5 in this example
Next question should be "How many for John"
I want to input the number 3
The number 3 should go into cell F5
How many for Jim
I need to input a number
The number should go to cell I5
How Many for Jerry
I need to input a number
The number should go to cell L5
I would also like the same function for the "How many won" button.
I'm looking to have an excell file shared on a windows network to a simple LAN (samba sharing), and then for a user to be able to click on a HTML link that will launch that excell pile, and to have it populate specific and predeterminined cells with information that will be handed to it by the HTML link from the website.
Think of a CRM web app that href is a link like: "LAN1Filesexcellbook01.xls?Mrs%20Wendy%20Jones?4%20Skin%20Street"
From something like that I would like to launch excell and have Mrs Jones' details populate into the predefined excell sheet. The HTML website can dynamically populate a HTML link depending on the customer being viewed.
So I'd like to know how two things
1) How to launch a network file in this way, while correctly handing it parameters or switched that it will be able to later:
2) Collect that information and distribute it into the cells I choose in VBA.
I'm familiar with Visual Basic .Net primarily, and have a small amount of VBA experience.
We would like to continue to use Google chrome if possible...
I've recently created a macro which takes a .csv file and scrubs the data and dresses it up for a report. I've finally got it running without any problems and have added it individually to 5 different user accounts on my network as an add-in, but need a way to share it across a computer network without allowing others to edit the file.
The report is generated multiple times a day by the different users, none of whom are very computer literate (not that I am much better, myself), and the macro over-writes the old file each time. I've read that it's possible to simply create a shortcut to the file that automatically opens the spreadsheet in read-only format, which would be perfect for what I'm trying to do, but I haven't been able to get it to work so far.
My current shortcut pathway is similar to this:
"C:Program FilesMicrosoft OfficeOffice15EXCEL.EXE" /R "C:UsersBenDocumentsOT Master.xlsx"
On my computer at work (Windows 7) the shortcut simply opens the spreadsheet so that anyone can edit it, instead of as a read-only file. On my computer at home (I'm trying to experiment) (windows 8.1) it won't even let me save the shortcut, saying I need to provide administrator permission & then cutting to a window that says "access denied".
I won't have the pathway for the actual file I'm trying to create a shortcut for until tomorrow when I get back to work, but I've formatted it essentially the same, directing to the excel.exe file, running the switch "/R", and then directing it to the actual file location.
The ultimate goal is to have multiple people be able to view the file without locking out those who need to update it.
Ok my data is like this:
data, data1, data2, "data99, data98, data97" ,data4, data 5 data, "data1a, data1b", data1, data2, data3 data, data, data, data,
and I use the following code to extract.
Code:
Open "D:data.csv" For Input As #1
do until eof(1)
Line Input #1, strData
myArray1 = Split(strData, ",")
For iCounter1 = LBound(myArray1) To UBound(myArray1)
Next iCounter1
Loop
Close
My problem is that it does not group the ones within the quotes, just splits them.
Two questions
1. Can my current code be modified?
2. I can use Input instead, to go one by one, and it recognises the quotes, but how can i tell I've moved to the next row of data?
My new Time in Motion sheet is coming along nicely with some snazzy programmed Command Buttons to fill data into another sheet.
One of my buttons is for 'other' tasks, so I'd like to programme a button to prompt first for a number of minutes (e.g. 30), and then a brief description (e.g. Meeting) and input the data side-by-side in 2 columns on sheet 2.
This is what I have for my other single-entry buttons, so what would I need to add?
Dim ans As String
ans = InputBox("Case reference:", "Data Entry")
Sheets("Sheet2").Range("F" & Sheets("Sheet2").Range("F" & Rows.Count).End(xlUp).Row + 1).Value = ans
I am trying to find the exponential equation from a set of data in excel. The problem is that the graph does not follow the scatter plotted points on the graph. How would you remedy this?
View 1 Replies View RelatedI have two sheets and two listbox's(ColumnCount8) and one command button.
lstInYard rowsource is set to sheet1
lstMilled rowsorce is set to sheet3
Iam trying to cut and paste the selections in lstInYard to lstMilled as well as the corrosponding row values in sheet1 to sheet3 by using cmdMoveSelected click event.
I have a 2 worksheets. One "Projects in Process"and the other "Project Completed". when the users fill in the information in cells A4-F4 (line 4 is the starting line and it could go down as far as line 23 or a23 - f23) In Projects in Process they press a "completed Project" command button and the data in a4-f4 copies over to worksheet "Projects completed". In the next available row (starting at row 4).
View 13 Replies View RelatedSo right now I have the
Private Sub CommandButton19_Click()
Dim product As Double
product = ActiveSheet.Range("J45").Value * ActiveSheet.Range("S44").Value
Sheets("Sheet3").Range("R2").Value = product
End Sub
I want to be able to switch the cell the product goes into based on a certain command button that I click(for example I have a command button for when I use copper wiring and one for aluminum wiring and I want when I click the copper wiring button for the product to show up in one cell, but when I click the aluminum button for the product to show up in a different cell , but on the same sheet). To be more clear I have a quantity command button(which is the code above) that i want to be able to recognize the difference between when the aluminum command button is clicked and when the copper command button is clicked.
I have 5 xactive command buttons on a sheet1 and 1 xactive command button on sheet 2. If button1 on sheet 1 is clicked I would like to run the code named close1 from the click event of the commandbutton1 on sheet 2, but if button 2 on sheet 1 is clicked I would like to run the code close2 under the click event of sheet 2 commandbutton1 and so on. Is there some way of achieving the above
View 4 Replies View RelatedI have below code to open DOS from excel. it is working fine.
I wanted to excecute some DOS command automatically after opening DOS, command is entered in the excel Sheet1 E5:E1000
Sub openDos()
Call Shell("cmd.exe " & dosCmd, vbNormalFocus)
End Sub
I have placed a CommandButton1 and CommandButton2 on the Sheet1....how do i then assign the macro code to these Buttons
CommandButton1 is called MANAGERS GROUPS (macro called Split Sheets)
CommandButton2 is called CLEAR MANAGERS GROUPS (macro called clear_managers)
It was working before as i just used rectangle shapes as the buttons but have since removed these shapes and used the CommandButtons
The error is below in RED
Code:
Option Explicit
Public TrainingRows
Sub SplitSheets()
'
Dim rCell As Range
Dim sht As Worksheet
Dim lastrow As Integer
[code].....
I have an excel spread sheet with say 20 or so sheets. Sheet 20 known as "Rates" is hidden. What I would like to do is when the user presses a command button say on sheet 1 he is asked for a password which will unhide the Rates sheet . If he gets it wrong a message pops up say incorrect please try again or have an exit button to cancel.
View 9 Replies View RelatedI haven't worked much with macros and the work I have had to do so far I have been able to figure out by searching forums such as this one. However, I need this community's help with what I am currently working on. Here's the code I have so far:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E2:E2")) Is Nothing Then
Exit Sub
Else
Range("D1").Select
Selection.AutoFill Destination:=Range("D1:D2"), Type:=xlFillDefault
Range("D1:D2").Select
End If
End Sub
I need this code/behavior to repeat in cells E3:E10, skip a couple rows, then repeat in E13:18 skip a couple more rows, repeat in E16:E21, etc.
Version: Excel 2000
OS: XP Pro
I have the following line from my code of which I am having trouble with I get an error:
If Left(IRange, 1).Value "1" Then
I am trying to find the first string in cell range ...IRange ... I know the Left function = Left(string, length) but can I reference a Range or perhaps cell location within the string argument??? This can be done in excel formulas, but can it be done with VB code??
IRange = Cells (iRow,iCol) and value in cells are Long
My goal is to try and find out how I can use my excel formula:
Left(D3,Find("1",D3)) and impliment this to my above VB line
Workbook performs perfectly 95% of the time but every now and again I have this issue where the formular do not calculate (it is set to automatic).
I have a workaround of doing a find and replace across all the worksheets of "=" replaced with "=" and this solves the issue.
The problem I am having is that some of the sheets are protected and some are not. The code I am using for the find and replace is as follows...
VB:
Option Explicit
Sub ChgInfo()
Dim WS As Worksheet
Dim Search As String
Dim Replacement As String
[Code] .....
I need to add to this code the unprotect command and then reprotect only the originally protected sheets?
I am sure these is a better way of doing this but im really short on time. This issue only occurs 5% of the time. the other 95% the formula all calculate fine!
I just need a way of doing the find and replace for = to = across all sheets in the workbook regardless of the sheet protection!!
I have a macro which I use to do all sorts of routine things on a spreadsheet for my employees. (you guys helped me with that too. thanks)
I would like to add a command button to this sheet to run series of code and I want to add this to the final step of a macro.
Question, is there a series of code, which would
1. create the command button on the active sheet, and
2. add the required code to the command button, and
3. embed that button and code onto the active sheet all at once.
?
I need to do this regularly so it is a currently manual process I am trying to replace.
Ex. of code I want placed:
Rows("1:1").Select
Selection.Font.Bold = True
This is not the exact code which I will use, but it will help me to understand what I would have to do to make it work.
I have recently obtained some help on code to bring up a user form based on cell change selection.
the code is as follows:
Dim c As Range
If Not Intersect(Target, Range("C6")) Is Nothing Then
With Sheets("Data Set").Range("D4:D43")
Set c = .Find(Range("C6").Value, LookIn:=xlValues)
If Not c Is Nothing Then
UserForm4.Show
End If
End With
End If
It brings up userform4, but the command buttons do not carry out the code behind them:
Private Sub CommandButton2_Click()
Sheets("Data Entry Form").Range("$C$6") = "Choose Employee"
Unload Me
End Sub
If I run user form seperate from the sheet and bring it up only in visual basic, it works...Any ideas???
PS this command button is no so it resets field C6. The other button is yes and performs copy paste function. Neither are operational when user form is brought up through the sheet change. Only works when initialized in VB.
I am trying to create a program to automate the gauging figures on oil barges, currently everything is done by hand and takes approximately 20 minutes to complete. I have the charts entered for the tanks already and have the code set so that when you click on a tank "gauge" it will enter the "volume" which corresponds to that gauge on a totals sheet. Here is where my problem is coming in
The barges are gauged at four points
"before loading"
"after loading"
"before discharge"
"after discharge"
I want to set a worksheet as the default page with four command buttons that let the user select which operation he wants to perform. before load figures. after load figures and depending on which operation they select have it enter the volumes in the appropriate cells on the totals sheet. So if someone selects "loadopen" command button I want the following code to run on my worksheets............................