Posts by Royzer

Re: VBA to copy 1st sheet of files from several different folders--Disregard Question

I am going to post a much shorter question that may resolve the issue. Please disregard this post.

Thank you.

Hi. The code in the bottom code window will copy the first sheet of all .xls files that reside in a single folder into a master workbook. For example:

Fpath = "S:\Accounting\News NOP\
Fname = Dir(Fpath & "*.xls")

I need to find a way to get through several different folders on a website to reach different files. An example of the path would be "https://infoportal.red.com/programming/filmcontracts/WBFO/Annual_Film/" This folder would have several files in it but I would need to open and copy the first sheet of the only one that begins with the string "Film" (ie, Film_Source_File_WBFO.xls).

There would be several folders, all like the one above except with different TV station call letters where that one has WBFO.

For example:

"https://infoportal.red.com/programming/filmcontracts/WLBT/Annual_Film/"
"https://infoportal.red.com/programming/filmcontracts/WJKF/Annual_Film/"

The folder at the end of each path would have a source file labeled "Source_File_(station name).xls

Is there any way to modify this code to do this, or another way to do it? Thanks! :smile:

Private Sub CommandButton1_Click()
Dim Fpath As String, Fname As String
     
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With
     
    Fpath = "S:\Accounting\News NOP\" ' change to suit your directory
    Fname = Dir(Fpath & "*.xls")
     
     
    With Workbooks("NOP-Backup file.xls") 'MUST BE OPEN
        Do While Fname <> ""
            If Fname <> .Name Then
                Workbooks.Open Fpath & Fname
                 'MOVE ONLY IF NOT   SAVING ON CLOSE. IF SAVING, USE COPY.
                Workbooks(Fname).Sheets(1).Move After:=.Sheets(.Sheets.Count)
            End If
            Fname = Dir
        Loop
    End With
     
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
    End With
    Dim i As Integer
Dim varLinks As Variant
varLinks = ActiveWorkbook.LinkSources(xlLinkTypeExcelLinks)
For i = UBound(varLinks) To LBound(varLinks) Step -1
ActiveWorkbook.BreakLink varLinks(i), xlLinkTypeExcelLinks
Next i
     
End Sub

Re: Complicated Look Up (without making changes to the values in the table)

Re: If Statement

Give this a try. It works for me:

=IF(G7="","No",IF(OR(N7>0,O7>0,P7>0),"Yes","No"))

You should be able to add the other 9 cell addresses to the three "OR"'s above (I think).

Re: Scroll workbook to Hyperlinked cells

Which Excel version are you on? If it's 2010, this should work. Go to:

File
Options
Advanced

Scroll down to near the bottom of the page where you see "Lotus Compatibility" and check the box beside "Transition Navigation Keys".

Re: Compare Data from Two different tabs and to identify variance items

Where do you want the results to go? That is, what range in which worksheet?

Re: combine 4 if statements

This may not be very pretty, but it works the way I understand you're looking for. Enter this formula into C3:

=IF(LEFT(A3,1)<>"A","NA",IF(G3="Bilingual",1,IF(G3="English",2,IF(G3="French",3,"NA"))))

Re: VBA - ignore certain columns when combining worksheets

AWESOME! You did it!

Thanks! :yourock:

Re: VBA - ignore certain columns when combining worksheets

Thanks, Pike. The code ran fine. The data in column "K" of the source files pasted into column "J", so we're one column closer to what I am looking for. The ideal result would be for the data from column K in the source files to be pasted to column H of the Master file sheet with that column being the last one with data. That would have the same effect as what I was looking for in the original post, but just phrased differently.

Basically, I am trying to copy these columns from the source files:

A - G, K

and past them to the master file sheet as:

A - H

Re: VBA - ignore certain columns when combining worksheets

Quote from pike;625406

Hello Royzer

one way is with VData as union of ranges

Code

With ActiveWorkbook.Sheets(1)


 Set vData = Union(.Range(.Cells(iFirstRow, "A"), .Cells(100 + iFirstRow, "I")), .Range(.Cells(iFirstRow, "K"), .Cells(100 + iFirstRow, "K")))  ''' get data
     ''' show progress
    Application.StatusBar = "Processing " & ActiveWorkbook.Name
End With
 
With wsTarget 
    nLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    If nLastRow = 1 Then
        i = 2
    Else
        i = 1
    End If
    vData.Copy Destination:=.Cells(nLastRow + i, "A").  ''' change Resize to suit rows and columns required
End With

Display More

Thanks Pike. I replaced my section of code with yours and this line is showing in Red:

vData.Copy Destination:=.Cells(nLastRow + i, "A"). ''' change Resize to suit rows and columns required

