Hi guys, let me just get to the example, since I cannot explain it so well.
Because I might explain badly, let me give an example, if I have:
Cat Cat Weight Notes
Dylan 13 God kitty
Kitten 14 Sharp claw
I have some VBA code that gives a folder "dylan" and "Kitten" with some pdf that works as intended: it creates folders "Dylan" and "Kitten" and within Dylan we will export a pdf called "Dylan" that only has Dylan's information and the header row; within Kitten we will export a pdf "Kitten" that only has Kitten's information and the header row. It is useful for, if I have like 100 cats, printing 100 different pdfs for individual cats.
I need to add to the right folder another document on my computer that is called "God kitty.txt" to "Dylan" and "Sharp claw.txt" to "Kitten". These have some important information regarding the cats.
I know that the creating-PDF and saving-in-folder works just fine! All thanks to Leith Ross. Unfortunately, the finding "God kitty.txt" does not work. It finds "God kitty.txt", but then it does not copy it. I am confused how to copy the folder from one place to another, if I just use FSO.CopyFolder I think I need to use FSO.CreateObject("scripting.filesystemobject"), but I am having some trouble with this approach. Is there any other way for me to copy the folder to indexFolder after I find it? indexFolder is the folder I create for each PDF, all within "C:/Users/叶秋/Desktop/Test1". Sorry for the Chinese, cannot change that easily the computer name.
I would greatly appreciate a step in the right direction. I've been at VBA for about 3 weeks, so I'm kind of a beginner. It's really satisfying though
Thanks!
Ye Qiu
Sub pdfCopy()
Dim File As String
Dim Folder As String
Dim index As Long
Dim pdfRng As Range
Dim Rng As Range
Dim Wks As Worksheet
Dim fileRng As Range
Dim indexFolder As String
Dim StrFile As String
Dim wantfile As Variant
Folder = "C:\Users\叶秋\Desktop\Test1\"
wantfile = Dir("C:\Users\叶秋\Desktop\Test2\")
Set Wks = Worksheets("Sheet1")
Set Rng = Wks.Cells(1, "A").CurrentRegion
Set fileRng = Wks.Cells(1, "G").CurrentRegion
Wks.Names.Add "Print_Area", Rng
Set pdfRng = Intersect(Rng, Rng.Offset(1, 0))
If pdfRng Is Nothing Then Exit Sub Else pdfRng.Rows.Hidden = True
For index = 1 To pdfRng.Rows.Count
pdfRng.Rows(index).Hidden = False
With Sheets(1).PageSetup
.Orientation = xlLandscape
.FitToPagesTall = False
.FitToPagesWide = 1
.Zoom = False
End With
indexFolder = Folder & pdfRng.Item(index, 1) & "\"
MkDir indexFolder
While (wantfile <> "")
If InStr(wantfile, fileRng.Item(index + 1, 1)) > 0 Then
MsgBox "found " & wantfile
End If
wantfile = Dir
Wend
File = indexFolder & pdfRng.Item(index, 1) & ".pdf"
Wks.Names("Print_Area").RefersToRange.ExportAsFixedFormat xlTypePDF, File
pdfRng.Rows(index).Hidden = True
Next index
Rng.Rows.Hidden = False
End Sub
Bookmarks