Posts by stildawn

Hi All

Got a really tricky one... Can a excel sheet be saved as CSV but with Pipe "|" delimiter instead of ","?

I know there is a way to change windows wide delimiters from "," to "|", but that is not ideal, is there a way for excel VBA code to do it?

Or alternatively, VBA code that changes the windows value, saves a CSV, and then changes back the windows value to a ","?

Thanks in advance.

Re: Combining data into a single string - leading zeros

Quote from Trebor76;777205

Hi stildawn,

Try this:

Code

.Range("AA2:AA" & Lastrow).Formula = "=E2&""_""&IF(F2<10,""00""&F2,IF(AND(F2>=10,F2<=99),""0"" & F2,F2))&""_""&G2"

Regards,

Robert

Display More

Thanks, this one seems to have worked in the few tests I've done.

So I understand it, does the ".formula" part apply the "=E2& etc" in AA2 then copy it down to the end like you would if you dragged the formula down manually in excel?

Re: Combining data into a single string - leading zeros

Yeah, and a loop was something I was hoping to avoid.

Re: Combining data into a single string - leading zeros

Ok, So originally the second column has it as value "2", how in VBA can I change that to "002" and text?

There are other values sometimes like "504" etc, which show correctly cause the number is a real number, but there are a few ones like 002 or 006 or 010 etc. All need to be three digits long.

Re: Combining data into a single string - leading zeros

That generates the same result as my code: "10153_2_S"

The second column which contains "002", excel see's it as "2" but the number-format shows "002"

What if I changed that column to text or something perhaps?

EDIT: Nope, tried setting the second column to numberformat "@" didn't work, and now the second column shows "2" instead of "002".

Hi All

Run into a small issue.

I have three columns:

[TABLE="class: grid, width: 500"]

ID

Color ID

Size

10153

002

S

[/TABLE]

I use this code to place them into a string (for a large list of data):

.Range("AA2:AA" & Lastrow) = "=Indirect(""RC[-22]"",0)&""_""&Indirect(""RC[-21]"",0)&""_""&Indirect(""RC[-20]"",0)"

As you can see, this uses indirects to combine them all base on the above tables location to the column AA (E, F & G respectively)

This works fine ish, it will generate the following: 10153_2_S

The issue is, it cuts off the leading zeros of the middle value, it should return: 10153_002_S

Any ideas on how to force excel via VBA to do this?

Thanks

Re: Getting LEFT on an entire column without a loop

Ah forgot about the range formula thing, thanks that will do it nicely.

Hi All

I have a massive column of data that looks like this "XXX_XXX_XXX_XXXX_XXXX"

This column can be massive long (like thousands of rows). So I want to avoid a row loop if I can.

How can I in VBA get a simple LEFT say 7 characters on the entire column without a loop, so the results will be "XXX_XXX" cutting off all the other data past 7 characters.

I'm well aware I can do a loop to do this, but loops of such a massive length slows things down and I wish to avoid that if possible.

Cheers

Re: Listbox Rowsource Not Populating Correctly

Ah good to know, makes sense now. Thanks

Re: Listbox Rowsource Not Populating Correctly

Figured it out, it needed (External:=True) at the end of the address part.

Dont know why though

Hi All

I have a list box in which I am trying to show names and email addresses from Sheets("Emails").Range("A:B")

Here is the userform_initialize code:

'##### Fills in email addresses ###############################################
Lastrow = ThisWorkbook.Sheets("Emails").Range("A" & ThisWorkbook.Sheets("Emails").Rows.Count).End(xlUp).Row
If IsEmpty(ThisWorkbook.Sheets("Emails").Range("A1").Value) Then
Else
    With Menu.EmailsList
        .ColumnCount = 2
        .ColumnWidths = "45;95"
        .RowSource = ThisWorkbook.Sheets("Emails").Range("A1:B" & Lastrow).Address
    End With
End If
'##############################################################################

This basically checks if there are any names and addresses in the 'Emails" sheet, and if so populates the "EmailList" listbox on the userform "Menu".

But the code acts odd, for example if I'm currently on the "Emails" sheet in excel, then the code works fine, however if I am on the "Master" sheet (which is the only sheet the end users will ever see) then the listbox populates range("A1") on the master sheet, which is just a title and is completely not what I want.

I can't see where the listbox is getting this "Master" sheet reference from when populating, all the code clearly references ".RowSource = ThisWorkbook.Sheets("Emails").Range("A1:B" & Lastrow).Address"

What could be going on?

Regards

Re: VBA to open a workbook and run the startup VBA of that new workbook.

Any ideas on this one? Is it possible? Or do I need to have another approach.

Re: X to close a userform, open another?