Hi.

This is part of the code that copies a range of cells from each file and pastes it beneath the last row of data in a sheet in the master file. Right now it includes all columns from A:K. I would like for it to ignore H, I, and J, but can't figure out how to do it. I would really appreciate any help I can get with this.

Workbooks.Open FileItem


                        With ActiveWorkbook.Sheets(1)
                            vData = .Range(.Cells(iFirstRow, "A"), .Cells(100 + iFirstRow, "K")) ''' get data
                            ''' show progress
                            Application.StatusBar = "Processing " & ActiveWorkbook.Name
                        End With
                        
                        With wsTarget
                            nLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
                            If nLastRow = 1 Then
                                i = 2
                            Else
                                i = 1
                            End If
                            .Cells(nLastRow + i, "A").Resize(100, 11) = vData     ''' change Resize to suit rows and columns required
                        End With

Re: VBA-file download that will overwrite any existing files

Quote from LocalPeople;625224

If the file has the same name, you'll get the same 'overwrite existing file?' dialog that you would if you'd mannually save-as-ed the file with the same name as an existing file. You can suppress this (and all other) warnings with

Code

application.displayalerts = false

. Note this will suppress all 'are you sure' messages, and it doesn't auto-reset when the code stops, so make sure to reset it after you've done, and in any error-handling.

Sorry, just one more thing: where exactly do I need to put this code, at the end?

Re: VBA-file download that will overwrite any existing files

Thanks, LocalPeople! :thumbcoo:

I found this code on the web and adapted it to download files from our share point site. Periodically I will have to re-download the same files into the same folder. I would like for them to overwrite the existing files with the same name. Is there something I can add to this code that will do that? Thanks!

Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
                                           "URLDownloadToFileA" ( _
                                           ByVal pCaller As Long, ByVal szURL As String, _
                                           ByVal szFileName As String, _
                                           ByVal dwReserved As Long, _
                                           ByVal lpfnCB As Long) As Long




Sub DownloadFileFromWeb()
Dim i As Integer
'this and the code above work together to copy files from infoportal to a destination folder--WORKS great!
    Const strUrl1 As String = "https://infoportal.blue.com/locations/data1.xls"
    Dim strSavePath1 As String
    Dim returnValue1 As Long
    
        
    strSavePath1 = "s:\accounting\locations\data1.xls"
    returnValue1 = URLDownloadToFile(0, strUrl1, strSavePath1, 0, 0)

Re: TRIM with SUBSTITUTE returning &quot;$&quot; sign when substring is not found in string

You are both absolutely right. I was trying to adapt a formula that was being used to return the number of subs from the strings. Thank you so much for giving me a great solution!

I have a formula that is pulling the substring "OUT" from strings that include it, but the formula is returning a "$" when it looks at a string that does NOT have the substring "OUT".

For example:

income from retransmission agreement - 16,381 subs OUT DMA @ $0.38 per sub (returns "OUT")

income from retransmission agreement - 5,262 subs @ $0.45 per sub (returns "$")

The formula is:

=IF(H187=0,"",TRIM(RIGHT(SUBSTITUTE(LEFT(H187,FIND("subs",LOWER(H187)&"subs")+7)," ",REPT(" ",999)),999)))

Is there a way to return " " for strings that do not contain "OUT"?

Thanks!

Re: How to make SUBSITUTE recognize both upper and lower case words

Thanks Mike!

Re: How to make SUBSITUTE recognize both upper and lower case words

Works perfectly, NBVC! Thanks!!:thumbcoo:

Hi.

I am using this formula:

=IF(H23=0,"",--TRIM(RIGHT(SUBSTITUTE(LEFT(H23,FIND("subs",H23&"subs")-1),"- ",REPT(" ",999)),999)))

to pull the number of subs from strings like this:

income from retransmission agreement - 684.5 subs

I'm having a problem though, because even though the string technically is the same, sometimes I run into cells that have the word "subs" in upper case and the formula returns #VALUE.

income from retransmission agreement - 684.5 subs returns 684.5

income from retransmission agreement -684.5 SUBS returns#VALUE

I'd appreciate any help you can give me.

Thanks!

I have an application that will generate a report each month and export it to Excel. The problem is that there are no options to change the layout of the export and I need to get some of the information into a different format to be reviewed by the "higher-ups". The exported file will have over 2,000 rows each month, so the idea of manually transferring this information is overwhelming (and, I daresay, impossible).

I prepared a file to upload to show you, but I can't get the site to upload it. Maybe it is too big. Here are screenshots of the two example worksheets:

[ATTACH=CONFIG]47269[/ATTACH]

[ATTACH=CONFIG]47270[/ATTACH]