• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Unable to Copy and Paste the data from one Download file to Macro File

J

jawaharprm

Member
Hi All,

Below given code is copy the data from download file "Cust rep report-CA Only_07_18_22.xlsb" to Macro File "RepAlignments_Macro_File.xlsb"
this code will take file as mentioned path in code.
Enclosed file for your reference.
Thanks

Code: 
Option Explicit
Public cust_search As Worksheet, ocust As String
Const path_ As String = "C:\Jawahar Works\New folder\Marcor Works & Projects\Canada Project\New folder"
Const be_path_ As String = "C:\Jawahar Works\New folder\Marcor Works & Projects\Canada Project\New folder"
Const dtEarliest = #1/1/2019# 'stops loop if file is not found by this date

Sub CopyData_Can()

'On Error Resume Next
Dim cell As Range
Dim pos_ As Integer, posSpace As String
Dim cansheet, fNames As Worksheet
Dim be_cansheet, be_repSheet As Worksheet
Dim prname, prHandle, prEmail As String
Dim repFirst, repLast, repFull As String
Dim repname, repEmail As String
Dim oThisWB, cxWB, ccWB, raWB As String
Dim dtTestDate As Date
Dim sStartWb As String
Dim t, t_, k As Long

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False



Set cansheet = ThisWorkbook.Sheets(6)



cansheet.Cells.ClearContents

oThisWB = ActiveWorkbook.Name

'Run only if file updated Customer Extract file is found
dtTestDate = Date + 1

While ActiveWorkbook.Name = oThisWB And dtTestDate >= dtEarliest
   ocust = "Cust rep report-CA Only_" & Format(dtTestDate, "MM_DD_YY") & ".xlsb"
    On Error Resume Next
    Workbooks.Open Filename:=path_ & ocust, ReadOnly:=True
    dtTestDate = dtTestDate - 1
    On Error GoTo 0
Wend

'On Error Resume Next

If Not ActiveWorkbook.Name = oThisWB Then
    'Copy US Customer Extract
    cxWB = ActiveWorkbook.Name
    With Workbooks(cxWB)
        fNames.Cells(2, 1) = cxWB
        .Sheets(6).Cells.AutoFilter
        'WR 20210714 - .Sheets(1).Range("A:A,C:C,E:F,N:J,Q:R,V:W,Y:Y,AB:AB,AF:AF").EntireColumn.Copy _
            Destination:=custSheet.Columns("A")
        .Sheets(6).Range("A:A,C:C,E:F,H:I,N:Q,T:U,W:W,Z:Z,AD:AD,AF:AF").EntireColumn.Copy _
            Destination:=cansheet.Columns("A")
        'Enter date of document below
        'cansheet.Range("R2") = dtTestDate + 1
    End With
    Workbooks(cxWB).Close False
End If
'######## BIO EXPRESS EXTRACT ########
Workbooks(oThisWB).Activate
dtTestDate = Date + 1

While ActiveWorkbook.Name = oThisWB And dtTestDate >= dtEarliest
    On Error Resume Next
    Workbooks.Open Filename:=be_path_ & "Cust rep report-CA Only - BioExpress Only " & Format(dtTestDate, "MM_DD_YY") & ".xlsb", ReadOnly:=True
    dtTestDate = dtTestDate - 1
    On Error GoTo 0
Wend
      
'On Error Resume Next

If Not ActiveWorkbook.Name = oThisWB Then
    'Copy US Customer Extract
    cxWB = ActiveWorkbook.Name
    With Workbooks(cxWB)
        fNames.Cells(3, 1) = cxWB
        .Sheets(6).Cells.AutoFilter
        .Sheets(6).Range("A:B,D:E,G:H,M:O,T:U, W:W,AB:AB,AF:AF,AO:AQ").EntireColumn.Copy _
            Destination:=be_cansheet.Columns("A")
        'Enter date of document below
        'cansheet.Range("R2") = dtTestDate + 1
    End With
    'Rename headers of "BE Cust Extract" worksheet
    With be_cansheet
        .Range("A1") = "BE Soldto"
        .Range("B1") = "BE Customer"
        .Range("C1") = "BE Name 1"
        .Range("D1") = "BE Name 2"
        .Range("E1") = "BE Street"
        .Range("F1") = "BE Street 2"
        .Range("G1") = "BE City"
        .Range("H1") = "BE Rg"
        .Range("I1") = "BE Postal Code"
        .Range("J1") = "BE Cust ID"
        .Range("K1") = "BE Cust ID Desc"
        .Range("L1") = "BE Inds Desc"
        .Range("M1") = "BE Rep No"
        .Range("N1") = "BE Creation Date"
    End With
    Workbooks(cxWB).Close False