I'll look into the tabs idea and see if it works, thanks for the tip.

Hi All

I don't know if what I'm trying to achieve is possible, but basically I have a template file that does a bunch of stuff, at the end of the process, it saves as a completely new .xlsm file (thus the new file retains all the code).

What I want to achieve is that from the users point of view, in the main menu form, they click the button and it does its stuff, saves the new file, closes the template file and opens the new file loading up the main menu again.

Here is my code thus far which isn't working:

'##### Creating new master file, closing template file #################
ThisWorkbook.Sheets("Settings").Range("A2").Value = 1
InstallPath = ThisWorkbook.Sheets("Settings").Range("A1").Value & "\"
MPONo = ThisWorkbook.Sheets("Master").Range("D6").Value
CurDate = Format(Date, "yyyy.mm.dd")




On Error Resume Next
MkDir InstallPath & "MPO Masters"
On Error GoTo 0




MasterSavePath = InstallPath & "MPO Masters\" & MPONo & " " & CurDate




MkDir MasterSavePath
ThisWorkbook.SaveAs Filename:=MasterSavePath & "\" & MPONo & " Master File - " & CurDate & ".xlsm"
Workbooks.Open MasterSavePath & "\" & MPONo & " Master File - " & CurDate & ".xlsm"
Application.Run "'" & MasterSavePath & "\" & MPONo & " Master File - " & CurDate & ".xlsm" & "'!" & "Startup"
ThisWorkbook.Close
'########################################################################

Ideally just opening the new saved workbook would start this code:

Private Sub Workbook_Open()


If IsEmpty(ThisWorkbook.Sheets("Settings").Range("A1").Value) Then
    MsgBox "No installation folder detected, please enter settings and set installation folder path.", vbExclamation, "No Installation Detected"
Else
End If




If ThisWorkbook.Sheets("Settings").Range("A2").Value = 0 Then
    With Menu
        .StartUpPosition = 0
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
        .Show
    End With
Else
    If ThisWorkbook.Sheets("Settings").Range("A2").Value = 1 Then
        With Menu1
            .StartUpPosition = 0
            .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
            .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
            .Show
        End With
    Else
    End If
End If




End Sub

But it wasn't the new workbook would open and the template file would close, but the new workbook would not run its Workbook_Open code.

So then I tried adding an identical code into a module, and have the template file call that module when opening the new workbook:

Public Sub Startup()


If IsEmpty(ThisWorkbook.Sheets("Settings").Range("A1").Value) Then
    MsgBox "No installation folder detected, please enter settings and set installation folder path.", vbExclamation, "No Installation Detected"
Else
End If




If ThisWorkbook.Sheets("Settings").Range("A2").Value = 0 Then
    With Menu
        .StartUpPosition = 0
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
        .Show
    End With
Else
    If ThisWorkbook.Sheets("Settings").Range("A2").Value = 1 Then
        With Menu1
            .StartUpPosition = 0
            .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
            .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
            .Show
        End With
    Else
    End If
End If








End Sub

But that didn't work either, the new workbook opens and does nothing, and the template file closes. Thus the user is left sitting with a spreadsheet and no user form main menu showing.

Is this possible?

Regards

Hi All

Having epic issues with this, and I'm fairly sure it should be simple.