End If

End Sub
 
enclosed File
 

Attachments

  • Cust rep report-CA Only_07_18_22.xlsb
    17.1 KB · Views: 0
  • RepAlignments_Macro_File.xlsb
    778.2 KB · Views: 0
jawaharprm
Based Your pasted code ...
Your path_ ends with \New folder - okay.
Should You have something after path_ ... before any filename?
Have You tracked - which line gives Error?
 
Hi Sir,
Thanks for replay, Mentioned Path ends with newfolder\ only, download file name mentioned below code.
Filename will received with multiple date, but "Cust rep report-CA Only_" text will be same for every file.
Iam not getting any errors.


Code: 
While ActiveWorkbook.Name = oThisWB And dtTestDate >= dtEarliest
   ocust = "Cust rep report-CA Only_" & Format(dtTestDate, "MM_DD_YY") & ".xlsb"
    On Error Resume Next
    Workbooks.Open Filename:=path_ & ocust, ReadOnly:=True
    dtTestDate = dtTestDate - 1
    On Error GoTo 0
Wend
 
jawaharprm
How do below ends? with r-letter
Const path_ As String = "C:\Jawahar Works\New folder\Marcor Works & Projects\Canada Project\New folder"
Do do Your filename starts? ... with C-letter
That means something like:
"C:\Jawahar Works\New folder\Marcor Works & Projects\Canada Project\New folderCust rep report-CA Only_" & Format(dtTestDate, "MM_DD_YY") & ".xlsb"
 
Hi Sir,

Thanks for your time,

changed but getting error in "Format"

Error:
Compile Error:
Constant expression required

Code: 
Option Explicit
Public cust_search As Worksheet, ocust As String, dttestdate As Date

Const path_ As String = "C:\Jawahar Works\New folder\Marcor Works & Projects\Canada Project\New folder\Cust rep report-CA Only_" & Format(dttestdate, "MM_DD_YY") & ".xlsb"

Const be_path_ As String = "C:\Jawahar Works\New folder\Marcor Works & Projects\Canada Project\New folder\Cust rep report-CA Only_" & Format(dttestdate, "MM_DD_YY") & ".xlsb"

Const dtEarliest = #1/1/2019# 'stops loop if file is not found by this date
 
jawaharprm
I didn't try to give a hint to do as above ...
# add "\" in the end of Your path_ and be_path_

About Your error ...
# When do You and Your code knows dttestdate -value?
... as well as - can it ( dttestdate ) be fixed? ... always same?
Try to undo Your change and try to do mine.

I asked: Have You tracked - which line gives Error? ... the keyword is tracked
Open Watch-window and track step-by-step - do err.number has other value than 0 ( zero ).
 
Hi Sir,
Thanks

Added in the end but getting
Compile Error
Type Mismatch

"path_ As String ="

Code: 
Option Explicit
Public cust_search As Worksheet, ocust As String
Const path_ As String = "C:\Jawahar Works\New folder\Marcor Works & Projects\Canada Project\New folder" \ ""
Const be_path_ As String = "C:\Jawahar Works\New folder\Marcor Works & Projects\Canada Project\New folder" \ ""
Const dtEarliest = #1/1/2019# 'stops loop if file is not found by this date
 
Thanks Sir, its working..
But Getting Error where i bold, in code
"Object Variable or with block variable not set"

but i defined the variable
Dim cansheet, fNames As Worksheet
Dim oThisWB, cxWB, ccWB, raWB As String

Code: 
If Not ActiveWorkbook.Name = oThisWB Then
    'Copy US Customer Extract
    cxWB = ActiveWorkbook.Name
    With Workbooks(cxWB)
        fNames.Cells(2, 1) = cxWB
        .Sheets(6).Cells.AutoFilter
 
Your fName is as Worksheet ... Excel won't guess which Worksheet.

I tried as about Sheets(6) - it different than sheet6.
Workbooks(cxWB).Sheets(6) is sixth sheet in that Workbook ... without name
 
You must log in or register to reply here.
Back
Top