Basically, early on in a new project (its been a long time between projects so I'm quite rusty)

Have two forms:

"Menu" (the main menu)
"Settings" (the settings menu)

A button on Menu, closes "Menu" and opens "Settings", code below:

Private Sub SetBut_Click()


'##### Opens up the settings menu #######################################
Unload Menu




With Settings
    .StartUpPosition = 0
    .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
    .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
    .Show
End With
'########################################################################




End Sub

The there is a bunch of stuff on "Settings" but basically all I want to do is have the x button close (fully) the "Settings" menu and re open "Menu".

Here is the full code of my "Settings" form thus far, you'll see I have a command button that works well, just trying to get the X button to do the same.

Private Sub BackBut_Click()


'##### Closes Settings menu and reopens Main Menu #############################
Unload Settings




With Menu
    .StartUpPosition = 0
    .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
    .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
    .Show
End With
'##############################################################################




End Sub




Private Sub BroBut_Click()




'##### Opens up dialog box for user to select new folder location #############
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = strPath
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
End With
'##############################################################################




'##### Populates both database and textbox with selected folder path ##########
NextCode:
FoldPathText.Value = sItem
Set fldr = Nothing
'##############################################################################




End Sub




Private Sub ClearBut_Click()




'##### Clears all data in Template, used if they somehow messed up file #######
ans = MsgBox("Continuing will clear all data from template file, please confirm ok to proceed?", vbOKCancel, "Clearing All Data!")
If ans = 1 Then
    With ThisWorkbook.Sheets("Master")
        .Range("D6:E15").ClearContents
        
        Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
        If Lastrow < 18 Then
        Else
            .Rows("18:" & Lastrow).Delete
        End If
    End With
Else
End If
'###############################################################################




End Sub




Private Sub SaveBut_Click()




'##### Enters in textbox folder path into spreadsheet settings sheet ###########
ThisWorkbook.Sheets("Settings").Range("A1").Value = FoldPathText.Value
ThisWorkbook.Save
'###############################################################################




End Sub




Private Sub UserForm_Initialize()




'##### Fills in folder path textbox with correct info upon opening menu #######
If IsEmpty(ThisWorkbook.Sheets("Settings").Range("A1").Value) Then
    FoldPathText.Value = Application.ThisWorkbook.Path
Else
    FoldPathText.Value = ThisWorkbook.Sheets("Settings").Range("A1").Value
End If
'##############################################################################




End Sub




Private Sub Userform_QueryClose(Cancel As Integer, CloseMode As Integer)




'##### Makes Main Menu load when X button used to close #######################
If CloseMode = vbFormControlMenu Then
    Call BackBut_Click
    Cancel = True
End If
'##############################################################################




End Sub

Basically currently, the X button leaves the "Settings" form open (now behind the reopened "Menu"), this is not ideal, cause if you wanted to reopen the "Settings" form from the command button on "Menu" again, then it errors out cause its already open.

Any ideas?

Thanks

Re: Filter A Set of Dates Between Two Dates - VBA

It doesnt matter where it is, the E3 date is only used once for the filter then its pointless, even the date column themselves is eventually deleted once the date range has been achieved.

The above code is messy cause I just threw it together to test out, I have a finalized version now which is slim.

Also I have managed to solve the issue, apparently the sort between dates thing only accepts the US mm/dd/yyyy format, if I change both Start/EndDate to this mm/dd/yyyy format, then it works as desired.

Hi All

I have a list of data, with a date column in NZ format (so dd/mm/yyyy)

I would like to filter all the data selecting rows between two dates, I have this code:

DateSplit = Split(.Range("E3"), "/")    
FormatDate = DateSerial(DateSplit(2), DateSplit(1), DateSplit(0))
    
    If Left(.Range("E3"), 2) = 15 Then
        StartDate = FormatDate - 14
        EndDate = FormatDate
        Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("$A$1:$E$" & Lastrow).AutoFilter Field:=1, Criteria1:= _
            ">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate
    Else
    End If

It grabs a date from a E3 which is manually entered and then makes sure its in the right format (one of my main hates with excel is its date handling).

Then it tried to filter it.... But it returns zero rows, as in no data is left.

I got the filter code by recording a macro, which worked perfectly when i was recording it. But if you run the recorded macro again, it does the same thing as mine, returns nothing.

Any ideas?

Or alternatively a completely different approach to getting the date range required? This data is massive though like 21000 lines or so, so it would need to be efficient.

Thanks

Re: Network Located Files - How to work with VBA

Yeah I can navigate to the workbook and open using the excel "open" button (you know like usual)

But using VBA seems to throw the errors, I've done this a hundred times before with no issue, usually with network drives it you put the "\\" path like "\\ctffs01\" above it works fine, but this is odd, it seems like the network drive is mapped to G:\ but neither work.

Failing this, I'm gonna have to get them to manually copy and paste things in, which sorta defeats the whole purpose of streamlined VBA processes.

Hi All

I'm building a Excel VBA tool, basically wanting to load in different spreadsheets and do some basic stuff.

Anyway, on this machine from what I can see they log in into a terminal server type set up. And I think because of this Excel is throwing errors when trying to open saved files.

Codes I'm using:

Workbooks.Open "\\ctffs01\Auckland\common\Breakbulk\ACTIVE - Import Air Breakbulk\1.xls"

I've tried to test it a bit using:

File = Application.GetOpenFilenameMsgbox FileWorkbooks.Open File

The msgbox returns this value on a actually browsed to and selected file: "G:\Auckland\common\Breakbulk\ACTIVE - Import Air Breakbulk\1.xls"

However both using the \\ctffs01 and the G:\ paths above in the Workbooks.Open lines, causes the usual "file cannot be opened" error.

I'm guessing somehow they have locked Excel VBA out of accessing files on the network? Is there anyway around this or anything I'm missing?

Cheers

Re: More Efficient Way To Code This - Hiding Rows Based On User Input

In regards to "objects" are you refering to the drop down lists? I didnt use actual objects, just the data validation feature? Does this still create a object event for the drop down cells?